Re: Sybase/Sql server Outer Joins, nulls, and counts

Your queries are strange.

> select count(*)
> from E621.dbo.THE_TABLE r /* 303990 rows */
> LEFT OUTER JOIN
> E622.dbo.THE_TABLE e /* 216816 rows */
> On r.unique_id = e.unique_id
>
> 303990

As expected. I think you understand this one.

> select count(*)
> from E621.dbo.THE_TABLE r
> LEFT OUTER JOIN
> E622.dbo.THE_TABLE e
> On r.unique_id = e.unique_id
> And e.unique_id is NULL
>
>
> 303990

As expected. All results from E621.dbo.THE_TABLE are retained in the LEFT OUTER JOIN.
> select count(*)
> from E621.dbo.THE_TABLE r
> LEFT OUTER JOIN
> E622.dbo.THE_TABLE e
> On r.unique_id = e.unique_id
> And e.unique_id is NOT NULL
>
>
> 303990

As expected. All results from E621.dbo.THE_TABLE are retained in the LEFT OUTER JOIN.
>
> This is really, really wierd. I should be getting 87174 rows
> difference. But I'm not.
>

No, you shouldn't. You would if you had been using your WHERE clause properly.

SELECT COUNT(*)
FROM E621.dbo.THE_TABLE r

    LEFT OUTER JOIN E622.dbo.THE_TABLE e ON r.unique_id = e.unique_id WHERE e.unique_id IS NOT NULL

You could get everything in one query, of course:

SELECT COUNT(r.unique_id) AS Total, COUNT(e.unique_id) AS WithMatches FROM E621.dbo.THE_TABLE r

    LEFT OUTER JOIN E622.dbo.THE_TABLE e ON r.unique_id = e.unique_id

NULL is omitted from aggregate expressions. Received on Tue Oct 24 2006 - 15:48:48 CDT