SQL LEFT JOIN

SQL LEFT JOIN statement returns all the records present in the left table and matched records from the right table. However, the result will contain null for the rows which are not present in the right table. In this article, we will discuss the SQL LEFT JOIN Statement. Also, we will discuss a few examples of writing queries.

Note: LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax

SELECT table1.column1, table2.column2 … FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

  • table1.column1: The columns which you need to fetch from table1.
  • table2.column2: The columns which you want to fetch from table2.
  • table1: The name of the first table.
  • table2: The name of the second table.
  • table1.common_field = table2.common_field: The Join Condition.

Also, you can use SQL Aliases in order to give a temporary name to table1 and table2. This can help in using short memorable names for the tables.

SELECT t1.column1, t2.column2 … FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.common_field = t2.common_field;

SQL LEFT JOIN
LEFT JOIN

Demo Tables

Refer the following tables for all the examples in this article. We have two tables: users and orders.

mysql> SELECT * FROM users;
+---------+----------+------------------+
| user_id | username | email_id         |
+---------+----------+------------------+
|       1 | john     | [email protected]   |
|       2 | emily    | [email protected]  |
|       3 | monica   | [email protected] |
|       4 | clark    | [email protected]  |
+---------+----------+------------------+

mysql> SELECT * FROM orders;
+----------+---------+-------------+------------+
| order_id | user_id | order_total | order_date |
+----------+---------+-------------+------------+
|        1 |       1 |         500 | 2019-07-01 |
|        2 |       1 |         400 | 2019-06-01 |
|        3 |       4 |        1000 | 2018-07-15 |
|        5 |       3 |         600 | 2019-06-05 |
+----------+---------+-------------+------------+

Example

For instance, we want to fetch all the user details from the table, whether they have placed an order or not, we can use LEFT JOIN. If we wanted to fetch only those users who have placed an order, we should use SQL INNER JOIN.

SELECT u.user_id, u.email_id, o.order_id, o.order_total 
FROM users AS u 
LEFT JOIN orders AS o ON u.user_id = o.user_id;
+---------+------------------+----------+-------------+
| user_id | email_id         | order_id | order_total |
+---------+------------------+----------+-------------+
|       1 | [email protected]   |        1 |         500 |
|       1 | [email protected]   |        2 |         400 |
|       4 | [email protected]  |        3 |        1000 |
|       3 | [email protected] |        5 |         600 |
|       2 | [email protected]  |     NULL |        NULL |
+---------+------------------+----------+-------------+

Since user_id 2 did not have a matching order in the orders table, the order_id and order_total corresponding to it is NULL.

Conclusion

In conclusion, we discussed the SQL LEFT JOIN. You can read more about it on Wikipedia. Additionally, you can also learn about SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *