Description\n-----------\n\nMariaDB supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:\n\n``sql\ntable_references:\n    table_reference [, table_reference] ...\n\ntable_reference:\n    table_factor\n  | join_table\n\ntable_factor (<= MariaDB 11.6):\n    tbl_name [PARTITION (partition_list)]\n        [query_system_time_period_specification] [[AS] alias] [index_hint_list]\n  | table_subquery [query_system_time_period_specification] [AS] alias\n  | ( table_references )\n  | { ON table_reference LEFT OUTER JOIN table_reference\n        ON conditional_expr }\n\ntable_factor (>= MariaDB 11.7):\n    tbl_name [PARTITION (partition_list)]\n        [query_system_time_period_specification] [[AS] alias] [index_hint_list]\n  | table_subquery [query_system_time_period_specification] [AS] alias [(column_name_list)] \n  | ( table_references )\n  | { ON table_reference LEFT OUTER JOIN table_reference\n        ON conditional_expr }\n\njoin_table:\n    table_reference [INNER | CROSS] JOIN table_factor [join_condition]\n  | table_reference STRAIGHT_JOIN table_factor\n  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr\n  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition\n  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor\n\njoin_condition:\n    ON conditional_expr\n  | USING (column_list)\n\nquery_system_time_period_specification:\n    FOR SYSTEM_TIME AS OF point_in_time\n  | FOR SYSTEM_TIME BETWEEN point_in_time AND point_in_time\n  | FOR SYSTEM_TIME FROM point_in_time TO point_in_time\n  | FOR SYSTEM_TIME ALL\n\npoint_in_time:\n    [TIMESTAMP] expression\n  | TRANSACTION expression\n\nindex_hint_list:\n    index_hint [, index_hint] ...\n\nindex_hint:\n    USE {INDEX|KEY}\n      [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])\n  | IGNORE {INDEX|KEY}\n      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)\n  | FORCE {INDEX|KEY}\n      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)\n\nindex_list:\n    index_name [, index_name] ...\n`\n\nA _table reference_ is also known as a _join expression_.\n\nEach table can also be specified as db_name.tabl_name. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.\n\nThe 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.\n\nThis 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:\n\n`sql\nSELECT  FROM t1 LEFT JOIN (t2, t3, t4)\n                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)\n`\n\nIt is equivalent to this query:\n\n`sql\nSELECT  FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)\n                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)\n`\n\nIn MariaDB, CROSS JOIN is a syntactic equivalent toINNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with anON clause, CROSS JOIN is used otherwise.\n\nIn general, parentheses can be ignored in join expressions containing only inner join operations. MariaDB also supports nested joins (see Nested Join Optimization).\n\nSubqueries\n\nA table subquery is specified as a parenthesized query and must contain a following derived table name (specified as _alias_ in the above syntax specification).\n\nYou can optionally specify a list of column names in parenthesis.\n\n`sql\nSELECT ic1, ic2, ic3 FROM\n      (\n        SELECT c1, c2, c3 FROM t1 GROUP BY c1\n      ) dt2 (ic1, ic2, ic3)\n    JOIN t2 ON t2.c1 = dt2.ic1\n    WHERE c2 > 0\n    GROUP BY ic1\n`\n\nHere, 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.\n\nYou cannot optionally specify a list of column names in parenthesis.\n\nSee also Correlation Column List.\n\nSystem-Versioned Tabled\n\nSee System-versioned tables for more information about the FOR SYSTEM_TIME syntax.\n\nIndex Hints\n\nIndex hints can be specified to affect how the MariaDB optimizer makes use of indexes. For more information, see How to force query plans.\n\nOracle Mode\n\nThis feature is available from MariaDB 12.1.\n\nOverview\n\nWhen Oracle mode is active, the Oracle-style (+)  syntax can be used. For example, the following two queries are identical:\n\n`sql\nSELECT  FROM t1 LEFT JOIN t2 ON t1.a = t2.b;\n`\n\n`sql\nSELECT  FROM t1, t2 WHERE t1.a = t2.b(+);\n`\n\nSimilarly, the following two queries are identical:\n\n`sql\nSELECT  FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;\n`\n\n`sql\nSELECT  FROM t1, t2 WHERE t1.a(+) = t2.b;\n`\n\nWith more than two tables, these two queries are identical:\n\n`sql\nSELECT  FROM t1 LEFT JOIN t2 ON t1.a = t2.a LEFT JOIN t3 ON t2.a = t3.a\n`\n\n`sql\nSELECT  FROM t1, t2, t3 WHERE t1.a = t2.a(+) AND t2.a = t3.a(+)\n`\n\nTo "rewrite" a join query using the (+) syntax, use EXPLAIN EXTENDED (the last line is an approximation at using the "regular" LEFT JOIN syntax):\n\n`sql\nEXPLAIN EXTENDED\n    SELECT  FROM t1, t2, t3 WHERE t1.a = t2.a(+) AND t2.a = t3.a(+);\nid	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra\n1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	\n1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)\n1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)\nWarnings:\nNote	1003	select "test"."t1"."a" AS "a","test"."t2"."a" AS "a","test"."t3"."a" AS "a" from "test"."t1" left join "test"."t2" on("test"."t2"."a" = "test"."t1"."a") left join "test"."t3" on("test"."t3"."a" = "test"."t2"."a") where 1\n`\n\nLimitations\n\nThe table whose columns are marked with the (+) operator in a subexpression (a part of the WHERE clause divided by AND) are the inner part of the expression. A table whose columns are not marked with the operator belong to the outer part.\n\nExample of a single subexpression within a WHERE clause:\n\n`sql\n... WHERE t1.a = t2.a(+)\n`\n\nExample of two subexpressions within a WHERE clause – here, both t1.a = t2.a(+)  and t2.a = t3.a(+) are inner parts, because both contain a (+) operator:\n\n`sql\n... WHERE t1.a = t2.a(+) AND t2.a = t3.a(+)\n`\n\nExample of two subexpressions within a WHERE clause – here, t1.a = t2.a(+) is the inner part (because of the (+) operator), and t2.a = 42 is the outer part (it doesn't have a (+) operator:\n\n`sql\n... WHERE t1.a = t2.a(+) AND t2.a = 42\n`\n\n"Rewritten" as a "regular" join, that clause looks like this:\n\n`sql\n... FROM t1 LEFT JOIN t2 ON (t1.a = t2.a) WHERE t2.a = 42\n`\n\nThe following limitations apply:\n\n The (+) operator can only be used in a WHERE clause.\n The (+) operator can only be applied to a table column, and the column should be from the local SELECT, not from an outer SELECT.\n The (+) operator cannot be used with other JOIN methods – it must be a comma-separated list in the FROM clause.\n When the WHERE clause is split into subexpressions by AND, (+) cannot be used.\n The (+) operator cannot be used on the right side of an IN function.\n The (+) operator cannot be used in row operations.\n The (+) operator cannot be used when two or more tables are on one side of and marked with the (+) operator and some are not.\n The (+) operator cannot create loops (or cycles) of dependence, where the same table appears on both sides of the operator in one expression, or through a chain of expressions.\n\nError Codes\n\nThe following errors may occur when not adhering to the (+) operator limitations or for other reasons:\n\n ER_INVALID_USE_OF_ORA_JOIN\n ER_INVALID_USE_OF_ORA_JOIN_OUTER_REF\n ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC\n ER_INVALID_USE_OF_ORA_JOIN_ONE_TABLE\n ER_INVALID_USE_OF_ORA_JOIN_CYCLE\n\nYou can find examples of the errors by error code in these files (which are available on GitHub):\n\n mysql-test/suite/compat/oracle/t/ora_outer_join.test\n mysql-test/suite/compat/oracle/t/ora_outer_join_err.test`\n\nExamples\n--------\n\nSELECT left_tbl.*\n  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id\n  WHERE right_tbl.id IS NULL;\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins/join-syntax