comparision of null values in sql

[trysenthil]

hi all how can we compare two null values in SQL...

for example.,

select ename,commission from emp e
where commission=(select commission from emp where ename=e.ename)

suppose commission of the employee is null it is not comparing the null value... it is omitting the record which has null value in commision....
this problem is too complex when we check for multiple queries......

request: i have also tried with commission is NULL but note that i want to compare the null values..

[debasisdas]

Since null is not a value , u canst use any of the comparision operators on NULL.

[gintsp]

hi all how can we compare two null values in SQL...

for example.,

select ename,commission from emp e
where commission=(select commission from emp where ename=e.ename)

suppose commission of the employee is null it is not comparing the null value... it is omitting the record which has null value in commision....
this problem is too complex when we check for multiple queries......

request: i have also tried with commission is NULL but note that i want to compare the null values..

It is not clear what you want. If you wnat just rows where commision is null the write as follows:

select ename,commission from emp e
where commission IS NULL

If you want to compare two values with a requirement that if both values are null, then accept them as equivalent, then you can use
nvl(value1, <not_existing_value>) = nvl(value2, <not_existing_value>)
This compare has danger that probably sometimes <not_existing_value> will actually be in your data and the the absolutely secure way is:
where value1 = value2 or value1 is null and value 2 is null

Gints Plivna
http://www.gplivna.eu