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.
Syntax for IS NULL, IS NOT NULL in SQL :
| 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
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:
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:
This SQL selects only the records whose Age value is not NULL. The remaining records are not selected.