LEFT JOIN
A LEFT JOIN is a type of outer join. It returns all records from the left table and the matching records from the right table. If there is no match in the right table, the result will contain NULL values for the columns from the right table.
Background information
An outer join is a type of join that returns all records from both tables, even if there is no match between them. This includes rows that do not meet the join condition.
Outer joins include FULL JOIN, LEFT JOIN, and RIGHT JOIN. An outer join returns all rows that meet the join condition and also returns rows from one table that do not have a match in the other table, filling in NULL values for the unmatched columns.
This topic provides examples of LEFT JOIN. For examples of FULL JOIN, see FULL JOIN. For examples of RIGHT JOIN, see RIGHT JOIN.
LEFT JOIN syntax
An outer join statement typically includes a left table and a right table. The table that appears first in the JOIN clause is the left table, and the table that appears last in the JOIN clause is the right table.
The SQL syntax for a LEFT JOIN is as follows:
SELECT select_list FROM table_name1 LEFT JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ];
In this syntax, table_name1 is the left table, and table_name2 is the right table.
Examples
Create example tables and insert appropriate data.
CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
CREATE TABLE tbl_c(id INT NOT NULL , name VARCHAR(50));
Query OK, 0 rows affected (0.081 sec)
INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
INSERT INTO tbl_c VALUES(1,'aa'),(2,'dd'),(6,'ee'),(7,'hh'),(9,'xx');
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
Simple left join query
When you need to retrieve data from the JOIN operation that includes data that meets the join condition and filter condition, as well as data from the left table that meets the filter condition but not the join condition, you can use a LEFT OUTER JOIN, which can also be abbreviated as LEFT JOIN.
When you perform a LEFT JOIN query, if a row in the left table does not find a matching row in the right table, the result will automatically fill in NULL values for the columns from the right table.
Example 1: Perform a LEFT JOIN query on tables tbl_a and tbl_b and retrieve the result.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
SELECT * FROM tbl_a LEFT JOIN tbl_b ON tbl_a.id=tbl_b.number;
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 1 | ab | 1 | 1001 |
| 2 | cd | NULL | NULL |
| 3 | ef | 3 | 1003 |
| 4 | gh | NULL | NULL |
+------+------+--------+-------+
4 rows in set
Complex left join query
Here is an example:
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
SELECT * FROM tbl_c;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | dd |
| 6 | ee |
| 7 | hh |
| 9 | xx |
+------+------+
5 rows in set
SELECT * FROM tbl_a a LEFT JOIN tbl_b b LEFT JOIN tbl_c c ON c.id=b.number ON a.name=c.name;
+------+------+--------+-------+------+------+
| id | name | number | value | id | name |
+------+------+--------+-------+------+------+
| 1 | ab | NULL | NULL | NULL | NULL |
| 4 | gh | NULL | NULL | NULL | NULL |
| 3 | ef | NULL | NULL | NULL | NULL |
| 2 | cd | NULL | NULL | NULL | NULL |
+------+------+--------+-------+------+------+
4 rows in set
Using LEFT JOIN with the WHERE clause
You can use a LEFT JOIN to retrieve the join result and then use the WHERE clause to filter the join result.
Example 2: Perform a LEFT JOIN query on tables tbl_a and tbl_b and retrieve the data from tbl_a where name='ab'.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
SELECT * FROM tbl_a LEFT JOIN tbl_b ON tbl_a.id=tbl_b.number WHERE tbl_a.name='ab';
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 1 | ab | 1 | 1001 |
+------+------+--------+-------+
1 row in set