SELECT
Syntax
1090
index_list
:
index_name
[,
index_name
] ...
A table reference is also known as a join expression.
The syntax of
table_factor
is extended in comparison with the SQL Standard. The latter accepts
only
table_reference
, not a list of them inside a pair of parentheses.
This is a conservative extension if we consider each comma in a list of
table_reference
items as
equivalent to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL,
JOIN
,
CROSS JOIN
, and
INNER JOIN
are syntactic equivalents (they can replace each
other). In standard SQL, they are not equivalent.
INNER JOIN
is used with an
ON
clause,
CROSS
JOIN
is used otherwise.
In versions of MySQL prior to 5.0.1, parentheses in
table_references
were just omitted and all
join operations were grouped to the left. In general, parentheses can be ignored in join expressions
containing only inner join operations. As of 5.0.1, nested joins are permitted (see
Section 8.3.1.9,
“Nested Join Optimization”
).
Further changes in join processing were made in 5.0.12 to make MySQL more compliant with standard
SQL. These charges are described later in this section.
Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more
information, see
Section 13.2.8.3, “Index Hint Syntax”
.
The following list describes general factors to take into account when writing joins.
• A table reference can be aliased using
tbl_name AS alias_name
or
tbl_name alias_name
:
SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
SELECT t1.name, t2.salary
FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
• A
table_subquery
is also known as a subquery in the
FROM
clause. Such subqueries must
include an alias to give the subquery result a table name. A trivial example follows; see also
Section 13.2.9.8, “Subqueries in the
FROM
Clause”
.
SELECT * FROM (SELECT 1, 2, 3) AS t1;
•
INNER JOIN
and
,
(comma) are semantically equivalent in the absence of a join condition: both
produce a Cartesian product between the specified tables (that is, each and every row in the first
table is joined to each and every row in the second table).
However, the precedence of the comma operator is less than of
INNER JOIN
,
CROSS JOIN
,
LEFT
JOIN
, and so on. If you mix comma joins with the other join types when there is a join condition, an
error of the form
Unknown column 'col_name' in 'on clause'
may occur. Information about
dealing with this problem is given later in this section.
• The
conditional_expr
used with
ON
is any conditional expression of the form that can be used
in a
WHERE
clause. Generally, you should use the
ON
clause for conditions that specify how to join
tables, and the
WHERE
clause to restrict which rows you want in the result set.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
