如何用JOIN条件重写NOT IN子查询？优化高成本SQL语句

Let's break down how to rewrite those costly NOT IN subqueries using LEFT JOIN logic—this is a common fix to avoid the performance hit of repeated subquery executions, and it also avoids a silent trap that NOT IN can create with NULL values.

First, the Problem with Your Current NOT IN Checks #

Your query has two identical NOT IN (SELECT column1 FROM prod) checks (one in the join conditions, one in the WHERE clause). Not only is this redundant work, but NOT IN often forces the database to run the subquery once per row in table1, which gets slow as your dataset grows. Worse, if prod.column1 ever contains a NULL value, NOT IN will return no results at all (since NULL comparisons never evaluate to true)—a tricky bug to track down.

The Solution: Replace NOT IN with LEFT JOIN + IS NULL #

We can replace both NOT IN checks with a single LEFT JOIN to the prod table, then filter for rows where there's no matching entry in prod. This lets the query optimizer use efficient join algorithms (like hash joins) instead of repeated subquery runs, and it's immune to NULL-related issues.

Here's the rewritten query with explanations:

SELECT 
    clm.column1, 
    clm.column2, 
    ins.column3, 
    dia.column4, 
    clm.column5 
FROM table1 clm 
-- Keep your existing inner join to table2
INNER JOIN table2 ins ON clm.key = ins.key 
-- Add a left join to prod to find non-matching rows
LEFT JOIN prod p ON clm.column1 = p.column1 
-- Keep your left join to table3, with a small date comparison fix
LEFT OUTER JOIN table3 SFX 
    ON clm.number = SFX.number 
    AND SFX.id IN (SELECT MAX(id) FROM table3 GROUP BY number) 
    AND SFX.app_dt = TO_DATE('21-06-2020', 'DD-MM-YYYY') -- Changed to direct date comparison for efficiency
-- Keep your aggregated left join to the dia subquery
LEFT OUTER JOIN ( 
    SELECT 
        column1, 
        RTRIM(XMLAGG(XMLELEMENT(E, MD.process, ',').EXTRACT('//text()') ORDER BY column1).GetClobVal(), ',') AS column4 
    FROM table4 D 
    INNER JOIN table5 MD ON MD.key = D.id 
    GROUP BY column1 
) dia ON clm.column1 = dia.column1 
-- This replaces both NOT IN checks: only keep rows with no match in prod
WHERE p.column1 IS NULL;

Key Changes Explained: #

1. Single LEFT JOIN to prod: Instead of checking NOT IN twice, we link table1 to prod on column1. Rows in table1 that have no matching entry in prod will have NULL values for all prod columns.
2. WHERE p.column1 IS NULL: This filters exactly those rows with no match in prod—functionally equivalent to NOT IN, but faster and safer (no NULL traps).
3. Date Comparison Fix: I swapped TO_CHAR(SFX.app_dt, 'YYYYMMDD') = to_char('21-06-2020', 'YYYYMMDD') for a direct date comparison. Converting dates to strings for comparison is inefficient and error-prone; comparing dates directly is better for performance and correctness.

Optional Extra Optimization for the table3 Join #

Your current LEFT JOIN to table3 uses a subquery to get the max id per number. You can rewrite this with a window function to make it even more efficient:

LEFT OUTER JOIN (
    SELECT number, app_dt, id,
           ROW_NUMBER() OVER (PARTITION BY number ORDER BY id DESC) AS rn
    FROM table3
) SFX ON clm.number = SFX.number 
    AND SFX.rn = 1
    AND SFX.app_dt = TO_DATE('21-06-2020', 'DD-MM-YYYY')

This avoids the separate grouping subquery and lets the optimizer handle row ranking more smoothly.

Final Tips #

• Add an index on prod.column1—this will make the LEFT JOIN run much faster, especially with large datasets.
• If you're using Oracle (judging by the XMLAGG function), this rewrite will play nicely with the optimizer's ability to choose efficient execution plans.

内容的提问来源于stack exchange，提问作者Nvr