Database Reference
In-Depth Information
have the same structure for a union to be appropriate; the formal term is union compatible. Two tables are
union compatible when they have the same number of columns and when their corresponding columns rep-
resent the same type of data. For example, if the first column in table A contains customer numbers, the first
column in table B must also contain customer numbers.
63
EXAMPLE 24
List the number and name of those customers that have orders or are represented by sales rep 65, or both.
You can create a table containing the number and name of all customers that have orders by joining the
Orders table and the Customer table (Temp1 in the following example) and then projecting the result over
CustomerNum and CustomerName (Temp2). You can also create a table containing the number and name of
all customers represented by sales rep 65 by selecting from the Customer table (Temp3) and then projecting
the result (Temp4). The two tables ultimately created by this process (Temp2 and Temp4) have the same
structure. They each have two fields: CustomerNum and CustomerName. Because these two tables are union
compatible, it is appropriate to take the union of these two tables. This process is accomplished in relational
algebra using the following code:
JOIN Orders, Customer
WHERE Orders.CustomerNum=Customer.CustomerNum
GIVING Temp1
PROJECT Temp1 OVER CustomerNum, CustomerName
GIVING Temp2
SELECT Customer WHERE RepNum=
'
65
'
GIVING Temp3
PROJECT Temp3 OVER CustomerNum, CustomerName
GIVING Temp4
UNION Temp2 WITH Temp4 GIVING Answer
Intersection
As you would expect, using the intersection operation is very similar to using the union operation. The only
difference is that you replace the UNION command with the INTERSECT command, as illustrated in the
following example.
EXAMPLE 25
List the number and name of customers that have orders and that are represented by sales rep 65.
In this example, you need to intersect the two tables instead of taking their union. The code to accom-
plish this is as follows:
JOIN Orders, Customer
WHERE Orders.CustomerNum=Customer.CustomerNum
GIVING Temp1
PROJECT Temp1 OVER CustomerNum, CustomerName
GIVING Temp2
SELECT Customer WHERE RepNum=
'
65
'
GIVING Temp3
PROJECT Temp3 OVER CustomerNum, CustomerName
GIVING Temp4
INTERSECT Temp2 WITH Temp4 GIVING Answer
