foxed by nulls! - 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

General SQL Server Forums

New to SQL Server Programming

foxed by nulls!

Author

Topic

obscuregirl
Starting Member

41 Posts

Posted - 2008-08-29 : 06:34:52

I'm getting very confused! It's easily done with me! If anyone can help with what is probably a very basic question, I'd be eternally grateful!

I have a view that only returns one record, I have a table that contains many records including the one in the view. I need to do a join that finds the record that matches the view in the table.

Some of the fields contain nulls, if I join just on the fields that are not null, I get two records back because the values are not unique, if I join on all possible values (some of which are null in this case), I don't get anything back from the table.

Do joins not treat nulls as matching values? I thought that if the corresponding view and table values were the same (a value or a null) it should have returned the record I was looking for but it doeesn't.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:39:14

JOINs for a NULL columns are discarded.
Some ANSI settings may override that behaviour.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:44:57

[code]DECLARE @t1 TABLE (i INT)

INSERT @t1
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT 2

DECLARE @t2 TABLE (i INT)

INSERT @t2
SELECT 0 UNION ALL
SELECT NULL UNION ALL
SELECT 2

SELECT *
FROM @t1 AS t1
INNER JOIN @t2 AS t2 ON t2.i = t1.i

SELECT *
FROM @t1 AS t1
INNER JOIN @t2 AS t2 ON COALESCE(t2.i, -1) = COALESCE(t1.i, -1)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:45:41

NULL is treated as UNKNOWN.

If you have two unknown values, how do you if they are equal?

E 12°55'05.25"
N 56°04'39.16"

obscuregirl
Starting Member

41 Posts

Posted - 2008-08-29 : 06:48:15

quote:
Originally posted by Peso

NULL is treated as UNKNOWN.

If you have two unknown values, how do you if they are equal?

E 12°55'05.25"
N 56°04'39.16"

I guess that would explain it! Thanks Peso, I'll read up on the ansi settings!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 06:54:53

quote:

Originally posted by Peso

DECLARE	@t1 TABLE (i INT)

INSERT	@t1
SELECT	1 UNION ALL
SELECT	NULL UNION ALL
SELECT	2

DECLARE	@t2 TABLE (i INT)

INSERT	@t2
SELECT	0 UNION ALL
SELECT	NULL UNION ALL
SELECT	2

SELECT		*
FROM		@t1 AS t1
INNER JOIN	@t2 AS t2 ON t2.i = t1.i

SELECT		*
FROM		@t1 AS t1
INNER JOIN	@t2 AS t2 ON COALESCE(t2.i, -1) = COALESCE(t1.i, -1)

E 12°55'05.25"
N 56°04'39.16"

I would not use method 2 as it wont work for all set of data
See what happens if data to @t1 are

SELECT 1 UNION ALL
SELECT -1 UNION ALL
SELECT 2

I prefer using


SELECT		*
FROM		@t1 AS t1
INNER JOIN	@t2 AS t2 ON t2.i = t1.i or (t2.i is null and t1.i is null)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:58:38

Doesn't it depend on business rules?
If i column should always be a positive value greater than zero, the use of -1 is ok.

But you are right in that your suggestion is more versatile.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 07:08:28

quote:
Originally posted by Peso

Doesn't it depend on business rules?
If i column should always be a positive value greater than zero, the use of -1 is ok.

But you are right in that your suggestion is more versatile.

E 12°55'05.25"
N 56°04'39.16"

Yes it is

Madhivanan

Failing to plan is Planning to fail

obscuregirl
Starting Member

41 Posts

Posted - 2008-08-29 : 08:36:13

Thanks everyone!

I ended up using

SELECT *
FROM @t1 AS t1
INNER JOIN @t2 AS t2 ON t2.i = t1.i or (t2.i is null and t1.i is null)

and it worked perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources