You are here

Handling NULL values in SQL

Nulls in SQL server

Handling NULL values in SQL

In this post we learn how to handle NULLs in SQL server. for handling NULLs in SQL server we use ISNULL(), NULLIF(), COALESCE() functions with example.

ISNULL():

  • The ISNULL() function is commonly used function that return first value that is not null among their input.

Syntax:
ISNULL(col_name, replacement_value)

  • The ISNULL() function only support two arguments. First argument is the column name for which we check NULL value, and second argument is the replacement value.
  • The value of column name return if it is not NULL, otherwise replacement value is returned after it is implicitly converted, if different datatype.
  • The ISNULL() function is a proprietary T-SQL feature.

NULLIF():

  • The NULLIF() function is used to checked whether both column have NULL value or not.

Syntax:
NULLIF(expression1, expression2)

  • This function return NULL value if the expressions are equal. NULLIF() returns the first expression if the two expression are not equal. If the expression equal it return NULL value of type of the first expression.
  • NULLIF() function is equivalent to SELECT CASE statement.

COALESCE():

  • The COALESCE() function returns the first not-NULL value among its paramenter.
  • It has no limit for number of argument but they must all be of the same data type.

Syntax:
COALESCE(col1,col2,.. )

  • The COALESCE() function is defined by the ISO/ANSI SQL standard.

Example:

create table
create table tbltest(id int NOT NULL, name varchar(50) NOT NULL, email varchar(50) NULL, phone varchar(50) NULL, favcolor varchar(50));
–insert record into table
insert into tbltest(id,name, email, phone, favcolor)
values(1, ‘Riya’, ‘riya1234@gmail.com’, NULL, ‘Blue’), (2, ‘Priya’, NULL, 12334, ‘Red’), (3, ‘Siya’, NULL, NULL, ‘Green’);
–retrieve data
select * from tbltest;
output:

Id Name email Phone favcolor
1 Riya Riya1234@gmail.com NULL Blue
2 Priya NULL 12334 Red
3 Siya NULL NULL Green

Retrieve the name if they have email id

select id, ISNULL(email, ‘xyz@gmairrl.com’)as email from tbltest;

output:

Id

email

1

riya1234@gmail.com

2

xyz@gmail.com

3

xyz@gmail.com

Retrieve the name and color
select [name], favcolor, NULLIF(favcolor, ‘Blue’)as color from tbltest;
output:

name

favcolor color
Riya Blue

NULL

Priya

Red Red

Siya

Green

Green

Retrieve the name and use primary contact if email is available and if not then use phone number
select name, COALESCE(email, phone)as contact from tbltest;
output:

name contact
Riya riya1234@gmail.com

Priya

12334
Siya

NULL

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top