MySQL LEFT JOIN

In the SQL language, the LEFT JOIN command (also called LEFT OUTER JOIN) is a type of join between 2 tables. It allows listing all the results of the left table even if there is no match in the second table.

 
 

 

Syntax:

To list the rows of table1, even if there is no match with table2, you need to perform an SQL query using the following syntax.

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.fk_id

The query can also be written as follows:

SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.id = table2.fk_id

This query is particularly interesting to get the information from table1 while getting the associated data, even if there is no match with table2. Note that if there is no match, the columns of table2 will all be NULL.
 

Example :

Let’s say we have an application that contains users and orders for each of these users. The database of this application contains a table for the users and saves their purchases in a second table. The 2 tables are linked thanks to the user_id column of the orders table. This allows you to associate an order with a user.
 
Users table:

> SELECT * FROM Users;

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 

 
Orders table:

> SELECT * FROM Orders;

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |   UserID    |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |      1      |
|     2   |   9902    |  300.96  |      1      |
|     3   |   9903    |  145.03  |      1      |
|     4   |   9904    |  744.33  |      4      |
+---------+-----------+----------+-------------+

To list all users with their orders and also display the users who have not made any purchases, it is possible to use the following query:

SELECT UserID, Name, NumOrder, Total
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID

 
Output:

+---------+-----------+----------+-------------+
|  UserID |   Name    | NumOrder |    Total    |
+---------+-----------+----------+-------------+
|     1   |   Alex    |  9901    |  254.00     |
|     1   |   Alex    |  9902    |  300.96     |
|     1   |   Alex    |  9903    |  145.03     |
|     2   |   Emily   |  NULL    |  NULL       |
|     3   |   Jean    |  NULL    |  NULL       |
|     4   |   Bob     |  9904    |  744.33     |
+---------+-----------+----------+-------------+

The two before the last row show users who have not made any orders. The line returns NULL for the columns concerning the purchases they did not make.
 

 

Filter on NULL value

Be careful, the NULL value is not a character string. To filter on these characters you must use the IS NULL statement. For example, to list the users who have not made any purchases, you can use the following query.

SELECT UserID, Name, NumOrder, Total
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID
WHERE NumOrder IS NULL

 
Output:

+---------+-----------+----------+-------------+
|  UserID |   Name    | NumOrder |    Total    |
+---------+-----------+----------+-------------+
|     2   |   Emily   |  NULL    |  NULL       |
|     3   |   Jean    |  NULL    |  NULL       |
+---------+-----------+----------+-------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More