Month: February 2025

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.

Updating rows in one table based on data from another has always been possible in Oracle, but the syntax was never elegant. You had to use a correlated subquery or an inline view workaround. Oracle 23ai introduces direct JOIN syntax in UPDATE statements, making multi-table updates far more readable.

The old way — correlated subquery:

UPDATE employees e
SET    e.department_name = (
           SELECT d.department_name
           FROM   departments d
           WHERE  d.department_id = e.department_id
       )
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE  d.department_id = e.department_id
    AND    d.active = 1
);

The new way — direct JOIN (Oracle 23ai):

UPDATE employees e
JOIN   departments d ON d.department_id = e.department_id
SET    e.department_name = d.department_name
WHERE  d.active = 1;

Much cleaner. The intent is immediately clear — join employees to departments, update the column, filter by a condition on the joined table.

Supported join types:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

Multi-column update example:

UPDATE employees e
JOIN   salary_bands sb ON sb.grade = e.pay_grade
SET    e.min_salary = sb.min_salary,
       e.max_salary = sb.max_salary
WHERE  sb.effective_date = DATE '2025-01-01';

Performance note: The Oracle optimizer can leverage indexes and statistics on the joined table just as it does with regular SELECT joins. In many cases, this will be equivalent or better than the correlated subquery approach.

If you’ve ever looked at UPDATE statements and wished they read more like SELECT statements, Oracle 23ai has finally granted that wish. This is one of those features you didn’t know you were missing until you have it.

Oracle 23ai introduces table value constructors — a way to use a VALUES clause directly in a FROM clause, creating an inline result set without needing a real table, a UNION ALL chain, or a PL/SQL collection.

This is especially useful for testing queries, building lookup sets, or constructing small datasets on the fly.

Basic syntax:

SELECT *
FROM   (VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol'))
       AS t(id, name);

Practical use case — joining against a small inline dataset:

SELECT e.employee_name, r.region_label
FROM   employees e
JOIN   (VALUES ('EMEA', 'Europe, Middle East & Africa'),
               ('APAC', 'Asia Pacific'),
               ('AMER', 'Americas'))
       AS r(region_code, region_label)
ON     e.region = r.region_code;

Why not just use DUAL with UNION ALL?

Before 23ai, the standard Oracle workaround was:

SELECT 1 AS id, 'Alice' AS name FROM DUAL
UNION ALL
SELECT 2, 'Bob'   FROM DUAL
UNION ALL
SELECT 3, 'Carol' FROM DUAL;

It works, but it’s verbose and scales poorly. With FROM VALUES, the syntax is cleaner and performs better because Oracle can optimize it as a single operation.

Testing aggregate functions inline:

SELECT region, SUM(sales) AS total
FROM   (VALUES ('EMEA', 500), ('EMEA', 300), ('APAC', 800))
       AS t(region, sales)
GROUP BY region;

This pattern is fantastic for unit-testing SQL logic without needing real data in real tables. Write your query against a known dataset, validate it, then swap in the real table.

Table value constructors are a mature feature in ANSI SQL and in most other major databases. Their arrival in Oracle 23ai is welcome — and makes a lot of common SQL patterns significantly cleaner.