SQL LEFT JOIN – Examples and Syntax
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.
Table of Content
- What is SQL LEFT JOIN?
- LEFT JOIN with WHERE and ORDER BY Clause
- Join multiple tables using LEFT JOIN
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
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.
SELECT column_namesFROM table_1LEFT JOIN table_2 ON table_1.matching_column_name = table_2.matching_column_name;
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 | 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 |
| DepartmentID | DepartmentName |
| 1 | Mathematics |
| 2 | Physics |
| 3 | English |
| DepartmentID | FacultyName |
| 1 | Piyush |
| 2 | Namita |
| 3 | Ashneer |
| 4 | Ghazal |
| 5 | Anupam |
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.FacultyNameFROM StudentLEFT JOIN Faculty ON Student.DepartmentID = Faculty.DepartmentID;
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.FacultyNameFROM StudentLEFT JOIN Faculty ON Student.DepartmentID = Faculty.DepartmentIDWHERE Faculty.FacultyName = ‘Piyush’, ‘Ashneer’ORDER BY Student.StudentID;
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.FacultyNameFROM Student sLEFT JOIN Department d ON s.DepartmentID = d.DepartmentIDLEFT JOIN Faculty f ON s.DepartmentID = f.DepartmentID;
Output
| StudentID | Name | 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
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
Top Picks & New Arrivals
