A LEFT JOIN query is a LEFT OUTER JOIN query that returns all records in the left-side table and the matching records in the right-side table. The matching records in the right-side table are records that meet the join condition.
Background information
An OUTER JOIN query uses a comparison operator to compare the data in two tables. The join results include the rows that meet the join conditions and those do not meet the join conditions.
OUTER JOIN queries include FULL JOIN queries, LEFT JOIN queries, and RIGHT JOIN queries. An OUTER JOIN query returns all the rows that meet the join conditions. In addition, it returns unused rows of one table and fills NULL in the corresponding positions in the other table.
This topic describes how to use LEFT JOIN queries. For more information about FULL JOIN queries and RIGHT JOIN queries, see FULL JOIN queries and RIGHT JOIN queries.
Syntax
Generally, an OUTER JOIN statement contains a left-side table and a right-side table. The leftmost table in the JOIN clause is the left-side table, and the rightmost table in the JOIN clause is the right-side table.
The syntax 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-side table and table_name2 is the right-side table.
Examples
Create a table and insert proper data into the table.
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 queries
To use a JOIN clause to return data that meets the join and filter conditions and also to return data from the left-side table that meets the filter condition specified for the left-side table but does not meet the join condition, you can use LEFT OUTER JOIN, or LEFT JOIN for short.
In a LEFT JOIN query, the rows in the left-side table matching no rows in the right-side table are returned with the NULL value.
Example 1: Execute a LEFT JOIN query on tables tbl_a and tbl_b and obtain the returned results.
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 queries
Here is an 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)
LEFT JOIN queries with a WHERE clause
You can use a LEFT JOIN query to obtain the join result and then use the WHERE clause to filter the result.
Example 2: Execute a LEFT JOIN query on tables tbl_a and tbl_b and return the data that meets the name='ab' condition in the tbl_a table.
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