How to Exclude Rows in MySQL Queries Without EXCEPT?

EXCEPT Syntax Error in SQL Query

In MySQL, using the EXCEPT syntax to exclude rows from a query can result in a syntax error. This is because MySQL doesn't natively support the EXCEPT keyword.

Alternative Solutions

To achieve the desired exclusion functionality, you can use either the NOT IN clause or a LEFT JOIN with an IFNULL condition:

NOT IN Clause:

SELECT s.sno
FROM students s
WHERE s.sno NOT IN (
    SELECT t.sno
    FROM take t
    WHERE t.cno = 'CS112'
);

LEFT JOIN with IFNULL:

SELECT s.sno
FROM students s
LEFT JOIN take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112';

Example

Consider the following sample data:

create temporary table students (sno int);

insert into students values (1);
insert into students values (2);
insert into students values (3);
insert into students values (4);
insert into students values (5);
insert into students values (6);
insert into students values (7);
insert into students values (8);
insert into students values (9);
insert into students values (10);

create temporary table take (sno int, cno varchar(50));

insert into take values (1, 'CS112');
insert into take values (2, 'CS112');
insert into take values (3, 'CS112');
insert into take values (4, 'CS112');

Using either the NOT IN clause or the LEFT JOIN with IFNULL will return the following result:

+------+
| sno  |
+------+
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+

These alternative methods effectively exclude students who have taken the course CS112, resulting in a list of students who haven't taken the course.

The above is the detailed content of How to Exclude Rows in MySQL Queries Without EXCEPT?. For more information, please follow other related articles on the PHP Chinese website!