Combining Result Tables (UNION, INTERSECT, EXCEPT)

Combining Result Tables (UNION, INTERSECT,
EXCEPT)
In SQL, we can use the normal set operations of Union, Intersection, and Difference to
combine the results of two or more queries into a single result table:
n The Union of two tables, A and B, is a table containing all rows that are in either the first
table A or the second table B or both.
n The Intersection of two tables, A and B, is a table containing all rows that are common
to both tables A and B.
n The Difference of two tables, A and B, is a table containing all rows that are in table A
but are not in table B.
The set operations are illustrated in Figure 5.1. There are restrictions on the tables that
can be combined using the set operations, the most important one being that the two
tables have to be union-compatible; that is, they have the same structure. This implies that
the two tables must contain the same number of columns, and that their corresponding
columns have the same data types and lengths. It is the user’s responsibility to ensure that
data values in corresponding columns come from the same domain. For example, it would
not be sensible to combine a column containing the age of staff with the number of rooms
in a property, even though both columns may have the same data type: for example,
SMALLINT.
The three set operators in the ISO standard are called UNION, INTERSECT, and
EXCEPT. The format of the set operator clause in each case is:
operator [ALL] [CORRESPONDING [BY {column1 [, . . . ]}]]
If CORRESPONDING BY is specified, then the set operation is performed on the named
column(s); if CORRESPONDING is specified but not the BY clause, the set operation
is performed on the columns that are common to both tables. If ALL is specified, the
result can include duplicate rows. Some dialects of SQL do not support INTERSECT and
EXCEPT; others use MINUS in place of EXCEPT.
Example 5.32 Use of UNION
Construct a list of all cities where there is either a branch office or a property.
(SELECT city or (SELECT *
FROM Branch FROM Branch
WHERE city IS NOT NULL) WHERE city IS NOT NULL)
UNION UNION CORRESPONDING BY city
(SELECT city (SELECT *
FROM PropertyForRent FROM PropertyForRent
WHERE city IS NOT NULL); WHERE city IS NOT NULL);
This query is executed by producing a result table from the first query and a result table
from the second query, and then merging both tables into a single result table consisting
of all the rows from both result tables with the duplicate rows removed. The final result
table is shown in Table 5.32.
Example 5.33 Use of INTERSECT
Construct a list of all cities where there is both a branch office and a property.
(SELECT city or (SELECT *
FROM Branch) FROM Branch)
INTERSECT INTERSECT CORRESPONDING BY city
(SELECT city (SELECT *
FROM PropertyForRent); FROM PropertyForRent);
This query is executed by producing a result table from the first query and a result table
from the second query, and then creating a single result table consisting of those rows that
are common to both result tables. The final result table is shown in Table 5.33.
We could rewrite this query without the INTERSECT operator, for example:
SELECT DISTINCT b.city or SELECT DISTINCT city
FROM Branch b, PropertyForRent p FROM Branch b
WHERE b.city = p.city; WHERE EXISTS (SELECT *
FROM PropertyForRent p
WHERE b.city = p.city);
The ability to write a query in several equivalent forms illustrates one of the disadvantages
of the SQL language.
Example 5.34 Use of EXCEPT
Construct a list of all cities where there is a branch office but no properties.
(SELECT city or (SELECT *
FROM Branch) FROM Branch)
EXCEPT EXCEPT CORRESPONDING BY city
(SELECT city (SELECT *
FROM PropertyForRent); FROM PropertyForRent);
This query is executed by producing a result table from the first query and a result table
from the second query, and then creating a single result table consisting of those rows that
appear in the first result table but not in the second one. The final result table is shown in
Table 5.34.
We could rewrite this query without the EXCEPT operator, for example:
SELECT DISTINCT city or SELECT DISTINCT city
FROM Branch FROM Branch b
WHERE city NOT IN (SELECT city WHERE NOT EXISTS
FROM PropertyForRent); (SELECT *
FROM PropertyForRent p
WHERE b.city = p.city);