Using Null in an expression always returns null in SQL !!!

13. March 2011 08:38 by Mahmoud Farhat in SQL  //  Tags:   //   Comments (0)

Please check the below table and just remember that NULL means “unknown”

 

The expressions in this list always return NULL:

 

If A and B are NULL, then:

Is:

Because:

1 + 2 + 3 + A

NULL

If A is unknown, then 6 + A is also unknown.

'Home ' || 'sweet ' || A

NULL

If A is unknown, 'Home sweet ' || A is unknown.

MyField = A

NULL

If A is unknown, you can't tell if MyField has the same value...

MyField <> A

NULL

...but you also can't tell if MyField has a different value!

A = B

NULL

With A and B unknown, it's impossible to know if they are equal.

not (A)

NULL

If A is unknown, its inverse is also unknown.


The above table will help you at many cases that you want to use null so we will take a sample to make this list very easy and clear for you, like If you are looking to create SQL query for using it in search to get data from employee table according to some search criteria


Employee Table:
 

ID

EmployeeName

1

Karim Hasan

2

Karim

3

Ola Ali

 

 1- This query return all rows just if the name is equal "Karim"

 

Set @name = 'Karim'
select
 from Employee where EmployeeName = @name

Result
--------------------------
ID            EmployeeName
2              Karim


2- This query return all rows contains name "Karim"

 

Set @name = 'karim'
select
 from Employee where EmployeeName like '%' + @name + '%'

Result
--------------------------
ID            EmployeeName
1              Karim Hasan
2              Karim
 

The above samples are not my point there are jsust initiative my point is, if you want once the user pressed on the search button in you application without enter any data like "Employee Name" then return all the employees from the employee table and this means you are going to set @name parameter with NULL value (Set @name = null), how can we do that??

 

Honestly there are many ways to do that and I will list two samples one of them is the best at latest for me depend in NULL expression and I prefer to use it in my work.

 

Sample 1:

 

Declare @name varchar(20)
Declare
@Sql varchar(1000)

set @name = 'Karim'
select
@Sql ='select * from Employee where 1 = 1'

If (@name is not null)
Begin

     select @Sql = @Sql + ' and EmployeeName like ''%' + @name + '%'''
End
exec
(@Sql)


Sample 2:

Declare @name varchar(20)

set
@name = 'Karim'
select
* from Employee where EmployeeName = IsNull ( '%' + @name + '%', EmployeeName)

when you take a look in this statement at the first time "IsNull ( '%' + @name + '%', EmployeeName)" you will say how isnull function will return null without to check in this char '%', but as we list before in the above table that if you are using Null in an expression returns null

 
Select  1 +2
Result: 3
 
Select 1+Null
Result: Null
 
Select  '%' + Null
Result: Null
 

I prefer the sample 2 by using NULL expression because it is very easy and as you see we don't need to write many lines of code to write quires for search.

Good luck, and I hope this information is helpful to you and Please feel free to write your comment.