Oracle 5.0, Reference Manual

Page: 1115 / 3232

SELECT
Syntax
1095
Alternatively, avoid the use of the comma operator and use
JOIN
instead:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
This change also applies to statements that mix the comma operator with
INNER JOIN
,
CROSS
JOIN
,
LEFT JOIN
, and
RIGHT JOIN
, all of which now have higher precedence than the comma
operator.
• Previously, the
ON
clause could refer to columns in tables named to its right. Now an
ON
clause can
refer only to its operands.
Example:
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
Previously, the
SELECT
statement was legal. Now the statement fails with an
Unknown column
'i3' in 'on clause'
error because
i3
is a column in
t3
, which is not an operand of the
ON
clause. The statement should be rewritten as follows:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
• Resolution of column names in
NATURAL
or
USING
joins is different than previously. For column
names that are outside the
FROM
clause, MySQL now handles a superset of the queries compared to
previously. That is, in cases when MySQL formerly issued an error that some column is ambiguous,
the query now is handled correctly. This is due to the fact that MySQL now treats the common
columns of
NATURAL
or
USING
joins as a single column, so when a query refers to such columns,
the query compiler does not consider them as ambiguous.
Example:
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
Previously, this query would produce an error
ERROR 1052 (23000): Column 'b' in where
clause is ambiguous
. Now the query produces the correct result:
+------+------+------+
| b | c | y |
+------+------+------+
| 4 | 2 | 3 |
+------+------+------+
One extension of MySQL compared to the SQL:2003 standard is that MySQL enables you to qualify
the common (coalesced) columns of
NATURAL
or
USING
joins (just as previously), while the standard
disallows that.
13.2.8.3. Index Hint Syntax
You can provide hints to give the optimizer information about how to choose indexes during query
processing.
Section 13.2.8.2, “
JOIN
Syntax” , describes the general syntax for specifying tables in a
SELECT
statement. The syntax for an individual table, including that for index hints, looks like this:
tbl_name [[AS] alias ] [ index_hint ]
index_hint :
USE {INDEX|KEY} [FOR JOIN] ( index_list )
| IGNORE {INDEX|KEY} [FOR JOIN] ( index_list )
| FORCE {INDEX|KEY} [FOR JOIN] ( index_list )
index_list :
index_name [, index_name ] ...