YouTip

\\n\\n

SQL LEFT JOIN Keyword

\\n\\n

LEFT JOIN is a join keyword in SQL used to retrieve data from multiple tables.

\\n\\n

The difference between LEFT JOIN and INNER JOIN is that LEFT JOIN returns all records from the left table, even if there are no matching records in the right table.

\\n\\n

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.

\\n\\n

SQL LEFT JOIN Syntax

\\n\\n

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

\\nor:\\n\\n

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

\\nNote: In some databases, LEFT JOIN is referred to as LEFT OUTER JOIN.\\n\\n

\\n
• table1: Left table (primary table), all records from this table will be retained by LEFT JOIN.
\\n
• table2: Right table (secondary table), if no matching data exists, corresponding columns are filled with NULL.
\\n
• ON table1.column_name=table2.column_name: Specifies the join condition, usually a common field between the two tables.
\\n

\\n\\n

\\n\\n

Characteristics:

\\n

\\n
• Returns all records from the left table, even if there is no matching data in the right table.
\\n
• If there is no matching record in the right table, the corresponding fields in the result will be NULL.
\\n

\\n\\n

Suppose we have two tables: Customers and Orders.

\\n\\n

Customers Table:

\\n\\n\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n

CustomerID	Name
1	Alice
2	Bob
3	Charlie
4	David

\\n\\n

Orders Table:

\\n\\n\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n

OrderID	CustomerID	Product
101	1	Laptop
102	2	Smartphone

\\n\\n

Using LEFT JOIN Query:

\\n\\n

SELECT Customers.Name, Orders.Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;\\n

\\n\\n

Query Output Result:

\\n\\n\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n

Name	Product
Alice	Laptop
Bob	Smartphone
Charlie	NULL
David	NULL

\\n\\n

Explanation:

\\n

LEFT JOIN returns all records from the Customers table. For Charlie and David, since there is no matching CustomerID in the Orders table, their corresponding Product columns are NULL.

\\n\\n

---

\\n\\n

Demo Database

\\n\\n

In this tutorial, we will use the sample database.

\\n\\n

Here is some data selected from the "Websites" table:

\\n\\n

+----+--------------+---------------------------+-------+---------+\\n| id | name         | url                       | alexa | country |\\n+----+--------------+---------------------------+-------+---------+\\n| 1  | Google       | https://www.google.cm/    | 1     | USA     |\\n| 2  | Taobao           | https://www.taobao.com/   | 13    | CN      |\\n| 3  |        |     | 4689  | CN      |\\n| 4  | Weibo           | http://weibo.com/         | 20    | CN      |\\n| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |\\n| 7  | stackoverflow | http://stackoverflow.com/ | 0     | IND     |\\n+----+---------------+---------------------------+-------+---------+\\n

\\n\\n

Here is the data from the "access_log" website access log table:

\\n\\n

mysql> SELECT * FROM access_log;\\n+-----+---------+-------+------------+\\n| aid | site_id | count | date       |\\n+-----+---------+-------+------------+\\n| 1   | 1       | 45    | 2016-05-10 |\\n| 2   | 3       | 100   | 2016-05-13 |\\n| 3   | 1       | 230   | 2016-05-14 |\\n| 4   | 2       | 10    | 2016-05-14 |\\n| 5   | 5       | 205   | 2016-05-14 |\\n| 6   | 4       | 13    | 2016-05-15 |\\n| 7   | 3       | 220   | 2016-05-15 |\\n| 8   | 5       | 545   | 2016-05-16 |\\n| 9   | 3       | 201   | 2016-05-17 |\\n+-----+---------+-------+------------+\\n9 rows in set (0.00 sec)\\n

\\n\\n

---

\\n\\n

SQL LEFT JOIN Example

\\n\\n

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

\\n\\n

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

\\n\\n

Example

\\n\\n

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

\\n\\n

Executing the above SQL produces the following output:

\\n\\n

\\n\\n

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