Deleting Rows Using LEFT JOIN in SQL

1. Introduction

It’s a common and regular task to delete data from SQL to maintain data integrity or remove obsolete records. However, the DELETE command alone isn’t always the best option, particularly when dealing with relationships between tables.

Conversely, LEFT JOIN combined with DELETE is most suited for removing rows of data in one table based on the absence of matching rows in another related table.

In this tutorial, we’ll explore how to delete rows using LEFT JOIN in SQL, focusing on three popular database systems: PostgreSQL, MySQL, and SQL Server. We’ll use the Baeldung University database schema for examples and ensure clarity through step-by-step explanations.

2. Understanding LEFT JOIN

The basic function of LEFT JOIN is to retrieve all rows from the left (first) table and the matching rows from the right (second) table. However, if there’s no match, the result would include NULL or empty values for the right table’s columns.

Now, let’s quickly demonstrate how to use the LEFT JOIN command on related tables in the Baeldung University database schema. In addition, we’ll use the PostgreSQL database in this illustration.

Let’s apply the LEFT JOIN operation on the Student and Registration tables from the University schema to identify students with registered courses:

SELECT 
    s.id AS student_id, 
    s.name AS student_name, 
    r.course_id
FROM student s
LEFT JOIN registration r ON s.id = r.student_id;

student_id  | student_name    | course_id 
------------+-----------------+-----------
1001        | John Liu        | CS111
1001        | John Liu        | CS121
1001        | John Liu        | CS122
...
1107        | Phellum Luis    | MA111
1107        | Phellum Luis    | MA142
...
1619        | Sam Roberts     |
1617        | Philip Mohan    |
1719        | Siren Lobo      |
2001        | Reena Roy       |
...

As shown above, we selected two columns from the Student table, which are s.id and s.name. Also, we selected r.course_id from the Registration table. The LEFT JOIN operation joined the left and right, i.e., Student and Registration tables, respectively on the id and student_id columns.

As a result, the table shows the list of students and their registered courses. Thus, the rows with empty course_id columns indicate students who didn’t register for any courses.

3. Deleting Rows Using LEFT JOIN

We’ll need to modify the SQL query in the previous section by adding a DELETE and WHERE command to delete rows based on conditions.

Compared to MySQL and SQL Server, PostgreSQL has a somewhat different approach to using LEFT JOIN and DELETE within an SQL query.

3.1. PostgreSQL

Let’s explore how to delete rows in PostgreSQL:

DELETE FROM student
WHERE id IN (
  SELECT s.id
  FROM student s
  LEFT JOIN registration r ON s.id = r.student_id
  WHERE r.student_id IS NULL
);
DELETE 13

The result shows that 13 rows from the Student table were deleted based on the table from the subquery. For more context, the subquery used the LEFT JOIN command to retrieve the id of the students without course registration. Afterward, the DELETE and the WHERE clause used the subquery result to delete rows from the Student table.

To further verify this, we can next run the subquery as a standalone query to check if rows with NULL or empty columns appear in the output:

SELECT s.id
FROM student s
LEFT JOIN registration r ON s.id = r.student_id 
WHERE r.student_id IS NULL;

 id 
----
(0 rows)

The result shows that there are no empty rows anymore on the Student table. Another way to confirm this is to check if the names of the students we obtained from the previous section which have empty course_id columns are still present in the Student table.

Now, only students with registered courses remain in the Student table.

3.2. MySQL and SQL Server

For MySQL and SQL Server, the usage of the LEFT JOIN and DELETE operations is the same:

DELETE s 
FROM Student s 
LEFT JOIN Registration r ON s.id = r.student_id 
WHERE r.student_id IS NULL;

Query OK, 13 rows affected (0.02 sec)

Similarly, 13 rows representing students who didn’t register for courses were deleted from the Student table. The DELETE s and FROM Student s operations delete rows from the Student table.

However, the combination of the LEFT JOIN and WHERE clauses determines the rows deleted for students who haven’t registered for courses.

Let’s verify the removal of the rows of students with no registered courses:

SELECT * 
FROM Student s 
LEFT JOIN Registration r ON s.id = r.student_id 
WHERE r.student_id IS NULL;

Empty set (0.00 sec)

The result above shows that all the students with unregistered courses were deleted from the Student table. Notably, this method of using LEFT JOIN for deleting rows in SQL is applicable to SQL Server too.

4. Conclusion

In this article, we’ve explored using the LEFT JOIN operation to delete table rows in SQL. This method involves combining DELETE and LEFT JOIN operations to achieve the desired result based on specified conditions.

Moreover, we discussed the differences in using LEFT JOIN and DELETE for deleting rows in PostgreSQL, compared to MySQL and SQL Server.