Find sid’s of sailors who’ve reserved a red and a green boat

• INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples.

• Included in the SQL/92 standard, but some systems don’t support it.

• Contrast symmetry of the UNION and INTERSECT queries with how much the other versions differ.

SELECT S.sid

FROM Sailors S, Boats B1, Reserves R1,

Boats B2, Reserves R2

WHERE S.sid=R1.sid AND R1.bid=B1.bid

AND S.sid=R2.sid AND R2.bid=B2.bid

AND (B1.color=‘red’ AND B2.color=‘green’)

SELECT S.sid

FROM Sailors S, Boats B, Reserves R

WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’

INTERSECT

SELECT S.sid

FROM Sailors S, Boats B, Reserves R

WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’

Key field!

Previous slide

Next slide

Back to first slide

View graphic version