LEFT JOIN
Sql tutorial · PySpark.in
7.3 Left Join
A LEFT JOIN returns all rows from the left table and the matching rows from the right table.
If a row in the left table has no matching row in the right table, SQL still includes that row in the result, and the columns from the right table are filled with NULL.
In simple words:
LEFT JOIN keeps every row from the left table, whether a match exists or not.
Syntax:
```
hide
sql
SELECT *
FROM table_1
LEFT JOIN table_2
ON table1.c1 = table2.c2;
```
Example:
Fetch the names of students who have not enrolled in any course.
Here:
- The student table contains all students.
- The student_course table contains enrollment records.
Since we want all students, even those without enrollments, we use LEFT JOIN.
```
sql
-- Reset tables
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS student_course;
-- Create tables
CREATE TABLE student (
student_id INTEGER PRIMARY KEY,
full_name TEXT
);
CREATE TABLE student_course (
enrollment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER
);
-- Insert data
INSERT INTO student VALUES
(1, 'Amit Sharma'),
(2, 'Neha Verma'),
(3, 'Rohit Kumar'),
(4, 'Priya Singh'),
(5, 'Karan Mehta');
INSERT INTO student_course VALUES
(101, 1, 15),
(102, 2, 12),
(103, 3, 15);
-- Query
SELECT student.full_name
FROM student
LEFT JOIN student_course
ON student.student_id = student_course.student_id
WHERE student_course.enrollment_id IS NULL;
```
How It Works
Step 1: LEFT JOIN matches rows
```
hide
ON student.student_id = student_course.student_id
```
SQL matches rows where the student_id exists in both tables.
Matching rows:
- Amit Sharma → enrolled
- Neha Verma → enrolled
- Rohit Kumar → enrolled
No matching rows:
- Priya Singh
- Karan Mehta
For these unmatched students, columns from student_course become NULL.
Step 2: Filter unmatched rows
```
hide
WHERE student_course.enrollment_id IS NULL
```
This keeps only those students whose enrollment record does not exist.
Why IS NULL?
When there is no matching row in the right table, SQL places NULL in the right-table columns.
That is why we check:
```
hide
student_course.enrollment_id IS NULL
```
This is a common way to find missing related records.
Important Point
A LEFT JOIN always keeps all rows from the left table.
Even if there is no matching record in the right table, the left-table row still appears.
This makes LEFT JOIN very useful when you want to find:
- students with no enrollments
- customers with no orders
- employees with no assigned projects
More Sql tutorials
All tutorials · Try the free PySpark compiler · Practice challenges