LEFT OUTER JOIN Teaser
-
I have isolated some curious behaviour in SQL Server and wonder what people think of it!
I have a single table with an integer ID column.
Table tbl (ID int NOT NULL) containing 1,2,3,4,5,6,7,8
I have a single view which returns 1 for selected rows in the table.
View vw AS SELECT ID, 1 As Val FROM tbl WHERE ID < 5
In SQL Analyser, ISNULL appears to be returning inconsistent results;
SELECT tbl.ID, Val, ISNULL(Val,0) As [Val2]
FROM tbl LEFT OUTER JOIN vw ON tbl.ID=vw.ID
Returns
ID Val Val2
1 1 1
2 1 1
3 1 1
4 1 1
5 NULL 1
6 NULL 1
7 NULL 1
8 NULL 1
Doesnt seem right to me! What do you think.
-
wierd - dont' think I'd expect that, but haven't got access to sql server today to play with
does COALESCE(Val, 0) behave the same?
and do you get the same from ...
SELECT tbl.ID, Val, ISNULL(Val,0) As [Val2]
FROM tbl LEFT OUTER JOIN (select id, val from vw) vw ON tbl.ID=vw.ID
also is your instance on a standalone box or parallel setup, and is it all patched up?
Jon
-
Hi Jon
COALESCE(Val,0) returns the expected value of 0 for the missing rows in vw. Thats something Ive learnt today - I didnt know about COALESCE. Thanks.
CASE WHEN Val IS NULL THEN 0 ELSE Val END also works as expected.
SQL Server version is 8.00.760(SP3) which I think is fully patched on a standalone box.
Andy
-
Instead of view if the result set is stored in a # table, isnull is working fine.
SELECT ID, 1 As Val into #temp2 FROM tb1 WHERE ID < 5
SELECT tb1.ID, vw.Val, isnull(vw.Val,0) As Val2
FROM tb1 left OUTER JOIN #temp1 vw ON tb1.ID=vw.ID
Rajesh
-
Hi Rajesh - Yes, you are right. The first thing I did was to try it on a pair of tables instead of the table and the view and it works as expected.
-
Interesting - i didn't know that isnull worked differently from coalesce either, so thanks for that
.I'm just used to using coalesce, and it's quite nice because you can try lots of things - e.g COALESECE(tbl.A, tbl.B, tbl.C, 0)
But it definitely looks like a bug then if that has different behaviour. Only asked if it was a parallel system because they seem (from very small experience with DB2 and SQLServer parallel setups) to have the odd prob or two with ISNULL for some reason.
Jon
-
I think because a View is query rewrite and OUTER JOIN default mathematically to a NULL. That is the reason extra care is needed to get expected results. Check out SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer for more info about JOINS. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
That is interesting behavior, especially when COALESCE works. Note that if you change the constant in the view to a case statement, the isnull join works as expected.
View vw AS SELECT ID, case when ID<5 then 1 else null end As Val FROM tbl WHERE ID < 5
-
Actually use "ID>0" in the case statement, then you just have to modify the where clause
-
When does 'interesting behaviour' get to be a bug?
When ISNULL(Val, 0) returns 1 when val returns NULL, that is a bug.
Thanks for the input guys. If I had the energy I might see what Microsoft has to say about it, but I have to move on.
Andy
-
optimists read no further
after a few phonecalls ... please give complete core dumps of everything your'e doing ... followed a week later by yes, the guys know about that one, but everyone's trying to get sql 2005 to work 
... followed a few days later by ... hello, were you satisfied with your microsoft support ? 
-
Exactly
ttfn
-
The results may be slightly unexpected, but if you look at "View Resolution" in BOL, it might explain what SQL Server is actually doing! Remember that a View isn't a table, it's effectively a sub-query when used in a query!
-
I am sure there are explanations for its mis-behaviour, but to me its more than 'slightly unexpected'. Yes, I am sure that under the lid there may be indicators that things are not what they seem, but should I have to root around to justify it?
It may be that a view isnt a table but a sub-query. It may be that the View Resolution in BOL explains what SQL Server is actually doing. Nevertheless, in a sane and ordered world, ISNULL(Val,0) should return the same as COALESCE(Val,0).
Maybe I ask too much.
-
The query optimiser may be applying the WHERE in the view after the ISNULL.
In the SQL Server engine, then there is no guarantee of the JOIN being executed before the WHERE and vice versa.
Example:
SELECT * FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.col = tbl2.col WHERE tbl2.col IS NULL
may not return nonmatching rows, but this will
SELECT * FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.col = tbl2.col OR tbl2.col IS NULL
In this instance, try the NOEXPAND hint on the view to change how SQL evaluates the query:
SELECT tbl.ID, Val, ISNULL(Val,0) As [Val2]
FROM tbl LEFT OUTER JOIN vw (NOEXPAND) ON tbl.ID=vw.ID
I guess that COALESCE or CASE also changes how SQL evaluates the query.
All code untested!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply