SQL – IS NULL, IS NOT NULL

Prev     Next

Generally, NULL data represents data does not exist or missing data or unknown data.  IS NULL & IS NOT NULL in SQL is used with a WHERE clause in SELECT, UPDATE and DELETE statements/queries to validate whether column has some value or data does not exist for that column. Please note that NULL and 0 are not same. A column with NULL value has no value, it’s empty. Syntax for SQL IS NULL & IS NOT NULL are given below.

SQL Syntax for IS NULL, IS NOT NULL:

Syntax for SQL IS NULL SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 IS NULL;
Syntax for SQL IS NOT NULL SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 IS NOT NULL;

 Note:

  • IS NULL and IS NOT NULL can be used in the same SQL query in WHERE clause in any order and in any combination as per the requirement.
  • IS NULL and IS NOT NULL can also be used while creating new tables to set the property of each column. That is, whether the respective column can accept a NULL value or it must contain some value.

Please consider the following table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table25

In the above table, the Age of Raju and Thiyagarajan is NULL i.e. there are no data.

Example1: How to use IS NULL in WHERE clause in SELECT queries:

SQL query:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age IS NULL;

SQL query Output:

sql-table26

This SQL selects only the records whose Age value is NULL. The remaining records are not selected.

Example2: How to use IS NOT NULL in WHERE clause in SELECT queries:

SQL query:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age IS NOT NULL;

SQL query Output:

sql-table27

This SQL selects only the records whose Age value is not NULL. The remaining records are not selected.

Prev     Next





Share this:
Facebook Twitter Linkedin