Latest web development tutorials

SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

LEFT JOIN keyword from the left table (table1) returns all rows, even if there is no match for the right table (table2) in. If there is no match in the right table, the result is NULL.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name ;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name ;

NOTE: In some databases, LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN


The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 本教程      | http://www.w3big.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data "access_log" website access record table:

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| 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 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL LEFT JOIN examples

The following SQL statement will return all the sites and their views (if any).

The following examples of the Websites as we left the table, access_log as the right table:

Examples

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id = access_log.site_id
ORDER BY access_log.count DESC;

Execute the above SQL output results are as follows:

NOTE: LEFT JOIN keyword from the left table (Websites) returns all rows, even if there is no match for the right table (access_log) in.