EXCEPT
You are viewing an old version of this article. View
the current version here.
MariaDB starting with 10.3.0
EXCEPT was introduced in MariaDB 10.3.0.
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}]
Contents
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 10.3 supports EXCEPT
and INTERSECT in addition to UNION.
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.
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 UNION SELECT i FROM seqs WHERE i>=3; +------+ | i | +------+ | 1 | | 2 | | 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 INTERSECT SELECT i FROM seqs WHERE i>=3; +------+ | i | +------+ | 3 | +------+
See Also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.