SQL and Relational Theory, 2nd Edition

UNION, INTERSECTION, AND DIFFERENCE

Union, intersection, and difference (UNION, INTERSECT, and MINUS in Tutorial D; UNION, INTERSECT, and EXCEPT in SQL) all follow the same general pattern. I’ll start with union.

Union

Definition: Let relations r1 and r2 be of the same type; then their union, r1 UNION r2, is a relation of the same type, with body consisting of all tuples t such that t appears in r1 or r2 or both.

For example (I’ll assume for the sake of all of the examples in this section that parts have an extra attribute called STATUS, of type INTEGER):

As with projection, it’s worth noting explicitly in connection with union that “duplicates are eliminated.” Note that we don’t need to specify DISTINCT in the SQL version in order to achieve this effect; although UNION provides the same options as SELECT does (DISTINCT vs. ALL), the default for UNION is DISTINCT, not ALL (for SELECT it’s the other way around, as you’ll recall from Chapter 4). The result heading has attributes or columns STATUS and CITY—in that order, in SQL. As for the CORRESPONDING specification in the SQL formulation, that specification allows us to ignore the possibility that those columns might appear at different ordinal positions within the operand tables. Recommendations:

• Make sure every column of the first operand table has the same name and type as some column of the second operand table and vice versa.^[82]