You are here

SQL: IS OPERATOR

SQL: IS operator

SQL: IS OPERATOR

In this post we will learn IS operator it is use with NULL and NOT NULL operator to check whether a value is null or not.

  • In database NULL is unknown or missing value, therefor we can not use the <, > ,= operators to check value is NULL or not.
  • SQL provides the IS operator to check NULL in data.

Syntax:
Where col_name IS (NOT) NULL

  • We can use IS operator in where clause of select, update and subquery.
  • It returns true if column has null value, otherwise return false.
  • If we use NOT operator then it returns true if the column has not NULL value, otherwise return false.
  • NOT operator is use used to inverse the result.
  • For checking NULL we can also use ISNULL(), COALESCE() inbuilt functions.

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 that do not have email id:
select name, email from tbltest where email IS NULL;
output:

name

email
Priya

NULL

Siya

NULL

Retrieve the name that do not have email id:
select name, email from tbltest where email IS NOT NULL;
output:

name

email

Riya

riya1234@gmail.com

Retrieve the name whose email id is null and phone is not null
select id, name, email, phone from tbltest where email IS NULL AND phone IS NOT NULL;
output:

id

name email phone
2 Priya NULL

12334

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 Kotlin Book write by me from Amazon.

Leave a Reply

Top