EXCEPT was introduced in MariaDB 10.3.0.
 
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.
 
Syntax
------ 
SELECT ...
(INTERSECT | EXCEPT | UNION [ALL | DISTINCT]) SELECT ...
[(INTERSECT | EXCEPT | UNION [ALL | DISTINCT]) SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
 
Please note:
ALL is not supported by EXCEPT (and it is difficult to make
sense of ALL with EXCEPT).
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.
 
All behavior for naming columns, ORDER BY and LIMIT is the
same as for UNION.
 
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.
 

Parentheses
 
From MariaDB 10.4.0, parentheses can be used to specify
precedence. Before this, a syntax error would be returned.
 
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:
 
CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(3),(4),(5),(6);
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 1 |
| 2 |
+------+
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 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 |
+------+
 


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