A left join (LEFT JOIN) is a left outer join, which returns all records from the left table and the matching records from the right table whose join fields are equal.
Background information
An outer join (OUTER JOIN) compares data in two tables using comparison operators. The join result includes not only rows that meet the join conditions but also rows that do not.
Outer joins include full joins (FULL JOIN), left joins (LEFT JOIN), and right joins (RIGHT JOIN). An outer join returns all rows that meet the join conditions, and returns unmatched rows from one table with NULL filled in the corresponding positions of the other table.
This topic provides examples of left joins. For examples of full joins, see FULL JOIN, and for examples related to right joins, see RIGHT JOIN.
LEFT JOIN syntax
An outer join statement generally includes a left table and a right table. The table farthest to the left in the JOIN clause is the left table, and the table farthest to the right is the right table.
The syntax of 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 ];
Here, table_name1 is the left table, and table_name2 is the right table.
Examples
Create sample tables and insert appropriate data.
obclient [info]> CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl_c(id INT NOT NULL , name VARCHAR(50));
Query OK, 0 rows affected (0.081 sec)
obclient [info]> INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
obclient [info]> INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient [info]> INSERT INTO tbl_c VALUES(1,'aa'),(2,'dd'),(6,'ee'),(7,'hh'),(9,'xx');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
Simple left join query
If you want the data returned by JOIN to include not only the data that meets the join and filter conditions but also the data in the left table that meets its own filter conditions but does not meet the join conditions, use a left outer join (LEFT OUTER JOIN), which can also be abbreviated as a left join (LEFT JOIN).
In a left join query, if a row in the left table cannot be found in the right table, NULL is automatically filled in for that row in the right table.
Example 1: Perform a left join query on tables tbl_a and tbl_b and obtain the result.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> 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
Example:
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set (0.002 sec)
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set (0.002 sec)
obclient> SELECT * FROM tbl_c;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | dd |
| 6 | ee |
| 7 | hh |
| 9 | xx |
+------+------+
5 rows in set (0.002 sec)
obclient> 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 (0.004 sec)
Combined use of left join and WHERE clause
You can use a left join to obtain 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 return the data where name='ab' in table tbl_a.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> 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