Sybase/Sql server Outer Joins, nulls, and counts
dba...@yahoo.com
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!
Lionstone
> 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.
Brian Peasland
> 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
Try the following:
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
See if that works.
HTH,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
dba...@yahoo.com
WHERE e.unique_id is NULL
133295
WHERE e.unique_id is NOT NULL
170695
This looks much better.
Now they both add up to the count with no WHERE clause at all.
If only the SQL compiler would catch the bad syntax. :(
Gert-Jan Strik
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
fitzj...@cox.net
dba...@yahoo.com wrote:
> Good one! Thanks guys!
>
> WHERE e.unique_id is NULL
> 133295
>
>
> WHERE e.unique_id is NOT NULL
> 170695
>
>
> This looks much better.
> Now they both add up to the count with no WHERE clause at all.
>
>
> If only the SQL compiler would catch the bad syntax. :(
>
>
It does; you didn't have any 'bad syntax'. What the SQL engine won't
do is correct for poor logic, which you did have.
David Fitzjarrell
dba...@yahoo.com
It is the same logic that has worked perfectly hundreds,
probably thousands, of times for me before.
Oracle would not let such a statement even compile.
(can't have an AND without first having a WHERE)
The compiler would give an error. In which case, I would have just
corrected the syntax and got on with it.
The sql server/Sybase compilers allowed the bad syntax.
Thus the statement:
If only the SQL compiler would catch the bad syntax. :(
Erland Sommarskog
> Hmm. The logic was all laid out in the examples in the first posting.
> It is the same logic that has worked perfectly hundreds,
> probably thousands, of times for me before.
>
> Oracle would not let such a statement even compile.
> (can't have an AND without first having a WHERE)
> The compiler would give an error. In which case, I would have just
> corrected the syntax and got on with it.
>
> The sql server/Sybase compilers allowed the bad syntax.
> Thus the statement:
> If only the SQL compiler would catch the bad syntax. :(
ANSI joins work. Since this syntax works on Oracle too, as far as
I know, there is good reason to migrate to that syntax for Oracle as
well.
When you say
A LEFT JOIN B ON a.col = b.col AND b.othercol = 2
This means that you include all rows from table A. In the columns for
table B, all are NULL where othercol has another value than 2, as well
as those rows there no row in B match A.
This gives a new virtual table what can be filterted with WHERE.
I don't know about Oracle's proprieraty syntax, but SQL Server and
Sybase has an old syntax *=, which in SQL 2005 is only available in
compatibility mode. (I don't know about Sybase.) And for a very good
reason. The *= and =* operators were ambiguous, and the result you
got was often confusing. And there was no *=* so full joins were
not possible.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Roy Harvey
>> On r.unique_id = e.unique_id
>> And e.unique_id is NULL
>Oracle would not let such a statement even compile.
>(can't have an AND without first having a WHERE)
>The compiler would give an error. In which case, I would have just
>corrected the syntax and got on with it.
There is nothing illegal about that syntax, as the AND is simply part
of the ON clause. I suspect that Oracle does allow compound testing
in the ON clause.
Roy
David Portas
> Hmm. The logic was all laid out in the examples in the first posting.
> It is the same logic that has worked perfectly hundreds,
> probably thousands, of times for me before.
>
> Oracle would not let such a statement even compile.
> (can't have an AND without first having a WHERE)
> The compiler would give an error. In which case, I would have just
> corrected the syntax and got on with it.
>
In that case Oracle is not allowing the use of standard SQL because it
is perfectly legal to use AND in the ON clause. But I suspect you are
talking about Oracle 8i and the old-style outer join syntax, which is
non-standard anyway. AFAIK your queries are legal in Oracle 9.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--