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 website !!!
Facebook Twitter Linkedin

1. Online Compilers - Compile & Run your C/C++ programs:

✅ Online Compilers

2. Ask your questions or clarify your/others doubts from,

✅ Online Discussion Forums by Fresh2Refresh.com





Close
We Support JALLIKATTU !!!
We Support JALLIKATTU!
By Fresh2Refresh.com
Show Your Support as well. Thanks!