SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

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

SQL LEFT JOIN Syntax

Or:

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

Demo Database

In this tutorial, we will use the tutorialpro sample database.

Below is the data from the "Websites" table:

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

Below is the data from the "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 Example

The following SQL statement will return all websites and their access counts (if any).

In this example, we use the Websites table as the left table and the access_log table as the right table:

Example

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;

Executing the above SQL will produce the following output:

Note: The LEFT JOIN keyword returns all rows from the left table (Websites), even if there are no matches in the right table (access_log).