null in the table - 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 2005 Forums

Analysis Server and Reporting Services (2005)

null in the table

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-22 : 02:15:56

hi;

i have a table like that

Col1 Col2  Col3
1     AA    zz
2     BB    yy
3     NULL  xx

i have this query select * from Table1 WHERE col1 LIKE @col1 and col2 LIKE @col2

i put %% in all paramter.

the problem is when i insert in col1=3 than i do not get result because the reporting dont recognize the null value.
(do i need to upadte the colums where i have null value?)

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 02:48:12

select * from Table1 WHERE col1 LIKE @col1 and (col2 LIKE @col2 OR col2 IS NULL)

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 02:49:53

Any logic test to a column that is NULL will return FALSE

Col1 < NULL = FALSE
Col1 > NULL = FALSE
Col1 = NULL = FALSE (Use Col1 IS NULL)
Col1 IN ('AAA', 'BBB', NULL) = FALSE

etc

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-22 : 04:32:11

Kristen

if i put(in preview section) just in parameter Col2 = %AA% and col1=%%

in the result i get the line with AA plus
line 3 (3 NULL xx)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 04:53:12

quote:
Originally posted by Kristen

Any logic test to a column that is NULL will return FALSE

Col1 < NULL = FALSE
Col1 > NULL = FALSE
Col1 = NULL = FALSE (Use Col1 IS NULL)
Col1 IN ('AAA', 'BBB', NULL) = FALSE

etc

They aren't false. They are NULL

Assuming that value checked in col1 IS NULL Then:


( Col1 < NULL ) IS NULL
( Col1 > NULL ) IS NULL
( Col1 = NULL ) IS NULL

If they returned a FALSE then this kind of code would work (It would print 'foo'.


/*

Col1 < NULL = FALSE
Col1 > NULL = FALSE
Col1 = NULL = FALSE (Use Col1 IS NULL)
Col1 IN ('AAA', 'BBB', NULL) = FALSE

*/

DECLARE @test INT

SET @test = NULL

IF @test < 2 PRINT 'foo'
IF NOT (@test < 2 ) PRINT 'foo'

And it doesn't.

A comparison of a NULL returns a NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-22 : 05:08:52

Transact Charlie

sorry but i dont understand you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 05:14:35

SQL uses a 3 value logic system. So a sql boolean can either be

1 - TRUE
0 - FALSE
NULL - UNKNOWN

Because you have a 3 value logic system then if you are dealing with a NULL value you cannot say that a comparison to it is FALSE or TRUE because the value is unknown.

If you say

Let @foo be a variable that can contain integers (whole numbers)

Let @foo be UNKNOWN (NULL)

is @foo = 3?

You can't say for sure that @foo is definitely NOT 3 so the outcome of that comparison cannot be FALSE. Also you can't say for sure that @foo is equal to 3 so the comparison can't be TRUE. The comparison is therefore UNKNOWN (NULL)

Try reading this for a gentle introduction.

http://en.wikipedia.org/wiki/Null_(SQL)#Three-valued_logic_.283VL.29

I can't pass this as a hyper link because it's got an underscore in it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 06:14:37

" They aren't false. They are NULL"

Quite right! I meant false in the sense of "They won't be selected".

"if i put(in preview section) just in parameter Col2 = %AA% and col1=%%

in the result i get the line with AA plus
line 3 (3 NULL xx)"

You do? I don't



DECLARE @Temp TABLE
(
	Col1 VARCHAR(10),
	Col2 VARCHAR(10),
	Col3 VARCHAR(10)
)

INSERT INTO @Temp
SELECT	'1', 'AA', 'zz' UNION ALL
SELECT	'2', 'BB', 'yy' UNION ALL
SELECT	'3', NULL, 'xx'

select * from @Temp WHERE col1 LIKE '%%' AND col2 LIKE '%AA%'

Gives me:

       Col2       Col3       
---------- ---------- ---------- 
1          AA         zz

whereas if I use the syntax I recommended earlier:


select * from @Temp WHERE col1 LIKE '%%' AND  (col2 LIKE '%AA%' OR col2 IS NULL)

Gives me:

Col1       Col2       Col3       
---------- ---------- ---------- 
1          AA         zz
3          NULL       xx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources