intro
Learn everything you need to know about using JOIN in a DELETE query to remove rows from a table based on data from another related table.
In a relational database, data is often spread across multiple related tables. A JOIN query is what you need to retrieve this data together. But what if you want to delete data in one table based on information from another? This is where you need to use a JOIN in a DELETE query!
In this guide, you will explore how to use DELETE JOIN queries, when they are useful, and what to do when databases do not support them.
Let's dive in!
Can DELETE and JOIN Be Used Together?
Yes, it is possible to write queries that involve both DELETE and JOIN operations.
The DELETE statement supports a JOIN clause, which opens the door to removing records from a table based on matching conditions in another table. That is particularly useful for deleting data across related tables.
You might write a JOIN in a DELETE query to remove data that meets specific criteria across multiple tables, such as removing orphaned records or outdated entries.
Why Use DELETE with JOIN in SQL?
Writing a DELETE with a JOIN in SQL is useful when you need to remove records from one table based on conditions related to one or more other tables. This approach is particularly helpful when maintaining data integrity or cleaning up the database.
For instance, consider a streaming platform with users and sessions tables. If you want to delete users who have only banned sessions, a JOIN ensures that only matching records are removed while keeping other users intact. Another common example is an inventory system where you may need to delete discontinued products from a products table if they no longer have active listings in the offerings table.
In both cases, using a JOIN in a DELETE helps you remove data from one table based on conditions found in another. Read our guide for more information on how JOIN operations work.
JOIN in DELETE Query: Syntax and Usage
In MySQL, SQL Server, and other databases, this is the syntax for using JOIN in a DELETE query:
1
DELETE T1
2
FROM table_1 T1
3
JOIN table_2 T2 ON T1.id = T2.foreign_id
4
[WHERE ...];
The above query removes records from table_1 (aliased as T1) based on matching conditions in table_2 (aliased as T2). The JOIN ensures that only rows in table_1 with a corresponding match in table_2 are considered for deletion. That happens because, by default, a JOIN in a DELETE statement is an INNER JOIN in SQL, meaning only matching rows are included.
Finally, the optional (but typically present) WHERE clause further refines which rows to delete based on specific conditions, which can involve columns from both table_1 and table_2.
Keep in mind that not all databases support the above SQL DELETE JOIN syntax. For example, PostgreSQL does not allow JOIN clauses in DELETE statements. Instead, you need to use USING as follows:
1
DELETE FROM table_1 T1
2
USING table_2 T2
3
WHERE T1.id = T2.foreign_id
4
[WHERE ...];
In Postgres, USING servers a similar purpose of JOIN in DELETE queries. The main difference is that it always behaves like an INNER JOIN, not supporting other scenarios like a LEFT JOIN.
For more guidance, always refer to the documentation of your specific DBMS of choice:
DELETE JOIN SQL Complete Example
Now that you understand how DELETE with JOIN works, you are ready to take a look at how it can be applied in real-world scenarios.
Note: In this section, we will use DbVisualizer, a feature-rich database client, to simplify query execution and data exploration. The sample queries below will be written in MySQL, but they can be easily adapted to SQL Server or other databases that support a similar DELETE JOIN syntax.
We will delete data from the products and categories sample tables. This is what the products table looks like:
And here is the categories table:
Note that category_id in the products table is a foreign key referencing the categories table, as you can see in the ERD-like references graph generated by DbVisualizer:
Now, let's join and delete some data!
DELETE INNER JOIN
A DELETE INNER JOIN will be useful if you want to delete all products that belong to a category that has been discontinued. You have a products table and a categories table. You need to join these two tables and remove the products that belong to a discontinued category.
First, retrieve all products that belong to the "Discontinued" category with the following INNER JOIN query:
1
SELECT P.*
2
FROM products P
3
INNER JOIN categories C ON P.category_id = C.id
4
WHERE C.name = "Discontinued";
As you can see, this query returns all products that belong to the "Discontinued" category.
To delete these products, use the following DELETE INNER JOIN query:
1
DELETE P
2
FROM products P
3
INNER JOIN categories C ON category_id = C.id
4
WHERE C.name = "Discontinued";
The query removed two products from the products table that belong to the "Discontinued" category.
DELETE LEFT JOIN
Consider that you want to delete all products that do not belong to any category. That means their category_id is NULL when left joined with the categories table.
Get all products without a category by using the following LEFT JOIN query — include a WHERE clause searching for a non-existing category ID:
1
SELECT P.*
2
FROM products P
3
LEFT JOIN categories C ON P.category_id = C.id
4
WHERE C.id IS NULL;
This query returns all products that do not have a matching category:
To delete these products, use the following MySQL DELETE JOIN query:
1
DELETE P
2
FROM products P
3
LEFT JOIN categories C ON P.category_id = C.id
4
WHERE C.id IS NULL;
Executing this query will remove the two products from the products table that do not belong to any category:
Et voilà! You are now a master of using a JOIN in a DELETE query.
Alternatives to the DELETE JOIN Queries
As mentioned earlier, not all databases support DELETE JOIN statements. Also, DELETE JOIN may be costly if your database has a lot of data to sift through. By default, JOINs are slow because your database has to obtain all of these separate bits of information and pull them together again. As such, you may need to consider alternative approaches.
For instance, suppose you want to delete users from a users table if they have been banned at least once in one of their sessions. You would end up writing this query:
1
DELETE U
2
FROM users U
3
JOIN sessions S ON U.id = S.user_id
4
WHERE S.status = "banned";
This query deletes records from the users table if the user has at least one session in the sessions table where the status is "banned".
Now, explore alternative approaches that achieve the same result without using JOIN.
IN Query
If your database does not support DELETE JOIN or if the operation is too costly to run, you can use the IN clause with an SQL subquery instead:
1
DELETE FROM users
2
WHERE id IN (SELECT user_id FROM sessions WHERE status = "banned");
This statement deletes users whose id matches any user_id from the sessions table where the status is "banned".
EXISTS Query
Another alternative for large datasets is using EXISTS:
1
DELETE FROM users U
2
WHERE EXISTS (SELECT 1 FROM sessions S WHERE U.id = S.user_id AND S.status = "banned");
The above query deletes users only if a matching session with status = "banned" exists in the sessions table.
Note that this alternative works specifically for the scenario presented here but may not be suitable for all use cases.
MERGE Query
For databases that support MERGE statements, you can use it to perform a conditional delete:
1
MERGE users U
2
USING (SELECT user_id FROM sessions WHERE status = "banned") S
3
ON U.id = S.user_id
4
WHEN MATCHED THEN DELETE;
The query merges users with a filtered selection from sessions, deleting users when a match is found. Keep in mind that MySQL does not directly support MERGE.
Conclusion
In this blog post, you learned how to use JOIN in a DELETE statement. As demonstrated in two examples, the process becomes much easier with a feature-rich visual database client like DbVisualizer.
DbVisualizer has many capabilities, including visual query execution, data exploration, and table discovery. Additionally, it equips you with advanced features like SQL formatting and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is it possible to use DELETE with JOIN in MySQL?
Yes, MySQL supports DELETE with JOIN to remove records from one table based on conditions in another. The syntax typically involves INNER JOIN or LEFT JOIN clauses.
What are the types of JOINs supported in a DELETE query?
In SQL, DELETE queries generally involve INNER JOIN and, in some cases, LEFT JOIN, depending on the database:
Can a DELETE query have multiple JOINs?
Yes, a DELETE query can have multiple JOIN clauses, as in the following example:
1
DELETE P
2
FROM products P
3
**JOIN** inventory I ON P.id = I.product_id
4
**LEFT JOIN** orders O ON P.id = O.product_id
5
WHERE P.status = "discontinued" AND O.id IS NULL;
This query deletes products from the products table that are marked as discontinued (P.status = 'discontinued') only if they have no active orders (O.id IS NULL).
Is RIGHT JOIN supported in DELETE queries?
No, RIGHT JOIN is not supported in DELETE queries in most SQL databases like MySQL and SQL Server. Since DELETE queries target the first table listed, using RIGHT JOIN (which prioritizes the second table) would not make sense.
Why use a database client to deal with SQL DELETE and JOIN queries?
DbVisualizer is a powerful, highly-rated multi-database client that enables you to manage multiple databases from a single platform. A key feature is its ability to visually present data from queries and highlight the relationships between tables in a database. This makes it easier to understand JOIN queries and improves the overall experience of working with relational data. Grab a 21-day DbVisualizer free trial!
