EXCEPT

EXCEPT operation returns the rows that are present in the first querying table but not in the second. MySQL does not natively support the EXCEPT operator, but you can achieve similar results using LEFT JOIN with a NULL check or NOT EXISTS clauses.

Image scaled to 80%

Example using the NOT EXISTS approach:

Suppose we have two database tables with the names Customers and Suppliers as shown below:

Image scaled to 70%

Image scaled to 70%

Now we want to get the rows from Customers that do not exist in Supppliers based on a common column, let’s say city :

SELECT * FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Suppliers s
    WHERE c.city = s.city
);

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Execution of the above query will give the following result:

Image scaled to 80%