SQL LEFT OUTER JOIN后LOCATION计数结果不符合求和预期的问题求助

Hey there, let's break down why your count is returning 8 for MANILA instead of the expected 6, and how to fix it.

The Root Cause #

Your current query uses a LEFT OUTER JOIN between TABLE1 and TABLE2 on LOCATION. Here's what happens for MANILA:

• TABLE1 has 4 MANILA records, TABLE2 has 2 MANILA records
• The join pairs every MANILA record from TABLE1 with every MANILA record from TABLE2, creating 4×2=8 rows in the joined result set
• COUNT(b.LOCATION) then counts all those 8 non-null entries, hence the incorrect total of 8

SINGAPORE works correctly because there are no matching records in TABLE2, so the join just keeps the 6 rows from TABLE1 with nulls for TABLE2 columns, and COUNT(b.LOCATION) ignores the nulls (but since we're grouping by a.LOCATION, it counts the 6 rows from TABLE1 as expected).

Solution 1: Combine Aggregated Counts with UNION ALL #

The cleanest approach is to first count records per location in each table separately, then combine and sum those counts:

SELECT LOCATION, SUM(total_records) AS total_count
FROM (
    -- Get counts from TABLE1
    SELECT LOCATION, COUNT(*) AS total_records
    FROM TABLE1
    GROUP BY LOCATION
    
    UNION ALL
    
    -- Get counts from TABLE2
    SELECT LOCATION, COUNT(*) AS total_records
    FROM TABLE2
    GROUP BY LOCATION
) combined_counts
GROUP BY LOCATION

This works because:

• We first calculate how many times each location appears in each table
• UNION ALL stacks these results together (so MANILA will have two entries: 4 and 2)
• Finally, we group by location and sum the two counts to get 6 for MANILA, and 6 for SINGAPORE

Solution 2: Join Pre-Aggregated Subqueries #

If you prefer a join-based approach, you can aggregate each table first, then join the results and add the counts:

SELECT 
    COALESCE(t1.LOCATION, t2.LOCATION) AS LOCATION,
    COALESCE(t1.count_t1, 0) + COALESCE(t2.count_t2, 0) AS total_count
FROM (
    SELECT LOCATION, COUNT(*) AS count_t1
    FROM TABLE1
    GROUP BY LOCATION
) t1
FULL OUTER JOIN (
    SELECT LOCATION, COUNT(*) AS count_t2
    FROM TABLE2
    GROUP BY LOCATION
) t2 ON t1.LOCATION = t2.LOCATION

Using FULL OUTER JOIN ensures we include locations that only exist in TABLE2 (if any), and COALESCE handles null values by replacing them with 0 so the addition works correctly.

Why Your Original Query Failed #

To recap: joins multiply matching records when there are multiple entries in both tables. Instead of summing the counts, you were counting the number of joined rows, which is the product of the two table's counts for that location. Aggregating before joining (or using UNION ALL) avoids this Cartesian product issue.

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