Set Operations

Set Operations

The SQL operations union, intersect, and except operate on relations and correspond to the relational-algebra operations ∪, ∩, and −. Like union, intersection, and set difference in relational algebra, the relations participating in the operations must be compatible; that is, they must have the same set of attributes.

Let us demonstrate how several of the example queries that we considered in Chapter 3 can be written in SQL. We shall now construct queries involving the union, intersect, and except operations of two sets: the set of all customers who have an account at the bank, which can be derived by

We shall refer to the relations obtained as the result of the preceding queries as d and b, respectively.

The Union Operation

To find all customers having a loan, an account, or both at the bank, we write

The union operation automatically eliminates duplicates, unlike the select clause. Thus, in the preceding query, if a customer — say, Jones — has several accounts or loans (or both) at the bank, then Jones will appear only once in the result.

If we want to retain all duplicates, we must write union all in place of union:

(select customer-name

from depositor)

 union all

(select customer-name

from borrower)

The number of duplicate tuples in the result is equal to the total number of duplicates that appear in both d and b. Thus, if Jones has three accounts and two loans at the bank, then there will be five tuples with the name Jones in the result.

The Intersect Operation

To find all customers who have both a loan and an account at the bank, we write

The intersect operation automatically eliminates duplicates. Thus, in the preceding query, if a customer — say, Jones — has several accounts and loans at the bank, then Jones will appear only once in the result.

If we want to retain all duplicates, we must write intersect all in place of intersect:

The number of duplicate tuples that appear in the result is equal to the minimum number of duplicates in both d and b. Thus, if Jones has three accounts and two loans at the bank, then there will be two tuples with the name Jones in the result.

The Except Operation

To find all customers who have an account but no loan at the bank, we write

The except operation automatically eliminates duplicates. Thus, in the preceding query, a tuple with customer name Jones will appear (exactly once) in the result only if Jones has an account at the bank, but has no loan at the bank.

If we want to retain all duplicates, we must write except all in place of except:

The number of duplicate copies of a tuple in the result is equal to the number of duplicate copies of the tuple in d minus the number of duplicate copies of the tuple in b, provided that the difference is positive. Thus, if Jones has three accounts and one loan at the bank, then there will be two tuples with the name Jones in the result. If, instead, this customer has two accounts and three loans at the bank, there will be no tuple with the name Jones in the result.