Table of Contents Link to heading

• Example Database
• SET Operators
• UNION operator
• UNION ALL operator
• INTERSECT operator
• INTERSECT ALL operator
• INTERSECT and INNER JOIN
• EXCEPT operator

Example Database Link to heading

The below two relations will be used as examples for this post.

SET Operators Link to heading

Set operators are used to combine tuples of query results.

Standard SQL syntax with set operators:

• Inside a SelectSQL statement, a WHERE clause and some keywords (e.g. ORDER BY) can be included.

SelectSQL
< UNION|INTERSECT|EXCEPT [ALL] >
SelectSQL

A set is a result relation (e.g. results returned by a SELECT query {SelectSQL}).

A set operator has two sets (returned from the two SELECT queries) surrounding that set operator.

• Both sets must have the same number of attributes (columns).
• Both sets must have compatible attributes (same data types).
  • Note: NULL values can be of any data type.
• The attributes in both sets must be in the same order.

UNION operator Link to heading

The UNION set operator is used to combine query results, returning a combination of the tuples from both sets, hence the word union.

• Duplicates are removed.

UNION syntax:

SelectSQL
UNION
SelectSQL

For example, given the above example database:

Task - list the first names and last names of employees.

SELECT E1.firstName AS name
FROM Employee AS E1
UNION
SELECT E2.lastName
FROM Employee AS E2

Result - a new relation having only one attribute (name).

• Each tuple contains one value that may either be a first name or a last name of an employee.
• No duplicate values.

UNION ALL operator Link to heading

The UNION ALL set operator serves a similar purpose to the UNION keyword. The only discrepancy is that duplicate values are retained.

UNION ALL syntax:

SelectSQL
UNION ALL
SelectSQL

INTERSECT operator Link to heading

The INTERSECT set operator is also used to combine query results, returning only the tuples that appear in both sets, hence the word intersection.

INTERSECT syntax:

SelectSQL
INTERSECT
SelectSQL

For example, given the above example database:

Task - find the first names of employees that are also last names.

SELECT E1.firstName AS name
FROM Employee AS E1
INTERSECT
SELECT E2.lastName AS name
FROM Employee AS E2;

Is equivalent to:

SELECT DISTINCT E1.firstName
FROM Employee AS E1 INNER JOIN Employee AS E2
ON E1.FirstName = E2.lastName;

Is also equivalent to:

SELECT DISTINCT E1.firstName AS name
FROM Employee AS E1
WHERE E1.firstName IN (
    SELECT E2.lastName AS name
    FROM Employee AS E2
);

Is also equivalent to:

SELECT DISTINCT E1.firstName AS name
FROM Employee AS E1
WHERE EXISTS (
    SELECT E2.* FROM Employee AS E2
    WHERE E1.firstName = E2.lastName
);

Result - a new relation having only one attribute (name).

• The number of tuples represents the number of first names that are also last names.

INTERSECT ALL operator Link to heading

Akin to the UNION ALL operator, appending ALL to the INTERSECT operator will retain duplicate values.

INTERSECT ALL syntax:

SelectSQL
INTERSECT ALL
SelectSQL

INTERSECT and INNER JOIN Link to heading

The two are very different:

1. The former is a set operator that compares every tuple between two sets and can never return more tuples than in the smaller relation.
2. The latter is a keyword that generally matches on a limited number of attributes and can return zero or more tuples in either relation.

🔗 stackoverflow

EXCEPT operator Link to heading

Unlike INTERSECT, the EXCEPT set operator only returns the tuples from the left set (they must not be included in the right set), hence the word exception.

EXCEPT syntax:

SelectSQL
EXCEPT
SelectSQL

For example, given the above example database:

Task - find the first names of employees that are not last names.

SELECT E1.firstName AS name
FROM Employee AS E1
EXCEPT
SELECT E2.lastName AS name
FROM Employee AS E2;

Is equivalent to:

SELECT DISTINCT E1.firstName AS name
FROM Employee AS E1
WHERE E1.firstName NOT IN (
    SELECT E2.lastName AS name
    FROM Employee AS E2
);

Is also equivalent to:

SELECT DISTINCT E1.firstName AS name
FROM Employee AS E1
WHERE NOT EXISTS (
    SELECT E2.* FROM Employee AS E2
    WHERE E1.firstName = E2.lastName
);

Result - a new relation having only one attribute (name).

• The number of tuples represents the number of first names that are not last names.