JOIN
Description
MariaDB supporta le seguenti sintassi di JOIN
per la parte riferimenti_tabelle
della sintassi di SELECT
e delle DELETE
e UPDATE
multi-tabella:
riferimenti_tabelle: riferimento_tabella [, riferimento_tabella] ... riferimento_tabella: fattore_tabella | tabella_join fattore_tabella: nome_tabella [[AS] alias] [elenco_indici] | tabella_subquery [AS] alias | ( riferimenti_tabelle ) | { ON riferimento_tabella LEFT OUTER JOIN riferimento_tabella ON espre_condizionale } join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON conditional_expr | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor join_condition: ON conditional_expr | USING (column_list) index_hint_list: index_hint [, index_hint] ... index_hint: USE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | IGNORE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list) | FORCE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list) 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 MariaDB, CROSS JOIN
is a syntactic equivalent to
INNER JOIN
(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 general, parentheses can be ignored in join expressions containing only inner join operations. MariaDB also supports nested joins (see http://dev.mysql.com/doc/refman/5.1/en/nested-join-optimization.html).
Index hints can be specified to affect how the MariaDB optimizer makes use of indexes. For more information, see http://dev.mysql.com/doc/refman/5.1/en/index-hints.html.
Examples
SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;