You are here:Home » tsql » COUNT(*) with a LEFT JOIN may produce unexpected results - SQL Server

COUNT(*) with a LEFT JOIN may produce unexpected results - SQL Server

We should not use count (*) for left outer join two or more tables with group by. Instead we can use right table's column for count, otherwise it will come up with unexpected output.

Way cause for unexpected output
 
SELECT 
    a.id, 
    COUNT(*)
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO
Right way
 
SELECT 
    a.id, 
    COUNT(b.id) 
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO
Create tables with sample records, run above queries for them and see difference. Did you know this earlier?