UNION, EXCEPT, and INTERSECT are operators that operate similarly and are used between two queries to form Boolean combinations between the results of the two queries. Given queries A and B, UNION returns all records returned by either A or B. EXCEPT returns all records in A but not in B. INTERSECT returns all records returned by both A and also by B. 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.
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.
<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;
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;
<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;
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;
<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;
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;
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;
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.