SQL Delete Join

Introduction to SQL Delete Join

DELETE JOIN is an advanced structured query language(SQL) statement that is used to perform delete operations in multiple tables while using SQL JOIN such that all rows are deleted from the first table and the matching rows in another table or based on the kind of join operation used in the query. It is basically a combination of DELETE and JOIN statements. In this article, we will be learning about four types of DELETE operations, namely, DELETE while using INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.

Syntax and Parameters of SQL Delete Join

Following are the basic syntax of Delete Join with its parameters.

Syntax 1:

The basic syntax for Delete Join in SQL Server is as follows:

DELETE t1
FROM table_name1 AS t1 JOIN {INNER, RIGHT,LEFT,FULL} table_name1 AS t2
ON t1.column_name = t2.column_name
WHERE condition;

Syntax 2:

The basic syntax for Delete Join in MySQL is as follows:

DELETE t1.*
FROM table_name1 AS t1 JOIN {INNER, RIGHT,LEFT, FULL} table_name1  AS t2
ON t1.column_name = t2.column_name
WHERE condition;

Parameters of SQL Delete Join

The different parameters used in the syntax are:

• DELETE t1: It is used to delete the required table from the database. Here, you may choose from the first table’s instance t1 and the second table’s instance t2.
• FROM table_name1 as t1 JOIN table_name2 as t2: It is used to specify the source from which data has to be fetched and deleted. Here, table_name1 is the name of the left table and table_name2 is the name of the right table. To join, you may choose from INNER, LEFT, FULL and RIGHT joins.
• ON t1.column_name = t2.column_name: It is used to specify the common conditions on which the two tables will be joined. It can be a pair of primary and foreign keys.
• WHERE condition: It is used to specify the conditions to filter records.

Of the above-mentioned parameters, all the parameters except the WHERE clause are mandatory. Go ahead, we will be understanding delete joins in detail one by one.

Examples of SQL Delete Join

Following are the different examples of SQL Delete Join.

DELETE with INNER JOIN

Delete with inner join is used to delete all the records from the first table and all the matching records from the second table. In order to understand the concept better, we will take the help of two tables, Employees (this contains personal details of all the employees) and departments (it contains details like department id, name, and its hod).

The data in the department’s table look something like this:

departmentid	departmentname	head
4001	Sales & Marketing	Lina Krishna
4002	Products	David Jackson
4003	Human Resources	David Mayers

The data in the employee’s table is as follows:

employeeid	lastname	firstname	departmentid	address	city
10028	Becker	Todd	4001	27 street	Oslo
10029	Rebecca	Ginny	4001	27 street	Manhattan
10027	Tobby	Riya	4002	31 street	Manhattan
10026	Sharma	Deepak	4002	10th street	New Delhi
10024	Krishna	Lina	4001	27 street	Oslo
10023	Jackson	David	4002	27 street	Manhattan
10022	Mayers	David	4003	27 street	Manhattan

Example #1 – SQL Query to Illustrate DELETE INNER JOIN

Suppose in this example, a company wants to shut down the “sales & marketing” department. It would like to remove all the employees from this department from the company’s database. How will you perform this operation? It can be done by writing multiple sub-queries but we will do this simply by writing a delete join statement.

Code:

DELETE t1
FROM employees AS t1 INNER JOIN department AS t2
ON t1.departmentid = t2.departmentid
WHERE t2.departmentname = 'Sales & Marketing';

Output:

We can check using the SELECT statement that all the information for sales and marketing employees has been successfully deleted.

Code:

SELECT TOP 1000 [employeeid],
[lastname],
[firstname],
[departmentid],
[address],
[city] FROM [practice_db].[dbo].[employees]

Output:

DELETE with LEFT JOIN

A SQL DELETE LEFT join should be used in cases when we want to delete all data from one table(left) and only matching data from the other table.

Example #2 – SQL Query to Illustrate DELETE LEFT JOIN

Since in the previous steps, we have already shut down the sales and marketing department. Then we might further want to remove data pertaining to it from other database tables like tasks.

The tasks table looks something like this:

taskid	departmentid	employeeid
hr12	4003	10022
p123	4002	10026
p23	4002	10026
p231	4002	10023
sm12	4001	10028
sm45	4001	10029

The following SQL code will delete all the tasks for the sales and marketing department. We want to delete everything from the first table “tasks” but not from “departments”. So, we will be using a LEFT JOIN.

Code:

DELETE t1
FROM tasks AS t1 LEFT JOIN department AS t2
ON t1.departmentid = t2.departmentid
WHERE t2.departmentname = 'Sales & Marketing';

Output:

Let’s check if all the tasks pertaining to the sales and marketing department have been removed from the table.

Code:

SELECT TOP 1000 [taskid],
[departmentid],
[employeeid],
FROM [practice_db].[dbo].[tasks]

Output:

As you can see it has been successfully deleted.

DELETE with RIGHT JOIN

A SQL DELETE RIGHT join should be used in cases when we want to delete all data from one table (right) and only matching data from the other table.

Example #3 – SQL Query to Illustrate DELETE RIGHT JOIN

Since we have already deleted everything pertaining to the sales and marketing department in the tasks and employee table. Let’s now delete the department from the department table using a DELETE with RIGHT JOIN.

Code:

DELETE t2
FROM employees AS t1 RIGHT JOIN department AS t2
ON t1.departmentid = t2.departmentid
WHERE t2.departmentid NOT IN (SELECT t1.departmentid FROM employees as t1);

Output:

Let’s check if everything has been successfully deleted or not from the department’s table.

Code:

SELECT TOP 1000 [departmentid],
[departmentname],
[head] FROM [practice_db].[dbo].[department]

Output:

As you can see we have successfully achieved it.

DELETE with FULL JOIN

DELETE with FULL JOIN is similar to an INNER join. It is helpful in deleting all the data as FULL join returns all records from both the tables and will substitute the non-matching columns with NULL values.

Example #4 – SQL Query to Illustrate DELETE FULL JOIN

Finally, let’s try performing a delete operation with full join on employees and task tables. Assume that there is an additional column in the tasks table which shows the status of a particular task. Our aim is to delete all the employees(assuming they were freelancers) whose task has been completed.

Code:

DELETE t1
FROM employees AS t1 FULL JOIN tasks AS t2
ON t1.employeeid = t2.employeeid
WHERE t2.status = 'completed';

Output:

Let’s check if everything has been successfully deleted or not from the employee’s table.

Code:

SELECT TOP 1000 [employeeid],
[lastname],
[firstname],
[departmentid],
[address],
[city] FROM [practice_db].[dbo].[employees]

Output:

As you can see we have successfully achieved it.

Conclusion

DELETE JOINS is a combination of delete and joins in SQL. They help us in performing complex delete queries very efficiently. If we did not have delete joins, then we might have to write complex SQL subqueries within the delete statements.

Recommended Articles

This is a guide to SQL Delete Join. Here we discuss the basic concept and parameters of SQL Delete Join along with different examples and code implementation. You may also have a look at the following articles to learn more –

1. SQL Cross Join – Introduction
2. LEFT OUTER JOIN in SQL – Examples
3. Left Outer Join in MySQL – Key Differences
4. SQL Set Operators – Syntax and Parameters