Alongside the UPDATE improvement covered last week, Oracle 23ai also brings direct JOIN support to DELETE statements. The same ergonomic benefit applies — no more correlated subqueries just to filter a delete based on another table.
The old way:
DELETE FROM order_items oi
WHERE oi.order_id IN (
SELECT o.order_id
FROM orders o
WHERE o.status = 'CANCELLED'
AND o.order_date < DATE '2024-01-01'
);The new way (Oracle 23ai):
DELETE FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'CANCELLED'
AND o.order_date < DATE '2024-01-01';Same result, but the relationship between the two tables is explicit and readable.
Cascade delete pattern:
-- Delete all line items for expired promotions
DELETE FROM promotion_items pi
JOIN promotions p ON p.promo_id = pi.promo_id
WHERE p.expiry_date < SYSDATE;An important caveat: The join conditions determine which rows are deleted from the target table. You cannot delete from the joined table — only from the primary table in the DELETE FROM clause. If you try to delete from both sides, Oracle will raise an error.
Why this matters
In data warehousing and ETL scenarios, conditional deletes based on staging table content are extremely common. Before 23ai, you had to choose between a readable EXISTS subquery or a sometimes-faster IN subquery, both of which hide the join relationship.
The direct JOIN syntax is not only cleaner to write — it’s also easier to review in code reviews and easier to optimize because the join relationship is explicit in the query structure.
