UNION / EXCEPT / INTERSECT

il_unionexint01_01.pngUNION, EXCEPT, and INTERSECT are operators that operate similarly and can come between two queries to form Boolean combinations between the results of the two queries.  Tables returned by UNION, EXCEPT and INTERSECT by default will have duplicate records eliminated.  Use ALL to retain duplicates in the results table.

 

Consider the tables called Invoices, seen at right, and Students, seen below.  The Invoices table lists invoices for construction work done by named people, with some names appearing more than once.   The Students table lists names of students.   Some names appear in both tables.   

 

il_unionexint01_02.png

We can form Boolean combinations between the results of queries involving those tables.  For example, we may want to know the names of all people working on construction projects who are not students.      The UNION, EXCEPT, and INTERSECT operators can form Boolean combinations between any two queries.   In the examples that follow we show the operators working between two SELECT queries.

Examples

<SELECT query> UNION <query> - Return a table of all records found either in the <SELECT query> results or in the <query> results.

 

SELECT Name FROM Invoices

  UNION SELECT Name FROM Students;

 

il_unionexint01_03.png

The query above returns a table with all names found either in the Invoices table or in the Students table, removing duplicates.    To keep duplicates in the results table, we use ALL:

 

SELECT Name FROM Invoices

  UNION ALL SELECT Name FROM Students;

 

il_unionexint01_06.png

 

 

<SELECT query> EXCEPT <query> - Return a table of all records in the <SELECT query> results but not in the <query> results.

 

SELECT Name FROM Invoices

  EXCEPT SELECT Name FROM Students;

 

il_unionexint01_04.png

The query above returns a table with all names found in the Invoices table except those who are students, removing duplicates.   To keep duplicates in the results table, we use ALL:

 

SELECT Name FROM Invoices

  EXCEPT ALL SELECT Name FROM Students;

 

il_unionexint01_07.png

 

 

<SELECT query> INTERSECT <query> - Return a table of all records found both in the <SELECT query> results and also in the <query> results.

 

SELECT Name FROM Invoices

  INTERSECT SELECT Name FROM Students;

 

il_unionexint01_05.png

The query above returns a table with all names found in the Invoices table who also are students, removing duplicates.   To keep duplicates in the results table, we use ALL:

 

SELECT Name FROM Invoices

  UNION ALL SELECT Name FROM Students;

 

il_unionexint01_08.png

Other Queries

The UNION, EXCEPT, and INTERSECT operators can form Boolean combinations between any two queries or table expressions.   The above examples use SELECT, but we can use other statements, such as VALUES:

 

VALUES ('Anita'), ('Ravi'), ('George'), ('Tom')

  INTERSECT SELECT Name FROM Students;

 

il_unionexint01_09.png

 

Notes

No brackets - In this topic to reduce visual clutter we do not use square [ ] brackets around simple field and table names.    Square brackets are optional if the name is unambiguous.

 

Precedence - INTERSECT has priority over UNION or EXCEPT.

 

Hackish - The UNION, EXCEPT, and INTERSECT operators are somewhat hackish as defined by current SQL standards, in that some odd details are OK.   For example, the operators do Boolean operations without caring if field names are different in the two different results tables.   A legal query, according to the SQL standard and followed by Manifold, is:

 

SELECT Name FROM mfd_root

UNION ALL

SELECT Type FROM mfd_root;

 

The UNION works and the above query throws no errors even though the field names Name and Type are different.

 

See Also

Queries

 

Command Window

 

Query Builder

 

Aggregates

 

SQL Statements

 

GROUP BY

 

ORDER BY

 

SPLIT