SQL and Relational Theory, 2nd Edition

CHAPTER 12

12.1

  A NATURAL JOIN B : Illegal

A INTERSECT B : Illegal

SELECT * FROM A NATURAL JOIN B : Legal

SELECT * FROM A INTERSECT B : Illegal

SELECT * FROM ( A NATURAL JOIN B ) : Legal

SELECT * FROM ( A INTERSECT B ) : Illegal

SELECT * FROM ( SELECT * FROM A INTERSECT SELECT * FROM B ) : Illegal

SELECT * FROM ( A NATURAL JOIN B ) AS C : Illegal

SELECT * FROM ( A INTERSECT B ) AS C : Illegal

TABLE A NATURAL JOIN TABLE B : Illegal

TABLE A INTERSECT TABLE B : Legal

SELECT * FROM A INTERSECT SELECT * FROM B : Legal

( SELECT * FROM A ) INTERSECT ( SELECT * FROM B ) : Legal

( SELECT * FROM A ) AS AA INTERSECT ( SELECT * FROM B ) AS BB : 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 ...