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

From: Gert-Jan Strik <sorry_at_toomuchspamalready.nl>
Date: Tue, 24 Oct 2006 23:16:49 +0200
Message-ID: <453E82C1.3542E0FF@toomuchspamalready.nl>

Filtering of rows of the table that is retained needs to take place in the WHERE clause. Adding the predicate to the ON clause of the outer join will not eliminate rows from the outer table.

So you would need

  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

and

  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 NULL

Gert-Jan

dba_222_at_yahoo.com wrote:
>
> Dear Experts,
>
> I'm an Oracle guy, but I've been given an assignment to compare data in
> two different Sybase "databases".
>
> Given that I don't have the MINUS, or the INTERSECT command, and in
> this Sybase version, not even the inline views, I am trying to use
> Outer Joins.
>
> Now, in Oracle, when I use an outer join, the resulting query would
> look like this:
>
> Table A Table B:
>
> 123 123
> 124
> 125 125
> 126 126
> 127 127
> 128
> 129 129
>
> 7 rows
>
> with NULL values where the data did not exist in the table
> represented on the right.
>
> If I add in the where clause,
> where b.fieldname is NULL, I will get:
>
> Table A Table B:
>
> 124
> 128
>
> 2 rows
>
> If I add in the where clause,
> where b.fieldname is NOT NULL, I will get:
>
> Table A Table B:
>
> 123 123
> 125 125
> 126 126
> 127 127
> 129 129
>
> 5 rows
>
> So, if I change the query to show the counts of the results,
> the two variations on the where clause,
> where b.fieldname is NULL
> where b.fieldname is NOT NULL
> Will add up to the count with no clause for a null check.
>
> Nice and intuitive.
>
> However, I'm getting some really STRANGE results in Sybase
> and SQL Server.
>
> In this case
>
> 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
>
> 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
>
> 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
>
> the same thing occurs if I use:
> e.unique_id = null
> or
> e.unique_id <> null
>
> This is really, really wierd. I should be getting 87174 rows
> difference. But I'm not.
>
> I have been able to get results by inserting all the data into a #temp
> table, and querying that. But I should not have to do that every time.
>
> What is the secret to get the OUTER JOIN to work as I described above?
> 3 queries, 3 different row sets.
>
> Thanks a lot!
Received on Tue Oct 24 2006 - 16:16:49 CDT