在本教程中，我们将学习有关MySQL中的左连接的知识。

当我们使用LEFT JOIN时，它将返回左侧表中的所有行，而仅返回右侧表中的匹配行。
如果右侧表中没有匹配项，则得到" NULL"。

下面给出了LEFT JOIN的图示。

在本教程中，我们将使用"员工"和"评论"表。

员工表

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     7 | 1900-01-01 | 2016-02-04 06:08:10 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     8 | 1900-02-03 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     9 | 1900-05-20 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     6 | 1900-10-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

评论表

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2016-01-02 01:02:03 | 2016-01-02 01:02:03 |
|         3 | e01        | Happy               | 2016-01-02 01:04:03 | 2016-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

LEFT JOIN语法

SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
LEFT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;

LEFT JOIN员工和评论表

在下面的示例中，我们将列出所有发表评论的员工。
对于那些未评论的人，其列中将为NULL。

因此，为此，我们将左连接" employeeid"列上的两个表。
这将获取两个表中具有匹配employeeid的所有行。
如果右侧表(即" comments"表)中没有匹配的employeeid，那么我们将获得" NULL"。

mysql> SELECT e.employeeid, e.firstname, e.lastname, c.commentid, c.commentbody
FROM employee e
LEFT JOIN comments c
ON e.employeeid = c.employeeid
ORDER BY employeeid;

+------------+-----------+----------+-----------+---------------------+
| employeeid | firstname | lastname | commentid | commentbody         |
+------------+-----------+----------+-----------+---------------------+
| e01        |      |   |         3 | Happy               |
| e01        |      |   |         5 | Keep coding....     |
| e02        | John      | Doe      |      NULL | NULL                |
| e03        | Jane      | Doe      |         1 | Awesome             |
| e03        | Jane      | Doe      |         2 | Hello World         |
| e04        | Tin       | Tin      |         4 | This is my comment. |
| e05        | Bob       | Coder    |      NULL | NULL                |
+------------+-----------+----------+-----------+---------------------+
7 rows in set (0.00 sec)