Group on table joins

This is one of the more confusing issues of table joins. To make it extra confusing, try an outer join:

Select * from ta as a
left outer join tb as b
on a.key = b.key

Both tables have duplicate records for the field they are joined on. To clear up which tables have which records, I want to group on the key. Which table should I group by key?

I suspect there is not a general answer, it will depend on what you want to do. However, choose a simpler case, where ta has no duplicates in the key field, and tb does.
The answer was not what I expected (yes, I know, shows what a dunce I am). The CORRECT answer is to group on a.key

Select * from ta as a
left outer join tb as b
on a.key = b.key
group by a.key

Why? Say table b has no fields with key = x and key = y. You can catch this by using
where b.key is null
BUT if you group by b.key, the value of both these non-existent records in tb is null, so only one of them will show up as null, and this will also mean that only one of these values, x and y, is selected in ta. Tricky for guys like me.

This entry was posted in Programming. Bookmark the permalink.