EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL Delete Join

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL Delete Join

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

The following are the basic syntax of Delete Join with its parameters.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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';

Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (5,626 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

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

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • 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

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

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

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

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

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More