SQL LEFT JOIN – Examples and Syntax

SQL LEFT JOIN – Examples and Syntax

3 mins read4.3K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Mar 31, 2023 16:37 IST

SQL JOINS are used to extract the record from two or more interrelated tables. In the previous article, we briefly discussed SQL INNER JOIN. In this article, we will discuss how to use LEFT JOIN in SQL.

2022_08_MicrosoftTeams-image-233.jpg

Table of Content

What is SQL LEFT JOIN?

SQL LEFT JOIN returns all the rows from the left table (first table) and the corresponding matching rows from the right table (second table).

In simple terms, 

  • If any row is present in the first table (left table) but not in the second table (right table), then the result will include that row but with a NULL value in each column.
  • If any row is present in the second table (right) but not in the first table (left), then the result will not include that row.

Must Read: Joins in SQL

Must Read: SQL INNER JOIN

Let’s have a look at the below figure to get a better understanding:

In the above figure, we have two tables students and department table; when we left join student and department table, all the rows of the student table are included in the result, but with a NULL value in the department column as Student ID 1001, 1002, and 1003 are not in department table.

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

Syntax

 
SELECT column_names
FROM table_1
LEFT JOIN table_2
ON table_1.matching_column_name = table_2.matching_column_name;
Copy code

Now, let’s have an example to get a better understanding of SQL LEFT JOIN.

Let we have three tables, Student, Department, and Faculty tables:

StudentID Name E-mail Percentage(%) DepartmentID
1001 Ajay ajay@xyz.com 85 1
1002 Babloo babloo@xyz.com 67 2
1003 Chhavi chhavi@xyz.com 89 3
1004 Dheeraj dheeraj@xyz.com 75 4
1005 Evina evina@xyz.com 91 1
1006 Krishna krishna@xyz.com 99 5
Student
DepartmentID DepartmentName
1 Mathematics
2 Physics
3 English
Department
DepartmentID FacultyName
1 Piyush
2 Namita
3 Ashneer
4 Ghazal
5 Anupam
Faculty

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Example 1: Join the Student and Faculty table to extract the record from both the table where Student.DepartmentID = Faculty.DepartmentID.

Query

 
SELECT Student.StudentID, Student.Name, Faculty.FacultyName
FROM Student
LEFT JOIN Faculty
ON Student.DepartmentID = Faculty.DepartmentID;
Copy code

Output

StudentID Name FacultyName
1001 Ajay Piyush
1002 Babloo Namita
1003 Chhavi Ashneer
1004 Dheeraj Ghazal
1005 Evina Piyush
1006 Krishna Anupam

In the table we get all the record of student table (StudentID, and Name) and the corresponding common entries from the faculty table (Faculty Name).

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

SQL LEFT JOIN with WHERE and ORDER BY clause

Example 2: Join the student and faculty table and extract the record of students name, student ID, and faculty name where faculty names are Piyush and Ashneer.

Query

 
SELECT Student.StudentID, Student.Name, Faculty.FacultyName
FROM Student
LEFT JOIN Faculty
ON Student.DepartmentID = Faculty.DepartmentID
WHERE Faculty.FacultyName = ‘Piyush’, ‘Ashneer’
ORDER BY Student.StudentID;
Copy code

Output

StudentID Name FacultyName
1001 Ajay Piyush
1003 Chhavi Ashneer
1005 Evina Piyush

The above example is similar to example 1 but here we have use filter to extract the record where the faculties are Piyush and Ashneer using WHERE clause and after that we arrange them in ascending order of StudentID.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Join multiple tables using LEFT JOIN

Example 3: Extract the record of all the student: StudentID, Name, Email, DepartmentName and FacultyName. 

Query

 
SELECT
s.StudentID, s.Name, s.Email,
d.DepartmentName,
f.FacultyName
FROM Student s
LEFT JOIN Department d
ON s.DepartmentID = d.DepartmentID
LEFT JOIN Faculty f
ON s.DepartmentID = f.DepartmentID;
Copy code

Output

StudentID Name Email DepartmentName FacultyName
1001 Ajay ajay@xyz.com Mathematics Piyush
1002 Babloo babloo@xyz.com Physics Namita
1003 Chhavi chhavi@xyz.com English Ashneer
1004 Dheeraj dheeraj@xyz.com NULL Ghazal
1005 Evina evina@xyz.com Mathematics Piyush
1006 Krishna krishna@xyz.com NULL Anupam

In the above example when we used the LEFT JOIN on student, department and faculty table we get two NULL entries in Department Name as we don’t have the details of DepartmentID = 4 and 5.

Must Read: SQL WHERE

Must Read: Introduction to Normalization

Conclusion

In this article, we have briefly discussed how to use LEFT JOIN in SQL using example.

Hope this article will help you in your Data Science/Data Analysis journey.

Keep Learning!!

Keep Sharing!!

Must Read: SQL Comparison and Arithmetic Operator

Must Read: SQL Logical Operator

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments