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 INTERSECT and EXCEPT (and it is difficult to make sense of ALL with INTERSECT or EXCEPT).
  • Brackets for explicit operation precedence are not supported; use a subquery in the FROM clause as a workaround).

Description

MariaDB 10.3 supports INTERSECT and EXCEPT in addition to UNION

All behavior for naming columns, ORDER BY and LIMIT is the same as for UNION.

Both INTERSECT and EXCEPT implicitly suppose DISTINCT operation.

INTERSECT

The result of an intersect is the intersection of right and left SELECT results, i.e. only records that present in both result sets will be result of the operation.

INTERSECT has higher precedence than UNION and EXCEPT. If possible it will be executed linearly but if not it will be translated to a subquery in the FROM clause:

(select a,b from t1)
union
(select c,d from t2)
intersect
(select e,f from t3)
union
(select 4,4);

will be translated to:

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

EXCEPT

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 employees:

(SELECT e_name AS name, email FROM employees)
INTERSECT
(SELECT c_name AS name, email FROM customers);

Show customers which are not employees:

(SELECT e_name AS name, email FROM customers)
EXCEPT
(SELECT c_name AS name, email FROM employees);

Comments

Comments loading...
Loading