When combining multiple query blocks in MySQL, we can use the EXCEPT clause to return just those rows from the first block that are not present in the second block.

Example

Suppose we have the following two tables:

SELECT * FROM t1;
SELECT * FROM t2;

Result:

+--------+-------+
| c1     | c2    |
+--------+-------+
| Black  | Dog   |
| White  | Dog   |
| Yellow | Cow   |
| Green  | Horse |
| Black  | Cat   |
| White  | Cat   |
| Blue   | Fish  |
| Green  | Cow   |
+--------+-------+

+---------+-------+
| c1      | c2    |
+---------+-------+
| Black   | Dog   |
| White   | Dog   |
| Pink    | Cow   |
| Red     | Horse |
| Black   | Cat   |
| Crimson | Cat   |
| White   | Bird  |
+---------+-------+

Some rows in the second table are identical to rows in the first table. We can use the EXCEPT clause to exclude those rows from being returned:

SELECT * FROM t1
EXCEPT
SELECT * FROM t2;

Result:

+--------+-------+
| c1     | c2    |
+--------+-------+
| Yellow | Cow   |
| Green  | Horse |
| White  | Cat   |
| Blue   | Fish  |
| Green  | Cow   |
+--------+-------+

So we can see that all five rows are from the first table, and they are the rows that don’t have an identical row in the second table.

Different Columns

Selecting different columns will usually change the rows returned. This is because we’re selecting a different combination of column values, which will usually change which rows are identical.

Let’s select just a single column from each table to see how that affects the result:

SELECT c1 FROM t1
EXCEPT
SELECT c1 FROM t2;

Result:

+--------+
| c1     |
+--------+
| Yellow |
| Green  |
| Blue   |
+--------+

We got those three rows because those are the values that are in the first table but not the second table. We didn’t get White, because that value is in both tables. The reason White was returned in the previous example is because it was evaluated in combination with Cat (from the second column). In that case, there was a White Cat in the first table but not in the second. The second table had a Black Cat, a Crimson Cat, and a White Bird, but not a White Cat.

The DISTINCT Keyword

We can use the DISTINCT keyword to explicitly specify that we only want distinct values returned. As mentioned, this is the default behaviour of EXCEPT, and so using this keyword is optional – using it will yield the same result as not using it.

In any case, we can achieve the same result as the above example by doing this:

SELECT c1 FROM t1
EXCEPT DISTINCT
SELECT c1 FROM t2;

Result:

+--------+
| c1     |
+--------+
| Yellow |
| Green  |
| Blue   |
+--------+

The EXCEPT clause provides a more concise way of getting a result that we would otherwise need to include a lot more code in our query. Here’s an example of how we can get the same result without using EXCEPT:

SELECT 
    DISTINCT c1
FROM t1 
WHERE NOT EXISTS (SELECT c1 FROM t2
WHERE t1.c1 = t2.c1);

Result:

+--------+
| c1     |
+--------+
| Yellow |
| Green  |
| Blue   |
+--------+

The ALL Keyword

We can use the ALL keyword to return all rows – not just distinct rows:

SELECT c1 FROM t1
EXCEPT ALL
SELECT c1 FROM t2;

Result:

+--------+
| c1     |
+--------+
| Yellow |
| Green  |
| Green  |
| Blue   |
+--------+

In our case, the c1 table contains two instances of Green, and so we get an extra row for that. In the previous example Green was returned just once because we were returning distinct values (which eliminates duplicates).

Using EXCEPT with the TABLE Statement

We can use the EXCEPT clause with the TABLE statement, just as we can use it with the SELECT statement:

TABLE t1 EXCEPT TABLE t2;

Result:

+--------+-------+
| c1     | c2    |
+--------+-------+
| Yellow | Cow   |
| Green  | Horse |
| White  | Cat   |
| Blue   | Fish  |
| Green  | Cow   |
+--------+-------+

Using EXCEPT with the VALUES Statement

We can also use EXCEPT with the stand alone VALUES statement:

VALUES
    ROW( 'Black', 'Dog' ),
    ROW( 'White', 'Dog' ),
    ROW( 'Yellow', 'Cow' ),
    ROW( 'Green', 'Horse' ),
    ROW( 'Black', 'Cat' ),
    ROW( 'White', 'Cat' ),
    ROW( 'Blue', 'Fish' ),
    ROW( 'Green', 'Cow' )
EXCEPT
VALUES 
    ROW( 'Black', 'Dog' ),
    ROW( 'White', 'Dog' ),
    ROW( 'Pink', 'Cow' ),
    ROW( 'Red', 'Horse' ),
    ROW( 'Black', 'Cat' ),
    ROW( 'Crimson', 'Cat' ),
    ROW( 'White', 'Bird' );

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
| Yellow   | Cow      |
| Green    | Horse    |
| White    | Cat      |
| Blue     | Fish     |
| Green    | Cow      |
+----------+----------+