SELECT s.SubjectsKey, CountPerKey = ISNULL(d.CountPerKey,0)FROM Subjects sLEFT JOIN ( SELECT SubjectsIKey, CountPerKey = COUNT(*) FROM Subjects_items GROUP BY SubjectsIKey) d ON d.SubjectsIKey = s.SubjectsKeySELECT s.SubjectsKey, CountPerKey = COUNT(i.SubjectsIKey)FROM Subjects sLEFT JOIN Subjects_items i ON i.SubjectsIKey = s.SubjectsKeyGROUP BY s.SubjectsKeySELECT s.SubjectsKey, CountPerKey = ISNULL(d.CountPerKey,0)FROM Subjects sOUTER APPLY ( SELECT SubjectsIKey, CountPerKey = COUNT(*) FROM Subjects_items i WHERE i.SubjectsIKey = s.SubjectsKey GROUP BY SubjectsIKey) d