Control the execution order of UNION, EXCEPT, and INTERSECT operations. Learn how to use parentheses to define explicit operation priority.
You can control the ordering of execution on table operations using parentheses.
( expression )
[ORDER BY [column[, column...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]Using parentheses in SQL allows to control the order of execution for statements and , including , , and operations. MariaDB executes the parenthetical expression before the rest of the statement. You can then use and clauses the further organize the result set.
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE test.t1 (num INT);
INSERT INTO test.t1 VALUES (1),(2),(3);
(SELECT * FROM test.t1
UNION
VALUES (10))
INTERSECT
VALUES (1),(3),(10),(11);
+------+
| num |
+------+
| 1 |
| 3 |
| 10 |
+------+
((SELECT * FROM test.t1
UNION
VALUES (10))
INTERSECT
VALUES (1),(3),(10),(11))
ORDER BY 1 DESC;
+------+
| num |
+------+
| 10 |
| 3 |
| 1 |
+------+SQL set operations combine the results of multiple query blocks in a single result, using the standard SQL operators EXCEPT, INTERSECT, and UNION, and the Oracle operator MINUS.
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 |
+-----+Oracle-compatible synonym for the EXCEPT operator. It returns rows from the first query that are not present in the second query.
MINUS is a synonym for EXCEPT when is set.
CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);
SET SQL_MODE='ORACLE';
SELECT i FROM seqs WHERE i <= 3 MINUS SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
+------+This page is licensed: CC BY-SA / Gnu FDL
Return only rows that appear in both result sets. This set operator identifies the common records shared between two queries.
MariaDB has supported INTERSECT (as well as ) in addition to since .
All behavior for naming columns, ORDER BY and LIMIT is the same as for .
INTERSECT implicitly supposes a DISTINCT operation.
The result of an intersect is the intersection of right and left SELECT results, i.e. only records that are present in both result sets will be included in the result of the operation.
INTERSECT has higher precedence than UNION and EXCEPT (unless running , in which case all three have the same precedence). If possible it will be executed linearly, but if not, it will be translated to a subquery in the FROM clause:
will be translated to:
Parentheses can be used to specify precedence.
Parentheses cannot be used to specify precedence.
MariaDB starting with
INTERSECT ALL and INTERSECT DISTINCT . The ALL operator leaves duplicates intact, while the DISTINCT operator removes duplicates. DISTINCT is the default behavior if neither operator is supplied.
DISTINCT is the only behavior available.
Show customers which are employees:
Difference between , and INTERSECT:
Parentheses for specifying precedence:
(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 [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}](SELECT a,b FROM t1)
UNION
(SELECT c,d FROM t2)
INTERSECT
(SELECT e,f FROM t3)
UNION
(SELECT 4,4);(SELECT a,b FROM t1)
UNION
SELECT c,d FROM
((SELECT c,d FROM t2)
INTERSECT
(SELECT e,f FROM t3)) dummy_subselect
UNION
(SELECT 4,4)(SELECT e_name AS name, email FROM employees)
INTERSECT
(SELECT c_name AS name, email FROM customers);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)) INTERSECT (SELECT c FROM t3);
+------+
| a |
+------+
| 1 |
| 6 |
+------+
(SELECT a FROM t1) UNION ((SELECT b FROM t2) INTERSECT (SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+------+Combine results from multiple SELECT statements into a single result set. This operator can optionally remove duplicates or include all rows.
UNION is used to combine the results from multiple SELECT statements into a single result set.
UNION is used to combine the results from multiple statements into a single result set.
The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
If they don't, the type and length of the columns in the result take into account the values returned by all of the SELECTs, so there is no need for explicit casting. Note that currently this is not the case for - see .
Table names can be specified as db_name.tbl_name. This permits writing UNIONs which involve multiple databases. See for syntax details.
UNION queries cannot be used with .
EXCEPT and UNION have the same operation precedence and INTERSECT has a higher precedence, unless , in which case all three have the same precedence.
The ALL keyword causes duplicate rows to be preserved. The DISTINCT keyword (the default if the keyword is omitted) causes duplicate rows to be removed by the results.
UNION ALL and UNION DISTINCT can both be present in a query. In this case, UNION DISTINCT will override any UNION ALL to its left.
The server can in most cases execute UNION ALL without creating a temporary table (see ).
Individual SELECT statements can contain their own and clauses. In this case, the individual queries need to be wrapped between parentheses. However, this does not affect the order of the UNION, so they only are useful to limit the record read by one SELECT.
The UNION can have global and clauses, which affect the whole result set. If the columns retrieved by individual SELECT statements have an alias (AS), the ORDER BY must use that alias, not the real column names.
Specifying a query as does not work inside a UNION. If applied to the first SELECT, it is ignored. Applying to a later SELECT results in a syntax error:
Individual SELECT statements cannot be written or . If the last SELECT statement specifies INTO DUMPFILE or INTO OUTFILE, the entire result of the UNION will be written. Placing the clause after any other SELECT will result in a syntax error.
If the result is a single row, can also be used.
Parentheses can be used to specify precedence.
Parentheses cannot be used to specify precedence.
UNION between tables having different column names:
Specifying the UNION global order and limiting total rows:
Adding a constant row:
Differing types:
Returning the results in order of each individual SELECT by use of a sort column:
Difference between UNION, and :
This page is licensed: GPLv2, originally from
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]ERROR 1234 (42000): Incorrect usage/placement of 'HIGH_PRIORITY'(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION
(SELECT name, email FROM customers);SELECT CAST('x' AS CHAR(1)) UNION SELECT REPEAT('y',4);
+----------------------+
| CAST('x' AS CHAR(1)) |
+----------------------+
| x |
| yyyy |
+----------------------+(SELECT 1 AS sort_column, e_name AS name, email FROM employees)
UNION
(SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column;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)) INTERSECT (SELECT c FROM t3);
+------+
| a |
+------+
| 1 |
| 6 |
+------+
(SELECT a FROM t1) UNION ((SELECT b FROM t2) INTERSECT (SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+------+