I have 2 tables like this:

tableA:

CustomerID COUNT(OrderID)
A          3
B          2
C          1

tableB:

CustomerID COUNT(OrderID)
B          4
D          5
E          6

How can I join these 2 tables together, with non-existing rows being 0? Expected result like this:

CustomerID COUNT(OrderID) COUNT(OrderID)
A          3              0
B          2              4
C          1              0
D          0              5
E          0              6

CodePudding user response：

You need a full outer join such as

select
    Coalesce(A.customerId, B.customerId) as CustomerId,
    Coalesce(A.Acount, 0) as Acount,
    Coalesce(B.Bcount, 0) as Bcount
from A full outer join B on A.customerId = B.customerId;

CodePudding user response：

You can union two queries:

• the first query selects all records from tableA and all their matches from tableB, allowing the case when there is no match at tableB, in which case the second count is evaluated to 0, grouped by CustomerID of course
• the second query selects all records from tableB that have no match in tableA (because records having a match were already selected in the first query, this one selects those records from tableB that had no match) and gets the count, grouping by CustomerID, of course

SELECT tableA.CustomerID, COUNT(tableA.OrderID), COUNT(tableB.OrderID)
FROM tableA
LEFT JOIN tableB
ON tableA.CustomerID = tableB.CustomerID
GROUP BY tableA.CustomerID
UNION
SELECT tableB.CustomerID, 0, COUNT(tableB.OrderID)
FROM tableA
LEFT JOIN tableB
ON tableA.CustomerID = tableB.CustomerID
WHERE tableA.CustomerID IS NULL
GROUP BY tableB.CustomerID;