SQL LEFT JOIN
Posted on
< Previous Next >
A LEFT JOIN is used to combine data from two or more tables based on a related column between them. It returns all the rows from the left table (also known as the "first" or "left" table) and matching rows from the right table (also known as the "second" or "right" table). If there are no matching rows in the right table, NULL values are returned.
LEFT JOIN Syntax
SELECT column1, column2, …
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
For Example,
Suppose we want to see a list of all the students and the subjects they have taken, including those who have not taken any subjects yet.
We can achieve this using a left join:
SELECT student.name, subject.name
FROM student
LEFT JOIN exam ON student.id = exam.student_id
LEFT JOIN subject ON exam.subject_id = subject.id
ORDER BY student.name;
OUTPUT
In this above example,
We first select the student name and subject name columns from the student and subject tables, respectively. We then use a left join to combine the student and exam tables based on the student ID column. This will return all rows from the student table, even if there are no matching rows in the exam table. We then use another left join to combine the exam and subject tables based on the subject ID column. This will return all matching rows from the exam and subject tables.
For students who have not taken any exams yet, the exam columns in the result set will contain NULL values, but their names will still be included in the result set. This is the key difference between an inner join and a left join: with an inner join, only matching rows from both tables are returned, whereas with a left join, all rows from the left table are returned, even if there are no matching rows in the right table.
So, a left join is used to combine data from two or more tables based on a related column between them, returning all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, NULL values are returned. In our student, subject, and exam example, a left join can be used to see a list of all the students and the subjects they have taken, including those who have not taken any subjects yet.
< Previous Next >