SQL - Delete Join

SQL - Deleting Data with Joins

In a relational database, data is often spread across multiple tables. A common task is to delete records from one table based on conditions in another. This tutorial covers modern and effective techniques for performing such deletions.

Imagine an e-commerce database with a customers table and an orders table. How would you delete all orders placed by customers from a specific region? You can’t just run DELETE FROM orders because the region information is in the customers table. This is where deleting with joins or subqueries becomes essential.

Let’s set up our example tables:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO customers VALUES
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'London'),
(3, 'Peter Jones', 'New York');

INSERT INTO orders VALUES
(101, 1, '2023-10-01', 150.00),
(102, 2, '2023-10-03', 200.50),
(103, 1, '2023-10-05', 75.25),
(104, 3, '2023-10-06', 300.00);

Method 1: DELETE with a Subquery (Standard SQL)

The most portable and standard way to achieve this is by using a subquery with the IN operator in the WHERE clause. This approach works across almost all SQL databases.

Syntax

DELETE FROM table_to_delete
WHERE column_name IN (SELECT column_to_match FROM other_table WHERE condition);

Example: Delete Orders from ‘New York’ Customers

We want to delete all records from the orders table that belong to customers living in ‘New York’.

DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');

This query first finds all ids of customers in ‘New York’ (which are 1 and 3) and then deletes any orders with a matching customer_id (orders 101, 103, and 104).

Method 2: DELETE with JOIN Syntax (Vendor-Specific)

Some database systems offer a more direct JOIN syntax within the DELETE statement. This can sometimes be more readable or performant, but it’s not standard SQL and the syntax varies.

PostgreSQL / SQL Server Syntax

These systems use a USING or FROM clause to introduce the joined table.

-- PostgreSQL Syntax
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.city = 'New York';

MySQL Syntax

MySQL’s syntax is slightly different, specifying which table to delete from before the FROM clause.

-- MySQL Syntax
DELETE o
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE c.city = 'New York';

Important Note: The original tutorial’s claim that DELETE...JOIN deletes from all tables simultaneously is generally incorrect and dangerous. Most syntaxes only allow deleting from one table listed in the DELETE clause. While some dialects (like MySQL) have syntax to delete from multiple tables (DELETE c, o FROM ...), it’s rarely used and can be confusing. The primary use case is deleting from one table based on a join.

Method 3: Cascading Deletes (Declarative Approach)

A powerful, database-enforced method for maintaining referential integrity is the ON DELETE CASCADE constraint. When you define a foreign key, you can instruct the database to automatically delete any child records when the parent record is deleted.

Example Setup

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    -- This foreign key will cascade deletes
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

With this structure, if you delete a customer, all of their orders are automatically deleted by the database.

-- This single command will delete customer 1 AND all their associated orders (101 and 103).
DELETE FROM customers WHERE id = 1;

**Pros:** Enforces data integrity at the database level. Simplifies application logic.
**Cons:** Can be dangerous if the cascading effect is not fully understood. It can trigger a chain reaction of deletes across many tables.

Common Mistakes and Debugging

**Pro-Tip: Select Before You Delete!**

Before running a destructive `DELETE` command, always write a `SELECT` statement with the exact same joins and `WHERE` clause to verify which rows will be affected. This is the single most important safety check.

`-- For our example, run this first:
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');`

This will show you exactly what is about to be deleted, preventing catastrophic mistakes.