Return rows from the first result set that do not appear in the second. This set operator performs a subtraction of two datasets.
The result of EXCEPT contains all records of the left SELECT result set except records which are in right SELECT result set. In other words, it is the subtraction of two result sets.
MINUS is a synonym when is set.
MINUS is a synonym is not available.
Brackets for explicit operation precedence are not supported; use a subquery in the FROM clause as a workaround.
MariaDB supports EXCEPT and in addition to .
The queries before and after EXCEPT must be or statements.
All behavior for naming columns, ORDER BY and LIMIT is the same as for . Note that the alternative 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 , in which case all three have the same precedence.
Parentheses can be used to specify precedence. Before this, a syntax error would be returned.
MariaDB starting with
EXCEPT ALL and EXCEPT DISTINCT . The ALL operator leaves duplicates intact, while the DISTINCT operator removes duplicates. DISTINCT is the default behavior if neither operator is supplied.
Only EXCEPT DISTINCT is available.
Show customers which are not employees:
Difference between , EXCEPT and :
Parentheses for specifying precedence:
Here is an example that makes use of the storage engine and the statement, to generate a numeric sequence and remove some arbitrary numbers from it:
(video tutorial)
This page is licensed: CC BY-SA / Gnu FDL
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 } ](SELECT e_name AS name, email FROM customers)
EXCEPT
(SELECT c_name AS name, email FROM employees);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 |
+------+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 |
+------+(SELECT seq FROM seq_1_to_10) EXCEPT VALUES (2), (3), (4);
+-----+
| seq |
+-----+
| 1 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+-----+