How to Use NULL Values Inside NOT IN Clause in SQL?

In this article, we will see how to use NULL values inside NOT IN Clause in SQL. NULL has a special status in SQL. It represents the absence of value so, it cannot be used for comparison. If you use it for comparison, it will always return NULL. 

In order to use NULL value in NOT IN Clause, we can make a separate subquery to include NULL values.

Make a separate where clause for NULL like:

Query:

WHERE value IS NULL;

Step 1: Creating the database

Use the below SQL statement to create database called geeks;

Query:

CREATE DATABASE geeks;

Step 2: Using the database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table creation

We have the following demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
NAME VARCHAR(20),
GENDER VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );

Step 4: Insert data into a table

Query:

INSERT INTO demo_table VALUES
('ROMY KUMARI', 'FEMALE', NULL, 'NEW DELHI'),
('PUSHKAR JHA', 'MALE',24, 'NEW DELHI'),
('RINKLE ARORA', 'FEMALE',23, 'PUNJAB'),
('AKASH GUPTA', 'MALE', NULL, 'UTTAR PRADESH'),
('NIKHIL KALRA', 'MALE', 23, 'PUNJAB'),
('SHALINI JHA','FEMALE', 22, 'DELHI');

Step 5: View data of the table

Query:

SELECT * FROM demo_table;

Output:

Step 6: Use of NULL values in NOT NULL

For the demonstration, we will return the values from the table whose AGE is not NULL values.

Query:

SELECT * FROM demo_table WHERE AGE NOT IN (SELECT AGE WHERE AGE IS NULL);

Output:

If you want to add values in the NOT IN clause, define separate NOT IN clause with AND operator. If you add the value within the same NOT IN clause, it will return NULL.

Query:

SELECT * FROM demo_table WHERE AGE NOT IN ((SELECT AGE WHERE AGE IS NULL),24);

Output:

Returned empty table.

The correct way to add other values in NOT IN Clause with NULL values:

Query:

SELECT * FROM demo_table WHERE AGE NOT IN (SELECT AGE WHERE AGE IS NULL) and AGE NOT IN (24);

Output: