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
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.
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
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.