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}]

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);

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.