The result of EXCEPT is all records of the left SELECT result set except
records which are in right SELECT result set, i.e. it is subtraction of two
result sets. From MariaDB 10.6.1, MINUS is a synonym when SQL_MODE=ORACLE is
set.

Syntax
------

SELECT ...
(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL |
DISTINCT]) 
 SELECT ...
[(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL |
DISTINCT]) 
 SELECT ...]
[ORDER BY [{col_name | expr | position} [ASC | DESC] 
 [, {col_name | expr | position} [ASC | DESC] ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}
| OFFSET start { ROW | ROWS }
| FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]

Please note:

* Brackets for explicit operation precedence are not supported; use a subquery
in the FROM clause as a workaround).

Description
-----------

MariaDB has supported EXCEPT and INTERSECT in addition to UNION since MariaDB
10.3.

The queries before and after EXCEPT must be SELECT or VALUES statements.

All behavior for naming columns, ORDER BY and LIMIT is the same as for UNION.
Note that the alternative SELECT ... OFFSET ... FETCH syntax is only
supported. This allows us to use the WITH TIES clause.

EXCEPT implicitly supposes a DISTINCT operation.

The result of EXCEPT is all records of the left SELECT result except records
which are in right SELECT result set, i.e. it is subtraction of two result
sets.

EXCEPT and UNION have the same operation precedence and INTERSECT has a higher
precedence, unless running in Oracle mode, in which case all three have the
same precedence.

MariaDB starting with 10.4.0
----------------------------

Parentheses
-----------

From MariaDB 10.4.0, parentheses can be used to specify precedence. Before
this, a syntax error would be returned.

MariaDB starting with 10.5.0
----------------------------

ALL/DISTINCT
------------

EXCEPT ALL and EXCEPT DISTINCT were introduced in MariaDB 10.5.0. The ALL
operator leaves duplicates intact, while the DISTINCT operator removes
duplicates. DISTINCT is the default behavior if neither operator is supplied,
and the only behavior prior to MariaDB 10.5.

Examples
--------

Show customers which are not employees:

(SELECT e_name AS name, email FROM customers)
EXCEPT
(SELECT c_name AS name, email FROM employees);

Difference between UNION, EXCEPT and INTERSECT. INTERSECT ALL and EXCEPT ALL
are available from MariaDB 10.5.0.

CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);

SELECT i FROM seqs WHERE i <= 3 UNION SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

SELECT i FROM seqs WHERE i <= 3 UNION ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
|    3 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

SELECT i FROM seqs WHERE i <= 3 EXCEPT SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
+------+

SELECT i FROM seqs WHERE i <= 3 EXCEPT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
|    2 |
+------+

SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    3 |
+------+

SELECT i FROM seqs WHERE i <= 3 INTERSECT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    3 |
|    3 |
+------+

Parentheses for specifying precedence, from MariaDB 10.4.0

CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (b INT);
CREATE OR REPLACE TABLE t3 (c INT);

INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (5),(6);
INSERT INTO t3 VALUES (1),(6);

((SELECT a FROM t1) UNION (SELECT b FROM t2)) EXCEPT (SELECT c FROM t3);
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
|    5 |
+------+

(SELECT a FROM t1) UNION ((SELECT b FROM t2) EXCEPT (SELECT c FROM t3));
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Here is an example that makes use of the SEQUENCE storage engine and the
VALUES statement, to generate a numeric sequence and remove some arbitrary
numbers from it:

(SELECT seq FROM seq_1_to_10) EXCEPT VALUES (2), (3), (4);
+-----+
| seq |
+-----+
|   1 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+

URL: https://mariadb.com/kb/en/except/