CHAPTER 12
12.1
A
NATURAL JOINB
: IllegalA
INTERSECTB
: Illegal SELECT * FROMA
NATURAL JOINB
: Legal SELECT * FROMA
INTERSECTB
: Illegal SELECT * FROM (A
NATURAL JOINB
) : Legal SELECT * FROM (A
INTERSECTB
) : Illegal SELECT * FROM ( SELECT * FROMA
INTERSECT SELECT * FROMB
) : Illegal SELECT * FROM (A
NATURAL JOINB
) ASC
: Illegal SELECT * FROM (A
INTERSECTB
) ASC
: Illegal TABLEA
NATURAL JOIN TABLEB
: Illegal TABLEA
INTERSECT TABLEB
: Legal SELECT * FROMA
INTERSECT SELECT * FROMB
: Legal ( SELECT * FROMA
) INTERSECT ( SELECT * FROMB
) : Legal ( SELECT * FROMA
) ASAA
INTERSECT ( SELECT * FROMB
) ASBB
: Illegal
You were also asked what you conclude from this exercise. One thing I conclude is that the rules are very difficult to remember (to say the least). In particular, SQL expressions involving INTERSECT can’t always be transformed straightforwardly into their JOIN counterparts. I remark also that if we replace INTERSECT by NATURAL JOIN in the last two expressions, then the legal one becomes illegal and vice versa! That’s because, believe it or not, the expressions
( SELECT * FROM A
)
and
( SELECT * FROM B
)
are considered to be subqueries in the context of NATURAL JOIN but not that of INTERSECT. (In other words, a subquery is a SELECT expression enclosed in parentheses, loosely speaking, but a SELECT expression enclosed in parentheses isn’t necessarily a subquery.)
12.2 The effects are as follows: The second expression was previously illegal but becomes ...
Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.