My SQL | LEFT JOIN
Start your free 7-days trial now!
MySQL's LEFT JOIN clause returns all rows from the left table and records that match from the right table. NULL will be used for records that have no matching record in the right table. LEFT JOIN is also referred to as LEFT OUTER JOIN.
Syntax
SELECT column_name(s)FROM left_tableLEFT JOIN right_tableON left_table.column = right_table.column;
Examples
Consider the following table about products bought by some customers:
id | product | bought_by |
|---|---|---|
1 | computer | 1 |
2 | hair dryer | 3 |
The above sample table can be created using the code here.
Also consider the following table containing customer information:
id | name | age |
|---|---|---|
1 | alex | 15 |
2 | bob | 25 |
3 | cathy | 30 |
The above sample table can be created using the code here.
Basic usage
To perform a LEFT JOIN with customer as the main (left) table and product as the secondary (right) table:
SELECT c.id, c.name, c.age, p.name AS product_name FROM customer cLEFT JOIN product pON c.id = p.bought_by;
+----+-----------+------+--------------+| id | name | age | product_name |+----+-----------+------+--------------+| 1 | alex | 15 | computer || 3 | cathy | 30 | hair dryer || 2 | bob | 25 | NULL |+----+-----------+------+--------------+
Note that we return all three records from the left table customer and matching records from the right table (i.e. where value of id in customer table is equal to value of bought_by in products table). As bob did not have any matching record in the right table (he did not buy anything!) NULL is returned.
We can also perform LEFT JOIN using more than two tables. However, there will always be one main table (all its rows will be present in the result), while the others will be secondary (rows will only appear if there are matches else NULL).
