Sybase/Sql server Outer Joins, nulls, and counts

From: <dba_222_at_yahoo.com>
Date: 24 Oct 2006 13:33:43 -0700
Message-ID: <1161722023.445320.263110@i42g2000cwa.googlegroups.com>

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 - 15:33:43 CDT