Delete Using INNER JOIN in SQL

1. Overview

We use an INNER JOIN to run specific operations between two or more tables on a SQL database. One of these operations combines it with the DELETE statement to erase matching records from a database. Essentially, with an INNER JOIN, we can specify criteria from tables that reference each other to determine which records to delete.

In this article, we’ll venture into how to use an INNER JOIN to delete data in SQL effectively.

2. Understanding an INNER JOIN in SQL

The INNER JOIN is one of the four types of joins in SQL. Generally, we use it to join two tables that contain matching values in common fields. This means that the resulting table will have only values that meet the matching conditions.

However, in some scenarios, we can also use the INNER JOIN to delete data from rows across tables that have relative values in their columns.

3. Syntax for Deleting Using an INNER JOIN

The basic syntax and structure to perform a deleting operation with an INNER JOIN in SQL are simple:

DELETE Example_Table
FROM Example_Table 
INNER JOIN Example2_Table 
ON Example_Table.attributeName = Example2_Table.attributeName
WHERE condition;

• DELETE Example_Table specifies that we want to delete data from the Example_Table
• FROM Example_Table indicates the primary table in the INNER JOIN delete operation
• INNER JOIN Example2_Table indicates that Example2_Table joins with the primary table
• ON Example_Table.attributeName = Example2_Table.attributeName describes where both tables rows join based on their matching column values
• WHERE condition specifies the conditions that should be met before completing the deleting operation

Simply put, every part of the DELETE statement with INNER JOIN is important for a successful, accurate, and effective deletion operation across tables on a SQL database.

4. Deleting Using an INNER JOIN Example

To begin, we need to identify our target tables before completing the deletion operations through INNER JOIN. In this case study, we’re using two tables called Student and Department.

Let’s see what the data in the Student table looks like before the operation:

SELECT* FROM Student;
+----+-----------------+-------------+------------+-----------------+-----------------+------+---------------+
| id | name            | national_id | birth_date | enrollment_date | graduation_date | gpa  | department_id |
+----+-----------------+-------------+------------+-----------------+-----------------+------+---------------+
|  1 | Segun Durojaiye |   173486039 | 2000-01-15 | 2019-09-01      | 2023-06-30      |  2.5 |             2 |
|  2 | Dayo Adeyemi    |   947756891 | 1999-05-20 | 2018-08-25      | 2022-05-31      |  3.8 |             2 |
|  3 | Amara Nduka     |   353801590 | 2001-03-10 | 2020-02-01      | NULL            |  4.5 |             1 |
+----+-----------------+-------------+------------+-----------------+-----------------+------+---------------+
3 rows in set (0.001 sec)

Moreover, this is what the data in the Department table looked like before the operation:

SELECT * FROM Department;
+----+------------------+------+
| id | name             | code |
+----+------------------+------+
|  1 | Geology          | GEO  |
|  2 | Computer Science | CS   |
|  3 | Social Work      | SWK  |
+----+------------------+------+
...

Now, let’s use INNER JOIN to delete the students from the Computer Science department:

DELETE Student
FROM Student
INNER JOIN Department ON Student.department_id = Department.id
WHERE Department.name = 'Computer Science';
Query OK, 2 rows affected (0.020 sec)

Next, let’s verify that students from Computer Science department have been deleted:

 SELECT* FROM Student;
+----+-------------+-------------+------------+-----------------+-----------------+------+---------------+
| id | name        | national_id | birth_date | enrollment_date | graduation_date | gpa  | department_id |
+----+-------------+-------------+------------+-----------------+-----------------+------+---------------+
|  3 | Amara Nduka |   353801590 | 2001-03-10 | 2020-02-01      | NULL            |  4.5 |             1 |
+----+-------------+-------------+------------+-----------------+-----------------+------+---------------+
1 row in set (0.001 sec)

Evidently, we can see that the columns and rows associated with the department ID for Computer Science have been deleted after using the DELETE statement in combination with INNER JOIN.

5. Using Alias

Additionally, we can use aliases when performing INNER JOIN operations. Using this is particularly useful when we want our statement to be simple and clear. Moreover, in cases where we’re referencing columns from different tables, aliases can come in handy.

For example, let’s show how to use aliases while making an INNER JOIN delete statement:

DELETE s
FROM Student AS s
INNER JOIN Department AS d ON s.department_id = d.id
WHERE d.name = 'Computer Science';
Query OK, 2 rows affected (0.005 sec)

Simply put, s is an alias for Student, while d is for Department.

6. Conclusion

In this article, we’ve explored methods for deleting data using INNER JOIN in a SQL database. We’ve also learned that specific matching conditions must be met for the deletion operation to succeed. This approach ensures effective, accurate, and easy data management.

However, it’s important to understand a crucial point. While this method allows us to specify conditions across multiple tables, the deletion operation affects only the table explicitly targeted in the DELETE statement.