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 legal; the third, fifth, eleventh, twelfth, and thirteen were legal but become illegal; and the others were all illegal anyway and remain so. What do you conclude from this exercise?

12.3 It gives FALSE. Note, therefore (to spell the point out), it’s possible in SQL for two values to be “equal” and yet not “like” each other! (Lewis Carroll, where are you?)

12.4 The first gives:

(The point here is that BETWEEN is inclusive, not exclusive, and so 10 and 30 are both included in the result. Does this state of affairs accord with your own intuitive understanding of the meaning of between?) The second gives:

And the third gives:

London isn’t included in the result. The reason is that the expression

y BETWEEN x AND z

is shorthand for

x <= y AND y <= z

The problem here is that the natural language expression “y is between x and z” is symmetric in x and z (i.e., switching x and z has no effect on the meaning), while the same is not true for the SQL expression “y BETWEEN x AND z.” In a nutshell, BETWEEN in SQL doesn’t mean the same as between in natural language.

12.5 First of all, observe that both comparand expressions are subqueries, and they therefore evaluate to tables. Now, those tables both have exactly one column, a fact that can be determined at compile time. What’s more, given our usual sample values, they also both have exactly one row; the subqueries are therefore scalar subqueries, and the overall comparison is thus legal (a double coercion occurs on both sides, and the net effect is that two scalar values are compared). But suppose the WHERE clause in the second subquery had specified 12.0 instead of 14.0. Given our usual sample values, the comparison overall would then no longer be legal (it would fail at run time), because the second subquery would now be a table subquery instead of a scalar one.

12.6 No answer provided.

12.7 No answer provided.

12.8 No answer provided.

12.9 No answer provided.