`EXCEPT` clause

The EXCEPT expression returns all the rows that are present in the result of the first query but absent in the result of the second query. In other words, this expression calculates the difference between the results of the two queries.

In order to be able to calculate the difference for two queries, the queries must be compatible. This means that they must have the same number of columns and the corresponding columns must be of compatible types.

Syntax

SELECT ...
EXCEPT
SELECT ...

Examples

Let’s output a list of countries that occur in the country column in the capitals table, but do not occur in the column of the same name in the population table:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
CREATE TABLE population (country VARCHAR, population_mil BIGINT);

INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

SELECT country FROM capitals
EXCEPT
SELECT country FROM population;

+---------+
| country |
+---------+
| France  |
+---------+
| Italy   |
+---------+

Now let’s try to calculate the difference of two queries with different sets of columns:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
CREATE TABLE population (country VARCHAR, population_mil BIGINT);

INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

SELECT country, capital FROM capitals -- Error expected
EXCEPT
SELECT country FROM population;

ERROR: BinderException: Binder Error:
Set operations can only apply to expressions with the same number of result columns

We got an error because the queries are not compatible for calculating the difference — they specify different sets of columns.