SQL LEFT JOIN
SQL LEFT JOIN keyword
The LEFT JOIN keyword returns all rows from the left table (table1), even if there is no match in the right table (table2). If there is no match in the right table, the result is NULL.
SQL LEFT JOIN syntax
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
Or:
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2 .column_name;
Note: In some databases, LEFT JOIN is called LEFT OUTER JOIN.
Demo Database
In this tutorial we will use the php sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+------+---------+
The following is the data of the "access_log" website access record table:
+-----+--------- +-------+------------+
| aid | site_id | count | date |
+-----+------ ---+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016 -05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 201 6-05 -16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+----------+------+--- ---------+
9 rows in set (0.00 sec)
SQL LEFT JOIN Example
The following SQL statement will return all Websites and their traffic volume (if any).
In the following example, we use Websites as the left table and access_log as the right table:
Example
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
Execute the above SQL and the output result is as follows:
Comment:LEFT JOIN keyword Returns all rows from the left table (Websites), even if there are no matches in the right table (access_log).
