O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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):

image with no caption

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]

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required