How to Delete using INNER JOIN with SQL Server?

In SQL Server, we can use INNER JOIN within a DELETE statement to remove data from one table based on matching records in another table. This method is useful when we need to delete records from a target table that have corresponding rows in another table.

Understanding DELETE with INNER JOIN

Understanding DELETE with INNER JOIN in SQL Server enables users to remove records from one table based on matching conditions in another table. This method ensures that only relevant rows are deleted and maintains data integrity.

By identifying the target table in the `DELETE` statement and using `INNER JOIN`, you can effectively manage relationships between tables and ensure that deletions are accurate.

Syntax for DELETE using INNER JOIN in SQL Server

DELETE target_table
FROM target_table
INNER JOIN joined_table ON target_table.column = joined_table.column
WHERE <condition>;

Setting up Environment

To understand DELETE using INNER JOIN in SQL Server, we will create 2 tables, insert data, and then write queries to delete rows based on matching conditions from another table.

-- Creating the 'orders' table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Creating the 'customers' table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Inserting data into the 'orders' table
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1, 1, '2023-09-01'),
       (2, 2, '2023-09-02'),
       (3, 3, '2023-09-03');

-- Inserting data into the 'customers' table
INSERT INTO customers (customer_id, customer_name)
VALUES (1, 'John Doe'),
       (2, 'Alice Johnson'),
       (3, 'Michael Brown');

Example of How to Use DELETE with INNER JOIN in SQL Server

Example 1: Deleting Orders for Specific Customers Using INNER JOIN

Let's create a table called orders and customer and on these table, we will delete records from the orders table where there is a matching customer in the customers table

-- Step 1: Creating the 'orders' table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Step 2: Creating the 'customers' table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Step 3: Inserting data into the 'orders' table
INSERT INTO orders (order_id, customer_id, order_date)
VALUES 
    (1, 1, '2023-09-01'),
    (2, 2, '2023-09-02'),
    (3, 3, '2023-09-03');

-- Step 4: Inserting data into the 'customers' table
INSERT INTO customers (customer_id, customer_name)
VALUES 
    (1, 'John Doe'),
    (2, 'Alice Johnson'),
    (3, 'Michael Brown');

-- Step 5: Deleting orders for 'John Doe'
DELETE orders
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.customer_name = 'John Doe';

-- Step 6: Check remaining data in 'orders' table after delete
SELECT * FROM orders;

Output:

Explanation: This query deletes the record from the orders table where the customer name is 'John Doe' in the customers table. After running the query, only the orders for 'Alice Johnson' and 'Michael Brown' remain.

Example 2: Deleting All Orders for Customers Who No Longer Exist

In this example, we will delete all orders where there is no matching customer record in the customers table

-- Step 1: Creating the 'orders' table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Step 2: Creating the 'customers' table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Step 3: Inserting data into the 'orders' table
INSERT INTO orders (order_id, customer_id, order_date)
VALUES 
    (1, 1, '2023-09-01'),
    (2, 2, '2023-09-02'),
    (3, 3, '2023-09-03');

-- Step 4: Inserting data into the 'customers' table
INSERT INTO customers (customer_id, customer_name)
VALUES 
    (1, 'John Doe'),
    (2, 'Alice Johnson'),
    (3, 'Michael Brown');

-- Step 5: Deleting orders where there is no matching customer (This won't delete any records in our current setup)
DELETE orders
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.customer_id IS NULL;

-- Step 6: Check remaining data in 'orders' table (No change expected)
SELECT * FROM orders;

Output:

Explanation: In this example, we attempt to delete orders where there are no matching customer records in the customers table. Since all customers exist in the customers table, no orders are deleted and the table remains unchanged.

Conclusion

The use of DELETE with INNER JOIN in SQL Server offers a powerful method for managing related data across multiple tables. By using this technique, users can efficiently remove records that meet specific criteria, such as deleting orders associated with a customer. By understanding the above examples both the deletion of orders for specific customers and the attempt to remove orders for non-existent customers, highlighting the importance of maintaining accurate records in a relational database.