How to Use NULL Values Inside NOT IN Clause in SQL?
Last Updated :
23 Dec, 2024
In SQL, NULL
holds a special status as it represents the absence of a value, making it fundamentally different from regular values. Unlike numbers or strings, NULL
cannot be directly compared using operators like =
or !=
. This special status often leads to unexpected behavior in SQL queries, especially when filtering data. The NOT IN
clause, commonly used for filtering data, can encounter challenges when NULL
values are present.
A NULL
in the data or comparison list can cause an entire query to return incorrect or empty results. In this article, we will explain how to effectively handle NULL
values in the NOT IN
clause, ensuring accurate query results and avoiding unexpected behavior.
How to Use NULL Values Inside NOT IN Clause
To demonstrate how to handle NULL
values in the NOT IN
clause, let’s create a table and populate it with sample data. This will help us understand the challenges and solutions in a practical context.
Query:
CREATE TABLE demo_table(
NAME VARCHAR(20),
GENDER VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );
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');
SELECT * FROM demo_table;
Output

demo_table
Example 1: Excluding Rows with NULL
Values Using NOT IN
To correctly handle NULL
values in a NOT IN
clause, we can exclude them explicitly. This ensures that only non-NULL
values are considered in the comparison. Explicitly filtering out NULL
values avoids invalidating the entire NOT IN
condition. This approach is essential to ensure the query yields accurate and expected results.
Query:
SELECT * FROM demo_table WHERE AGE NOT IN (SELECT AGE WHERE AGE IS NULL);
Output

Excluding-Rows-with-NULL-Values-Using-NOT-IN
Explanation:
- The subquery selects
AGE
values that are NULL
.
- By using
NOT IN
, the main query excludes rows where AGE
matches the NULL
values explicitly filtered out.
- This ensures that only rows with non-
NULL
AGE
values are included.
Example 2: Challenges with NULL
in NOT IN
Clause
The NOT IN
clause evaluates each value in the comparison list against the target column. If the comparison list includes NULL
, the entire condition becomes invalid, returning no results.
Query:
SELECT * FROM demo_table WHERE AGE NOT IN ((SELECT AGE WHERE AGE IS NULL),24);
Output

Explanation:
- The subquery includes
NULL
values.
- When
NULL
is part of a NOT IN
clause, all comparisons involving it return NULL
, invalidating the condition and resulting in no rows being returned.
Example 3: Excluding Additional Specific Values Along with NULL
To exclude additional specific values along with NULL
, we can use multiple NOT IN
clauses combined with the AND
operator.
Query:
SELECT * FROM demo_table
WHERE AGE NOT IN (SELECT AGE WHERE AGE IS NULL)
AND AGE NOT IN (24);
Output
NAME |
GENDER |
AGE |
CITY |
RINKLE ARORA |
FEMALE |
23 |
PUNJAB |
NIKHIL KALRA |
MALE |
23 |
PUNJAB |
SHALINI JHA |
FEMALE |
22 |
DELHI |
Explanation:
- The first
NOT IN
clause excludes rows where AGE
is NULL
.
- The second
NOT IN
clause explicitly excludes rows where AGE = 24
.
- Combining these conditions ensures precise filtering, returning only rows with
AGE
values that are not NULL
or 24
.
Conclusion
Handling NULL
values in SQL requires careful attention, especially when using the NOT IN
clause. Since NULL
cannot be directly compared, it’s crucial to explicitly exclude or account for it in our queries. By using subqueries and adding separate conditions for NULL
, we can avoid common pitfalls and achieve accurate results. Mastering this technique ensures our SQL queries handle missing data effectively, leading to reliable outcomes in our database operations.
Similar Reads
How to Insert Rows with NULL Values in SQL?
In SQL, due to lack of data, we sometimes need to insert rows with NULL values in the tables. Here, the keyword NULL(without quotes) is used to represent no data. There are some key points of Null value: NULL value is different from a zero value.A NULL value is used to represent a missing value, but
2 min read
How to Update NULL Values in a Field in MySQL
There is a situation where we need to update certain columns with NULL values in a MySQL database, you're in the right place. It's a common task when you're working with databases and dealing with incomplete or undefined data. In this article, we'll walk through the process, break down the syntax, a
3 min read
How to Set a Column Value to Null in SQL?
You can set a column value to NULL using the SQL UPDATE statement. Through the UPDATE statement, existing records in a table can be changed. The fundamental syntax to set a column value to NULL is as follows. Syntax: UPDATE table_name set column_name=NULL WHERE Conditions; table_name: The name of th
2 min read
How to Set a Column Value to Null in PL/SQL?
In PL/SQL, setting a column value to NULL is a common requirement when working with databases. Understanding how to set column values to NULL is essential for database developers and administrators. In this article, we will look into the concept of setting a column value to NULL in PL/SQL, covering
4 min read
How to use Is Not Null in PySpark
In data processing, handling null values is a crucial task to ensure the accuracy and reliability of the analysis. PySpark, the Python API for Apache Spark, provides powerful methods to handle null values efficiently. In this article, we will go through how to use the isNotNull method in PySpark to
4 min read
How to Set a Column Value to NULL in SQLite?
SQLite is a lightweight and self-contained relational database management system in short RDBMS. Its has a server-less architecture which makes it a better option for small desktop and mobile applications. It also requires very low configuration which eventually helps the developer to integrate it i
4 min read
How to Set a Column Value to NULL in MariaDB
In MariaDB, the NULL represents an unknown value in a column. Changing a column value to NULL is the most common operation performed in MariaDB that allows us to remove existing data in a specific field. It is applicable in different ways including data correction, record inclusions and values setti
4 min read
How to Set a Column Value to NULL in SQL Server
In the world of database management, SQL Server is a leading and extensively utilized system. A fundamental task within SQL Server is manipulating data within tables, and setting a column value to NULL is a common operation. Whether it's for maintaining data integrity, performing updates, or meeting
4 min read
How to Filter Using 'in' and 'not in' Like in SQL in Polars
Python Polars, a fast and versatile DataFrame library in Rust with bindings for Python, offers efficient data manipulation capabilities. For those transitioning from SQL or those who need to perform complex data manipulations, Polars provides a familiar and powerful set of tools that mimic SQL-like
3 min read
How to Use âNOT INâ Filter in Pandas?
The "NOT IN"(â¼) filter is a membership operator used to check whether the data is present in DataFrame or not. Pandas library does not have the direct NOT IN filter in Python, but we can perform the NOT IN filter by negating the isin() operator of Pandas. In this tutorial, we will provide a step-by
3 min read