ISNULL problem with LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ISNULL problem with LEFT JOIN

Author

Topic

richwatson
Starting Member

3 Posts

Posted - 2008-01-25 : 14:04:46

Hi,

I have a very bizarre problem I am having trouble solving. I am running the following query on a newly installed SQLServer 2000 database with all options (ANSI NULLs etc) left at default (Off). This all works as expected on my 2005 development database.

I have two tables, the first contains a list of labels which are then associated to Users in the second table. I'm trying to get a full list of of the labels with a Y or N value for a particular user.

The two tables have the following data:

Select Label from LabelTable
-----------------------------------------
ALabel
BLabel
CLabel
DLabel
ELabel

Select Label, User From UserLabels
-----------------------------------------------------
BLabel, Rich
DLabel, Rich
ALabel, Bob
ELabel, Dave

This is my SQL statement, with the results I expected and then those I actually get:

SELECT Label, ISNULL(a.selected, 'N') as selected
FROM LabelTable LEFT OUTER JOIN
(SELECT Label, 'Y' as selected
FROM UserLabels WHERE User = 'Rich') as a

Expected results:
----------------------------------------------------------------------
ALabel, N
BLabel, Y
CLabel, N
DLabel, Y
ELabel, N

Actual results:
---------------------------------------------------------------------
ALabel, Y
BLabel, Y
CLabel, Y
DLabel, Y
ELabel, Y

Obviously, expecting NULLs, I checked to see if I actually get them, and (apparently) I do:

SELECT Label, a.selected
FROM LabelTable LEFT OUTER JOIN
(SELECT Label, 'Y' as selected
FROM UserLabels WHERE User = 'Rich') as a
----------------------------------------------------------------------------
ALabel, NULL
BLabel, Y
CLabel, NULL
DLabel, Y
ELabel, NULL

But, if I put an IS NOT NULL clause on the end, it doesn't have an effect:

SELECT Label, a.selected
FROM LabelTable LEFT OUTER JOIN
(SELECT Label, 'Y' as selected
FROM UserLabels WHERE User = 'Rich') as a
WHERE a.selected IS NOT NULL
----------------------------------------------------------------------------
ALabel, NULL
BLabel, Y
CLabel, NULL
DLabel, Y
ELabel, NULL

Having said all that, if I reuse "Label" in the ISNULL function, I DO get the results I expect:

SELECT Label, ISNULL(a.selected, Label) as selected
FROM LabelTable LEFT OUTER JOIN
(SELECT Label, 'Y' as selected
FROM UserLabels WHERE User = 'Rich') as a
----------------------------------------------------------------------
ALabel, A
BLabel, Y
CLabel, C
DLabel, Y
ELabel, E

SELECT Label, ISNULL(a.selected, Label) as selected
FROM LabelTable LEFT OUTER JOIN
(SELECT Label, 'Y ' as selected
FROM UserLabels WHERE User = 'Rich') as a
---------------------------------------------------------------------
ALabel, ALabel
BLabel, Y
CLabel, CLabel
DLabel, Y
ELabel, ELabel

What is going on?????!! Has anyone seen this before? I know there are other ways of doing this, such as a CASE statement, but I'd like to know what is going on as it will probably affect other more complex queries I've got.

Thanks in advance for any help,

Rich

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-25 : 15:06:59

Humm, I ran this code on both 2005 and 2000 boxes and get the same correct result:

DECLARE @LabelTable TABLE (Label VARCHAR(6))

INSERT @LabelTable
SELECT 'ALabel'
UNION ALL SELECT 'BLabel'
UNION ALL SELECT 'CLabel'
UNION ALL SELECT 'DLabel'
UNION ALL SELECT 'ELabel'

DECLARE @UserLabels TABLE (Label VARCHAR(6), UserName VARCHAR(4))

INSERT @UserLabels
SELECT 'BLabel', 'Rich'
UNION ALL SELECT 'DLabel', 'Rich'
UNION ALL SELECT 'ALabel', 'Bob'
UNION ALL SELECT 'ELabel', 'Dave'


SELECT 
	l.Label, 
	ISNULL(a.selected, 'N') as selected
FROM 
	@LabelTable AS l
LEFT OUTER JOIN
	(
		SELECT Label, 'Y' as selected 
		FROM @UserLabels WHERE UserName = 'Rich'
	) as a
	ON l.Label = a.Label

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-26 : 05:25:27

I ran this:-

SELECT l.Label, ISNULL(a.selected, 'N') as selected
FROM @LabelTable l 
LEFT OUTER JOIN
(SELECT Label, 'Y' as selected 
FROM @UserLabels WHERE User = 'Rich') as a
ON a.Label=l.Label

and got

Label selected
-------------------------------------------------- --------
ALabel N
BLabel N
CLabel N
DLabel N
ELabel N

while i ran this:-

SELECT l.Label, ISNULL(a.selected, 'N') as selected
FROM @LabelTable l 
LEFT OUTER JOIN
(SELECT Label, 'Y' as selected 
FROM @UserLabels WHERE [User] = 'Rich') as a
ON a.Label=l.Label

i got this:-

Label selected
-------------------------------------------------- --------
ALabel N
BLabel Y
CLabel N
DLabel Y
ELabel N

looks like its distinguishing user as some function as the result of subquery is varying

SELECT Label, 'Y' as selected
FROM @UserLabels WHERE [User] = 'Rich'

yields

Label selected
-------------------------------------------------- --------
BLabel Y
DLabel Y

while

SELECT Label, 'Y' as selected
FROM @UserLabels WHERE User = 'Rich'

yields nothing

Label selected
-------------------------------------------------- --------

richwatson
Starting Member

3 Posts

Posted - 2008-01-27 : 12:18:46

I've now tried the same query on another 2000 server and it works fine. I can't see a difference between the two setups - I think I've done the same on both! I'm still very confused!!

visakh16 - Thanks for your post - my subquery works fine, so this isn't the problem.

I think it is the way that the database is using NULL's. I created this database using Enterprise Manager from SQLServer 2005, could this be causing the problems?

Thanks,

Rich

richwatson
Starting Member

3 Posts

Posted - 2008-01-28 : 13:58:24

It all works fine if I use COALESCE instead of ISNULL, which is a good enough workaround. Still don't know why it works on one server and not another, maybe I've missed a service pack somewhere!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-28 : 16:22:14

Duh, Can't believe I didn;t see it before.. I guess that is why I do NOT use reserved words..

You will notice that your column name is User. As, visakh16 showed, if you use that without delimiting the columns(i.e. [User]) then sql seems to be using that value. So, make sure you delimit your objects named with reserve words. To see what is happening run:

SELECT USER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources