WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL LEFT JOIN Keyword


SQL LEFT JOIN keyword

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

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 Called LEFT OUTER JOIN。

SQL LEFT JOIN


Demo database

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

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/ | 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/| 13    | IN            |
| 3  | zomato      | http://www.zomato.com/ | 5000    | IN            |
| 4  | indiamart          | http://indiamart.com/      | 20    | IN            |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data for the "access_log" site visit log:

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 traffic (if any).

In the following example, we use Websites as the left table and access_log 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;

The result of executing the above SQL is as follows:

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