Fri Apr 17 09:39:42 EEST 2026: Article revised following comments received. I thank in advance for all comment providers!

Summary: In Oracle SQL, combining DELETE with a SELECT statement allows you to remove rows based on complex conditions derived from other tables or queries. This pattern is essential in real-world data management tasks such as: cleanup, synchronisation, and in enforcing business rules.

Overview

Oracle does not support DELETE … FROM … JOIN syntax like some other databases (e.g., SQL Server or MySQL).

Instead, it achieves the same functionality using:

• Subqueries (WHERE … IN, EXISTS)

• Correlated subqueries

• Inline views (advanced cases)

Basic Syntax

Using a Subquery (IN)

This case is quite straightforward, for example:

DELETE FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM another_table
    WHERE condition ...
);

Using EXISTS (Preferred in many cases)

This is also quite straightforward and is preferred by many developers. Example:

DELETE FROM table_name t
WHERE EXISTS (
    SELECT 1
    FROM another_table a
    WHERE a.column = t.column
    AND condition
);

Other Key Methods Explained

DELETE with IN Clause Example

Example Behavior:
• Deletes employees working in departments located in location 1700
• Subquery executes first, then DELETE applies

DELETE FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

Considerations:

May perform poorly with large datasets!

Can be affected by NULL values

Correlated Subquery: DELETE with EXISTS

Example:

DELETE FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

Why the Use of EXISTS operator is sometimes desirable?

Because depending on the database design and the dataset sometimes:

• OracleStops scanning once a match is found
• Often more efficient than IN
• Handles NULLs better

Correlated DELETE (Row-by-Row Evaluation)

Here is a typical example:

DELETE FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = o.customer_id
    AND c.status = 'INACTIVE'
);

Details and Explanation:

• For each row in orders, Oracle checks the subquery
• Deletes only matching rows

Advanced: DELETE Using Inline Views

Sometimes, Oracle allows deleting from a subquery (inline view) only under certain conditions.

Sample:

DELETE FROM (
    SELECT e.*
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.location_id = 1700
);

Important:

• The inline view must be key-preserved

• Otherwise, Oracle raises:

ORA-01752: cannot delete from view without exactly one key-preserved table

DELETE with JOIN Logic via Subquery

Since Oracle doesn’t support direct joins in DELETE, this would be a typical example:

This is the standard workaround for JOIN-based deletes:

DELETE FROM employees
WHERE department_id IN (
    SELECT d.department_id
    FROM departments d
    WHERE d.location_id = 1700
);

Performance Considerations

IN vs EXISTS

• I notice a reoccurring pattern of many Client performance related issues linked with the usage of those operators.
• What is important to know on the first hand is that modern optimizers like the Oracle’s CBO can and should often transform IN to EXISTS, making the performance difference context-dependent.
• The key difference often lies with NOT IN vs. NOT EXISTS and the handling of NULLs

Best Practice: Therefore, the advice is to investigate the underperforming code and possible correlation of the problematic test runs with NULL values. The first step is to investigate and validate the handling of NULLs!
IN case the NULL values are not properly handled by the code logic, the CBO might sometimes not do (justifiably) the operators transformation!
Therefore, it is worth reinvestigating the code logic and if found necessary, also check if is possible to refactor the code by revising the specific operator usage.
With other words, as application owners we should always ensure the custom code logic works as expected! Especially in tge case of the handling of NULL values!

This advice is again highly dependent of your database design and data set! As a first step, please also make sure to validate the handling of NULL values by the underperforming code!

Use Case: Handling Large Deletes

When possible, use ROWNUM for batching, e.g.:

DELETE FROM large_table
WHERE ROWNUM <= 10000;

Looping approach. Here is a simple example::

BEGIN
    LOOP
        DELETE FROM large_table
        WHERE condition
        AND ROWNUM <= 1000;
        EXIT WHEN SQL%ROWCOUNT = 0;
    END LOOP;
END;

Important Note: the first version of this article published on 15.04.2026 contained a serious error in the code sample above, which I have removed now, please see the note in the paragraph below for more details, thank you!

The first version of the PL/SQL example loop example included COMMIT inside the loop !! This is a major anti-pattern (COMMIT-in-loop) that undermines transactional integrity, causes poor performance, and risks ORA-01555: snapshot too old errors on large operations. Should be avoided.

I would like the remind that the code snippets are provided as-is for informative purposes and reusing it without proper prior testing is done at own risk. Also, I thank the readers in advance for all types of inputs including the critical, constructive ones!

DELETE with Subquery Returning Multiple Columns

As Oracle has supported this row-value constructor (tuple) syntax for many years, Oracle database does allows:

DELETE FROM table1
WHERE (col1, col2) IN (SELECT col1, col2 FROM table2);

But, sometimes and only for certain cases, this approach can be underperforming. If you need to look for alternatives, consider using instead:

DELETE FROM table1 t
WHERE EXISTS (
    SELECT 1
    FROM table2 s
    WHERE s.col1 = t.col1
    AND s.col2 = t.col2
);

Referential Integrity Considerations

Foreign key constraints may block deletes! to work around, use:

• ON DELETE CASCADE
• Or delete child rows first

Common Errors to be aware of

Here are a few common errors you should prepare for during design phase:

ORA-02292: Integrity constraint violated

• Child records exist

ORA-01752: Cannot delete from view

• Inline view not key-preserved

ORA-01427: Single-row subquery returns more than one row

• Incorrect subquery usage

I will update this list in future and possible derive separate articles for tricky cases.

Official Oracle Documentation References

Te best and the most relevant Oracle documentation sources:

⸻

1. Oracle Database SQL Language Reference which is the manual released by Oracle Corp. for each major release.