February 1, 2012 at 8:36 am
Dear Friends,
help me find out null comparision logic
for example:
select case when null=null then 1 else 0 end
returns:0 ->this means null is not equal to null ok
now we switch to another concept that prove null is equal to null
create table t (id int unique )
insert into t values(null)
1 row affacted
again
insert into t values(null)
returns
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__t__4830B400'. Cannot insert duplicate key in object 'dbo.t'.
The statement has been terminated.
this means null compares to values in table t for unique key that shows null is equal to null that is already present in
given table..
i am very confuse is null equal to null or not
i have test it in oracle ..
that allows null duplicacy in a table having unique key
both are dbms but why they works diferently what is the key concept behind this...
plz plz tell me the answer..
thanks ..
February 1, 2012 at 8:47 am
Null is not equal to null, however a unique constraint only allows one null. It simply means that unique constraints only allow a single null, it does not mean that nulls equal each other (they don't)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2012 at 8:52 am
Under the default settings, each time that you do any compression with null, the results will false. You can checkout this code:
if 5 = null
select 'true'
else
select 'fales'
if 5 <> null
select 'true'
else
select 'false'
if 5 > null
select 'true'
else
select 'false'
if 5 < null
select 'true'
else
select 'false'
if null = null
select 'true'
else
select 'false'
You can change this behavior by modifying the setting options (I don't recommend of doing so. I'm just showing it so you'll know). For example:
set ansi_nulls off
go
if 5 = null
select 'true'
else
select 'fales'
if 5 <> null
select 'true'
else
select 'false'
if 5 > null
select 'true'
else
select 'false'
if 5 < null
select 'true'
else
select 'false'
if null = null
select 'true'
else
select 'false'
There are few cases that are not fallowing this rule. When a column has to be unique, you can insert only one null regardless of the set options that are being used. When ever you sort by a column that has null, all the nulls will apear together at the beginning or end of the records (depending on the sorting desc or asc). Also when you use aggregations all the nulls are treated as one value.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 1, 2012 at 9:18 am
A common misconception about null lies in most people thinks: null = nothing or even null = 0.
Null sounds more "I dont know".
"null = null" sounds like "what's in my left hand? = what's in my right hand?" (if I not showing you my hands)
Well in boolean logic there's no room for "maybe" so "null = null" returns false.
When you try to insert two nulls in a unique key, well no one can guarantee null <> null or even null = null.
Try this:
select case when null=null then 1 else 0 end
select case when not(null=null) then 1 else 0 end
February 1, 2012 at 8:58 pm
CELKO (2/1/2012)
In the current ANSI/ISO standards have the IS [NOT] DISTINCT FROM theta operator, but only DB2 might have it -- I am not sure. We can useCOALESCE <expr1>, '?????') = COALESCE <expr2>, '?????')
But it is better not to have code like this.
It might be worth noting that while SQL Server doesn't have the operator, it does refer to that DISTINCT concept in a few places, specifically when grouping. To paraphrase: if NULL were not distinct from all values, it wouldn't be a separate entry when grouping (i.e. when grouping by values in a column allowing NULL). The BOL entry about this specifically points out that within that context NULL is not distinct from NULL, but is distinct from any actual value.
----------------------------------------------------------------------------------
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?
February 2, 2012 at 2:45 am
CELKO (2/1/2012)
In the current ANSI/ISO standards have the IS [NOT] DISTINCT FROM theta operator, but only DB2 might have it -- I am not sure.
Anyone wanting to vote for SQL Server to implement this can do so here:
https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate or https://connect.microsoft.com/SQLServer/feedback/details/307476/operator-for-nullable-column-comparison
We can use COALESCE <expr1>, '?????') = COALESCE <expr2>, '?????')
But it is better not to have code like this.
I much prefer:
...EXISTS (SELECT expr1 INTERSECT SELECT expr2)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply