SQL IS NULL
SQL IS NULL is a logical operator that checks for NULL values in a column.
IS NULL Operator in SQL
IS NULL Operator in SQL is used to test for empty or missing values in a column. It checks if the specified expression evaluates to NULL, if it evaluates to NULL, it returns TRUE; otherwise, it returns FALSE.
It can be used with the WHERE clause to filter rows where a specified column has a NULL value.
Syntax
SQL IS NULL syntax is:
SELECT * FROM table_name
WHERE column_name IS NULL;
Note: A NULL value is different from a Zero Value and Blank Spaces. A field that has NULL value means the field was left blank.
SQL IS NULL Examples
Let’s look at some examples of the IS NULL operator in SQL. These examples will help in understanding the working of IS NULL in SQL.
First, we will create a demo SQL database and table, on which we will use the IS NULL operator.
Demo SQL Database
We will use this demo table an example.
| user_id | name | problems_solved | coding_score | |
|---|---|---|---|---|
| 101 | Vishu | 20 | 100 | example1@gamil.com |
| 102 | Sumit | 19 | 99 | |
| 103 | Neeraj | 18 | 98 | example2@gamil.com |
| 104 | Aayush | 17 | 97 | |
| 105 | Harsh | 16 | example3@gamil.com | |
| 106 | Rahul | 15 | example4@gamil.com | |
| 107 | Vivek | 14 | 90 |
To create this table on your system, write the following query:
CREATE TABLE w3wiki(
user_id int PRIMARY KEY,
name varchar(100),
problems_solved int,
coding_score int,
email varchar(100)
);
INSERT INTO w3wiki (user_id, name, problems_solved, coding_score, email)
VALUES
(101, 'Vishu', 20, 100, 'example1@gamil.com'),
(102, 'Sumit', 19, 99, NULL),
(103, 'Neeraj', 18, 98, 'example2@gamil.com'),
(104, 'Aayush', 17, 97, NULL),
(105, 'Harsh', 16, NULL, 'example3@gamil.com'),
(106, 'Rahul', 15, NULL, 'example4@gamil.com'),
(107, 'Vivek', 14, 90, NULL);
SELECT* FROM w3wiki;
IS NULL with WHERE clause Example
In this example, we use the IS NULL operator with WHERE clause to filter data from the table.
Query:
SELECT * FROM w3wiki
WHERE email IS NULL;
Output:
IS NULL with WHERE clause
IS NULL Operator on Multiple Columns Example
In this example, we will use the SQL IS NULL operator on multiple fields using the OR operator.
Query:
SELECT * FROM w3wiki
WHERE email IS NULL or coding_score IS NULL;
Output:
IS NULL Operator on Multiple Columns
IS NULL with COUNT() Function Example
In this example, we will use IS NULL with COUNT() function to get the total number of NULL values in a column.
SELECT count(*) as count_empty_coding_score FROM w3wiki
WHERE coding_score IS NULL;
Output:
IS NULL with COUNT() Function
We can clearly see in our table that we have 2 rows that have NULL values in their coding score column i.e. user_id: 105,106.
IS NULL with UPDATE Statement Example
In this example, we will update NULL values with some default values using IS NULL operator and UPDATE statement.
Query:
UPDATE w3wiki
SET email = 'default@gmail.com'
WHERE email IS NULL;
Output:
IS NULL with UPDATE Statement
As we can see user_id’s: 102, 104, and 107 previously contain NULL values in their emails column but now they have a default value i.e. “default@gmail.com”.
IS NULL with DELETE Statement Example
In this example, we will delete all NULL values with the help of SQL’s DELETE statement.
Query:
DELETE FROM w3wiki
WHERE coding_score IS NULL;
Output:
IS NULL with the DELETE Statement
We can notice clearly that all those rows deleted have a null value in their coding score column.
Important Points About SQL IS NULL
- SQl IS NULL is used to detect any rows that contain a NULL value in its column.
- IS NULL operator is mostly used with WHERE clause in SQL.
- We can use IS NULL operator on multiple columns using OR operator.
- Using COUNT function we can count total number of NULL values in SQL.
- We can UPDATE or DELETE the NULL values, after filtering them with IS NULL operator.