SQL LEFT JOIN
Video Tutorial
Overview
In this article, you will learn how to use the LEFT JOIN clause in SQL to combine data from two or more tables based on a specific join condition. The article gives a thorough explanation of the syntax and application of the LEFT JOIN clause. It also includes examples of how to use it to retrieve data from multiple tables. By the end of the article, readers should have a solid understanding of using the LEFT JOIN clause effectively in their SQL queries.
Scope
This article will cover the following topics:
- Explanation of SQL JOINs and different types of JOINs, including LEFT JOIN.
- Syntax of the LEFT JOIN clause and how it differs from other JOINs.
- Examples of how to use LEFT JOIN to retrieve data from multiple tables.
- Need for LEFT JOIN in SQL.
What is LEFT JOIN in SQL?
A left join is a type of join in SQL that returns all the records from the left table and only those from the right table, which are matched to records in the left table.
For example, let us take the Customers table.
The Orders table for each customer could be viewed accordingly.
Now, let us join these two tables using the LEFT JOIN in SQL.
The joined table would look like this:
This means that a left join returns all the values from the left table (i.e., the ORDERS table in this case), plus matched values from the right table or NULL in case of no matching join predicate.
For example, look at the Venn diagram below.
Assume that you have two tables, Table A and Table B. A left join between Tables A and B would mean that all the records from Table A would be displayed, along with only those records from Table B, which are matched to Table A.
Syntax of LEFT JOIN
LEFT JOIN in SQL Example
Student Table
| student_name | age | class | roll_no |
|---|---|---|---|
| A Kumar | 18 | 12 | r1 |
| S Sinha | 19 | 11 | r2 |
| G Prasad | 18 | 12 | r3 |
Language_Marks Table
| r_no | subject | marks |
|---|---|---|
| r1 | French | 90 |
| r2 | German | 80 |
| r3 | Hindi | 85 |
To perform a left join on these two tables, we will use the following SQL query:
Abstract of Code
The above query displays the student_name, age, and class columns from the student table, and the subject, marks columns from the language_marks table.
Thus, the left table is joined to the right table using the *r_no* field.
Why Do We Need LEFT JOIN in SQL?
A left join is useful when a user wants to extract all the records from the left table. Even if there are no matched records in the right table, all the records from the left table would be displayed.
In the Customers and Orders table examples, we see that a left join is established on the Orders table and returns all the specified rows from the left table.
Conclusion
- LEFT JOIN in SQL are joins used to join the records from the left table to the matched records in the right table.
- Even if there are no matched records in the right table, the records from the left table are extracted.
- It is also called left outer join.