EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL Delete Join

SQL Delete Join

Priya Pedamkar
Article byPriya Pedamkar

Updated March 27, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and Parameters of SQL Delete Join

The 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:

INNER JOIN

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:

employees

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:

SQL Delete Join 3

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:

SQL Delete Join 1-1

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:

SQL Delete Join 1-2

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:

SQL Delete Join 1-3

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:

SQL Delete Join 1-4

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:

Example 1-5

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

We hope that this EDUCBA information on “SQL Delete Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  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
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
 204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more