How to Use NULL Values Inside NOT IN Clause in SQL?
Last Updated :
15 Oct, 2021
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:
Share your thoughts in the comments
Please Login to comment...