Retrieve data from multiple tables based on related columns. This section covers INNER, LEFT, RIGHT, and CROSS JOIN syntax and usage.
Main JOIN page, documenting all join types available in MariaDB.
Introduction to joining tables in MariaDB.
Introduction to advanced joins in MariaDB.
How to use a comma instead of a JOIN clause, and why you should always prefer the latter.
Review the full syntax for SQL joins in MariaDB. This guide details the structure of table references, index hints, and various join types supported in SELECT, UPDATE, and DELETE statements.
MariaDB supports the following JOIN syntaxes for the table_references part of statements and multiple-table and statements:
A table reference is also known as a join expression.
Each table can also be specified as db_name.tabl_name. This allows to write queries which involve multiple databases. See for syntax details.
The syntax of table_factor is an extension to 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. Consider this query:
It is equivalent to this query:
In general, parentheses can be ignored in join expressions containing only inner join operations. MariaDB also supports nested joins (see ).
A table subquery is specified as a parenthesized query and must contain a following derived table name (specified as alias in the above syntax specification).
You can optionally specify a list of column names in parenthesis.
Here, the table subquery for t1 will be materialized and named dt2, with column names ic1, ic2, ic3. These column names are used outside the subquery.
You cannot optionally specify a list of column names in parenthesis.
See also .
See for more information about the FOR SYSTEM_TIME syntax.
Index hints can be specified to affect how the MariaDB optimizer makes use of indexes. For more information, see .
When is active, the Oracle-style + syntax can be used. For example, the following two queries are identical:
and
Similarly, the following two queries are identical:
and
This page is licensed: GPLv2, originally from
table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor (<= MariaDB 11.6):
tbl_name [PARTITION (partition_list)]
[query_system_time_period_specification] [[AS] alias] [index_hint_list]
| table_subquery [query_system_time_period_specification] [AS] alias
| ( table_references )
| { ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
table_factor (>= MariaDB 11.7):
tbl_name [PARTITION (partition_list)]
[query_system_time_period_specification] [[AS] alias] [index_hint_list]
| table_subquery [query_system_time_period_specification] [AS] alias [(column_name_list)]
| ( table_references )
| { ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
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)
query_system_time_period_specification:
FOR SYSTEM_TIME AS OF point_in_time
| FOR SYSTEM_TIME BETWEEN point_in_time AND point_in_time
| FOR SYSTEM_TIME FROM point_in_time TO point_in_time
| FOR SYSTEM_TIME ALL
point_in_time:
[TIMESTAMP] expression
| TRANSACTION expression
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] ...SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)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)SELECT ic1, ic2, ic3 FROM
(
SELECT c1, c2, c3 FROM t1 GROUP BY c1
) dt2 (ic1, ic2, ic3)
JOIN t2 ON t2.c1 = dt2.ic1
WHERE c2 > 0
GROUP BY ic1SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;SELECT * FROM t1, t2 WHERE t1.a = t2.b(+);SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;SELECT * FROM t1, t2 WHERE t1.a(+) = t2.b;SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;Compare implicit and explicit join syntax. This article explains why using the explicit JOIN syntax with an ON clause is preferred over comma-separated tables for readability and precedence.
A query to retrieve the list of phone numbers for clients who ordered in the last two weeks might be written in a couple of ways. Here are two:
SELECT *
FROM
clients
INNER JOIN orders ON clients.id = orders.clientId
INNER JOIN phoneNumbers ON clients.id = phoneNumbers.clientId
WHERE
orderPlaced >= NOW() - INTERVAL 2 WEEK;Does it make a difference? Not much as written. But you should use the second form, for these reasons:
Readability. Once the WHERE clause contains more than two conditions, it becomes tedious to pick out the difference between business logic (only dates in the last two weeks) and relational logic (which fields relate clients to orders). Using the JOIN syntax with an ON clause makes the WHERE list shorter, and makes it very easy to see how tables relate to each other.
Flexibility. Let's say we need to see all clients even if they don't have a phone number in the system. With the second version, it's easy; just change INNER JOIN phoneNumbers to LEFT JOIN phoneNumbers. Try that with the first version, and recent MySQL versions will issue a syntax error because of the change in precedence between the comma operator and the JOIN keyword. The solution is to rearrange the FROM clause or add parentheses to override the precedence, and that quickly becomes frustrating.
Portability. If your queries use standard SQL syntax, you will have an easier time switching to a different database should the need ever arise.
— A blog entry about this topic.
The initial version of this article was copied, with permission, from on 2012-10-05.
This page is licensed: CC BY-SA / Gnu FDL
SELECT *
FROM
clients,
orders,
phoneNumbers
WHERE
clients.id = orders.clientId
AND clients.id = phoneNumbers.clientId
AND orderPlaced >= NOW() - INTERVAL 2 WEEK;