SQL左连接查询排除空行问题及替代方案求助

Hey, let's break down what's going wrong with your query and fix it step by step:

First, your modified query is dragging on for 35+ minutes (and not returning results) for two main reasons:

• Using a LEFT OUTER JOIN with b.classification IS NOT NULL in the ON clause effectively mimics an inner join but without the efficiency of proper inner join logic. Plus, without indexes, joining two 400k+ row tables forces the database to do full table scans and massive row comparisons—this is a huge performance hit.
• The DISTINCT clause adds extra overhead, as the database has to sort and deduplicate results after joining, which is costly on large datasets.

Here are optimized alternatives, starting with the most straightforward:

1. Use INNER JOIN with Explicit Filters (Fastest with Indexes) #

Since you don't want rows with null values, an INNER JOIN is perfect here—it only keeps rows where matches exist in both tables. We'll move the non-null and mismatch filters to the WHERE clause for clarity and efficiency.

First, add these critical indexes (run these once—they'll drastically speed up future queries):

CREATE INDEX idx_tablea_number_class ON TableA(Number, classification);
CREATE INDEX idx_tableb_number_class ON TableB(Number, classification);

Then run this query:

SELECT DISTINCT a.Number, a.classification, b.classification
FROM TableA a
INNER JOIN TableB b 
    ON a.Number = b.Number
WHERE 
    a.classification IS NOT NULL 
    AND b.classification IS NOT NULL
    AND a.classification != b.classification;

2. Use EXISTS Subquery (Great for Sparse Matches) #

If most Number values don't have mismatched classifications, a subquery with EXISTS can be more efficient—it stops searching once it finds a single match for each row in TableA:

SELECT DISTINCT a.Number, a.classification, b.classification
FROM TableA a
JOIN TableB b ON a.Number = b.Number
WHERE 
    a.classification IS NOT NULL
    AND b.classification IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM TableB b_match
        WHERE b_match.Number = a.Number
        AND b_match.classification != a.classification
        AND b_match.classification IS NOT NULL
    );

Quick Notes: #

• Indexes are non-negotiable here. Without them, even these optimized queries will struggle with 400k+ rows. The indexes we suggested let the database quickly find matching Number values and filter on classification without scanning the entire table.
• If Number is a unique key in both tables, you can remove the DISTINCT clause entirely—this will save even more time. Only keep DISTINCT if there are duplicate Number values in either table.

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