SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


comparing NULLs


comparing NULLs

Author
Message
pedro.ribeiro
pedro.ribeiro
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 982
Hi Masters,


I have a question about comparing strings and number with columns that have NULLs inside.

If i compare a column that as a NULL value with a column that as a integer or a varchar, the result is allways false. Correct?

Anything that i compare with a NULL (including other NULL) will always be avaliated as false.

Correct?

Tks,

Pedro
JacekO
JacekO
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 615
Not always it depends on ANSI_NULLS setting

This is from books online:

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the Region column in the Northwind Customers table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region IN ('WA', 'SP', 'BC')
OR Region IS NULL

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL

Regardless of the ANSI_NULLS setting, Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords. Also, a unique index or UNIQUE constraint that allows NULL can contain only one row with a NULL key value. A subsequent row with NULL is rejected. A primary key cannot have NULL in any column that is part of the key.

Computations involving NULL evaluate to NULL because the result must be UNKNOWN if any of the factors is unknown. For example, column1 + 1 evaluates to NULL if column1 is NULL.

When the columns being searched include those defined as allowing null values, you can find null or nonnull values in the database with this pattern:

WHERE column_name IS [NOT] NULL

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19080 Visits: 14900
If your server/session has ANSI_NULLS ON then any comparison to a NULL value results in Unknown, not true or false. ANSI_NULLS is ON by default in SQL Server so you should use IS NULL/IS NOT NULL when doing comparison to NULL values.

See NULL Comparison Search Conditions in BOL.

Edit: JacekO beat me to it.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1919 Visits: 409
In addition to the above you may want to check out the below link

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12637 Visits: 18584
JacekO (4/1/2009)
Not always it depends on ANSI_NULLS setting

This is from books online:

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the Region column in the Northwind Customers table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region IN ('WA', 'SP', 'BC')
OR Region IS NULL

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL

Regardless of the ANSI_NULLS setting, Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords. Also, a unique index or UNIQUE constraint that allows NULL can contain only one row with a NULL key value. A subsequent row with NULL is rejected. A primary key cannot have NULL in any column that is part of the key.

Computations involving NULL evaluate to NULL because the result must be UNKNOWN if any of the factors is unknown. For example, column1 + 1 evaluates to NULL if column1 is NULL.

When the columns being searched include those defined as allowing null values, you can find null or nonnull values in the database with this pattern:

WHERE column_name IS [NOT] NULL



A niggly comment first: NULL is not a value, it's the absence of any value, so "null value" doesn't compute. In a database sense NULL is a "state", and not any kind of value.

With that in mind, what ANSI_NULLS does is to allow you to WRITE "variable is NULL" as "Variable=NULL". It does NOT allow you do perform actual comparisons against NULLS, nor comparisons between two variables in a null state, etc.... Any unary operations on that statement will trigger the parser to treeat that as a comparison, and thus will fail.

Try this script on for size so you can get a better idea. Just toggle between ANSI_NULLS OFF and ANSI_NULLS ON at the top.


use testing
set ansi_nulls ON;

declare @n1 varchar(20)
declare @n2 varchar(20)

set @n1=null
set @n2='h'

select case when @n1=@n2 then 'true' else 'false' end
select case when NOT @n1=@n2 then 'true' else 'false' end
select case when @n1>=@n2 then 'true' else 'false' end
select case when @n1<=@n2 then 'true' else 'false' end
select case when @n1<>@n2 then 'true' else 'false' end
set @n2=null
select 'switch'
select case when @n1=@n2 then 'true' else 'false' end
select case when NOT @n1=@n2 then 'true' else 'false' end
select case when @n1>=@n2 then 'true' else 'false' end
select case when @n1<=@n2 then 'true' else 'false' end
select case when @n1<>@n2 then 'true' else 'false' end






----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 7914
While not available in SQL2000, the INTERSECT and EXCEPT operators compare NULLs as being equal even with ANSI_NULLS on.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19080 Visits: 14900
That actuallly makes sense as NULL's are also treated as equal in primary key's as well.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12637 Visits: 18584
Jack - I have to ask - are you talking about conceptual PK's?

I'm pretty sure you cannot have anything NULL in a primary key, at least not one actually enforced by SQL Server.

Just curious.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19080 Visits: 14900
Duh, my bad Matt. I had a brain cramp. A primary key cannot contain a nullable column. I was thinking unique constraints which would be a natural or conceptual primary key. So this code will only allow the first insert to complete.


CREATE TABLE null_primary_key_test
(
id INT IDENTITY(1,1),
null_column INT NULL CONSTRAINT uc_null_column UNIQUE
)
GO

INSERT INTO null_primary_key_test (
null_column
)
SELECT NULL

-- the 2 causes the batch to execute twice
GO 2

DROP TABLE null_primary_key_test





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search