When working with relational databases, combining numeric values across two different tables is a standard task. However, a common pitfall occurs when an item exists in one table but not the other: standard addition results in a NULL total, and a standard LEFT JOIN fails to capture items exclusive to the second table.

Understanding Why NULL Propagates in SQL

In SQL arithmetic, performing an operation with NULL almost always returns NULL. For instance, evaluating 3 + NULL yields NULL rather than 3. Furthermore, using a LEFT JOIN drops any items present strictly in the right-hand table, while filling unmatched rows from the right-hand table with NULL.

To solve this problem cleanly, you need an approach that catches all records across both tables and safely treats missing quantities as 0.

Solution 1: UNION ALL with GROUP BY (Recommended)

The cleanest, most readable, and cross-database compatible method is to combine both datasets using UNION ALL within a derived table, then aggregate the quantities using SUM(). SQL's SUM() function automatically ignores NULL values during aggregation.

SELECT 
    Fruit,
    SUM(Quantity) AS [Total Quantity]
FROM (
    SELECT Fruit, Quantity FROM Table1
    UNION ALL
    SELECT Fruit, Quantity FROM Table2
) AS CombinedFruits
GROUP BY Fruit;

Why this works best:

  • Full Coverage: Captures fruits present in Table1, Table2, or both.
  • Handles NULLs Automatically: Aggregate functions like SUM() ignore missing rows instead of producing NULL.
  • High Performance: UNION ALL avoids unnecessary deduplication passes compared to standard UNION.

Solution 2: FULL OUTER JOIN with COALESCE

If your relational database supports FULL OUTER JOIN (such as SQL Server, PostgreSQL, or Oracle), you can join both tables completely and wrap missing values in COALESCE(). The COALESCE() function returns the first non-null value in its argument list.

SELECT 
    COALESCE(T1.Fruit, T2.Fruit) AS Fruit,
    COALESCE(T1.Quantity, 0) + COALESCE(T2.Quantity, 0) AS [Total Quantity]
FROM Table1 T1
FULL OUTER JOIN Table2 T2 ON T1.Fruit = T2.Fruit;

Why this works:

  • COALESCE(T1.Fruit, T2.Fruit) ensures you get the fruit name regardless of which table it appears in.
  • COALESCE(T1.Quantity, 0) converts NULL values to 0 before addition occurs.

Conclusion

If you are working with MySQL (which lacks native FULL OUTER JOIN support) or simply want the most readable SQL query, the UNION ALL with GROUP BY approach is the best overall solution. For databases supporting full joins, FULL OUTER JOIN with COALESCE provides an explicit join-based alternative.