INTERSECT
You are viewing an old version of this article. View
the current version here.
MariaDB starting with 10.3.0
INTERSECT will be 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
INTERSECT
(and it is difficult to make sense of ALL with INTERSECT). - Brackets for explicit operation precedence are not supported; use a subquery in the
FROM
clause as a workaround).
Description
MariaDB 10.3 supports INTERSECT
in addition to UNION
All behavior for naming columns, ORDER BY
and LIMIT
is the same as for UNION
.
INTERSECT
implicitly supposes a DISTINCT
operation.
INTERSECT
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
. 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)
Examples
Show customers which are employees:
(SELECT e_name AS name, email FROM employees) INTERSECT (SELECT c_name AS name, email FROM customers);
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.