Database Reference
In-Depth Information
12.11 Queries Involving Set Operators
SQL supports the binary set operators UNION, UNION ALL, INTERSECT and MINUS.
Recall from Chapter 7, that a basic requirement for these operations is that (in each
case) the two participating relations must have corresponding attributes defined on the
same domain. Since from a design perspective, it is highly unlikely that a database will
have many base relations that meet this criterion, these set operators are usually used in
queries where two sub-queries produce results that meet the compatibility criterion.
The general syntax, therefore, is:
By way of review (except for UNION ALL), the set operators are explained below:
•
UNION: Returns all rows from both queries, but duplicate rows
are not displayed.
•
UNION ALL: Returns all rows from both queries, including
duplicate rows.
•
INTERSECT: Returns rows that appear in both query results.
•
MINUS: Returns rows that appear in the first query result, but not
in the second.
Example 44:
Produce a list all students enrolled in MIS, Computer Science or
Mathematics (assuming sample data of Figure 7-2), as in Example 37, but this time using
the union of sets:
Example 45:
Find all students from Lenheim Hall who are mathematics majors:
