# Joining Tables with JOIN Clauses This guide offers a simple, hands-on introduction to three basic `JOIN` types in MariaDB: `INNER JOIN`, `CROSS JOIN`, and `LEFT JOIN`. Use these examples to understand how different joins combine data from multiple tables based on specified conditions. ### Setup: Example Tables and Data First, create and populate two simple tables, `t1` and `t2`, to use in the `JOIN` examples: ```sql CREATE TABLE t1 ( a INT ); CREATE TABLE t2 ( b INT ); INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t2 VALUES (2), (4); ``` ### JOIN Examples and Output Below are examples of different `JOIN` types using the tables `t1` and `t2`. #### INNER JOIN An `INNER JOIN` produces a result set containing only rows that have a match in both tables for the specified join condition(s). ```sql SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b; ``` **Output:** ``` +------+------+ | a | b | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) ``` *Explanation:* Only the row where `t1.a` (value 2) matches `t2.b` (value 2) is returned. #### CROSS JOIN A `CROSS JOIN` produces a result set in which every row from the first table is joined to every row in the second table. This is also known as a Cartesian product. ```sql SELECT * FROM t1 CROSS JOIN t2; ``` **Output:** ``` +------+------+ | a | b | +------+------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 1 | 4 | | 2 | 4 | | 3 | 4 | +------+------+ 6 rows in set (0.00 sec) ``` *Explanation:* Each of the 3 rows in `t1` is combined with each of the 2 rows in `t2`, resulting in 3 \* 2 = 6 rows. *Note:* In MariaDB, the `CROSS` keyword can often be omitted if no `ON` clause is present (e.g., `SELECT * FROM t1 JOIN t2;` or `SELECT * FROM t1, t2;` would also produce a Cartesian product). #### LEFT JOIN (t1 LEFT JOIN t2) A `LEFT JOIN` (or `LEFT OUTER JOIN`) produces a result set with all rows from the "left" table (`t1` in this case). If a match is found in the "right" table (`t2`), the corresponding columns from the right table are included. If no match is found, these columns are filled with `NULL`. ```sql SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b; ``` **Output:** ``` +------+------+ | a | b | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.00 sec) ``` *Explanation:* All rows from `t1` are present. For `t1.a = 1` and `t1.a = 3`, there are no matching `t2.b` values, so `b` is `NULL`. For `t1.a = 2`, a match is found (`t2.b = 2`), so `b` is `2`. > Starting with MariaDB 12.3, multiple independent `LEFT JOIN` operations can be reordered by the optimizer for better performance if the [reorder\_outer\_joins](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/reorder_outer_joins) flag is enabled in [optimizer\_switch](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer-switch)*.* It is also recommended to set `optimizer_prune_level=0` to prevent heuristic pruning from eliminating optimal join orders. #### RIGHT JOIN (t1 RIGHT JOIN t2) A `RIGHT JOIN` (or `RIGHT OUTER JOIN`) produces a result set with all rows from the "right" table (`t2` in this case). If a match is found in the "left" table (`t1`), the corresponding columns from the left table are included. If no match is found, these columns are filled with `NULL`. ```sql SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b; ``` **Output:** ``` +------+------+ | a | b | +------+------+ | 2 | 2 | | NULL | 4 | +------+------+ 2 rows in set (0.00 sec) ``` #### LEFT JOIN (t2 LEFT JOIN t1) - Simulating a RIGHT JOIN This example uses a `LEFT JOIN` but with `t2` as the left table. This effectively demonstrates how a `RIGHT JOIN` would behave if `t1` were the left table and `t2` the right. A `RIGHT JOIN` includes all rows from the "right" table and `NULL`s for non-matching "left" table columns. ```sql SELECT * FROM t2 LEFT JOIN t1 ON t1.a = t2.b; ``` **Output:** ``` +------+------+ | b | a | +------+------+ | 2 | 2 | | 4 | NULL | +------+------+ 2 rows in set (0.00 sec) ``` *Explanation:* All rows from `t2` are present. For `t2.b = 2`, a match is found (`t1.a = 2`), so `a` is `2`. For `t2.b = 4`, there is no matching `t1.a` value, so `a` is `NULL`. ### Older (Implicit) JOIN Syntax The first two `SELECT` statements (`INNER JOIN` and `CROSS JOIN`) are sometimes written using an older, implicit join syntax: * **Implicit INNER JOIN:** ```sql SELECT * FROM t1, t2 WHERE t1.a = t2.b; ``` This is equivalent to `SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;`. * **Implicit CROSS JOIN (Cartesian Product):** ```sql SELECT * FROM t1, t2; ``` This is equivalent to `SELECT * FROM t1 CROSS JOIN t2;`. While this syntax works, the explicit `JOIN` syntax (`INNER JOIN`, `LEFT JOIN`, etc.) with an `ON` clause is generally preferred for clarity and to better distinguish join conditions from filtering conditions (`WHERE` clause). ### Understanding JOIN Types Summary * **`INNER JOIN`**: Returns rows only when there is a match in both tables based on the join condition. * **`CROSS JOIN`**: Returns the Cartesian product of the two tables (all possible combinations of rows). * **`LEFT JOIN` (Outer Join)**: Returns all rows from the left table, and the matched rows from the right table. If there is no match, `NULL` is returned for columns from the right table. * **`RIGHT JOIN` (Outer Join)**: Returns all rows from the right table, and the matched rows from the left table. If there is no match, `NULL` is returned for columns from the left table. (The example `SELECT * FROM t2 LEFT JOIN t1 ...` shows this behavior from `t1`'s perspective). ### Joining Multiple Tables `JOIN` clauses can be concatenated (chained) to retrieve results from three or more tables by progressively joining them. ### See Also * [More Advanced Joins](https://mariadb.com/docs/server/mariadb-quickstart-guides/more-advanced-joins) * [JOIN Syntax](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins/join-syntax) * [Comma vs JOIN](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins/comma-vs-join) * [Joins, Subqueries and SET](https://mariadb.com/docs/server/reference/sql-structure/joins-subqueries-set) *The initial version of this article was copied, with permission, from* [*Introduction\_to\_Joins*](https://hashmysql.org/wiki/Introduction_to_Joins) *on 2012-10-05.* *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}