SQL NULL Values

    The NULL value represents no value or black value. For instance, suppose there is a table which stores students first name and last name, if any student does not have any last name then the last name column for that student will remain blank and that blank cell in the SQL table will be represented by a NULL value. Note: Do Not confuse NULL value with zero or white space.

    SQL NULL Values

    Comparison Operators on NULL values

    Comparison operators such as < , > or = can not operate or can not check the condition for a NULL value, so to check for those records which hold a NULL value we have to use the SQL IS NULL and IS NOT NULL operators.

    IS NULL Operator

    The IS NULL operator checks if the cell data is a NULL value. IS NULL Syntax 

    SELECT column_names
FROM table_name
WHERE column_name IS NULL;

    IS NOT NULL Operator

    IS NOT NULL operator checks if the value is not-empty. IS NOT NULL Syntax 

    SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

    Example 

    +------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

    Query: Show those students ID and names whose age is not specified in the student table 

    SELECT id, name
FROM students
WHERE age IS NULL;

    Output 

    +------+-------+
| id   | name  |
+------+-------+
|    6 | Robin |
+------+-------+

    Query: Show those students ID and names whose age is specified in the student table 

    SELECT id, name
FROM students
WHERE age IS NOT NULL;

    Output 

    +------+--------+
| id   | name   |
+------+--------+
|    1 | Luffy  |
|    2 | Naruto |
|    3 | Zoro   |
|    4 | Sanji  |
|    5 | Nami   |
|    7 | Goku   |
+------+--------+

    Summary

    • The NULL value represents no value.
    • Comparison operators do not operate on a NULL value.
    • IS NULL check if the value is NULL
    • IS NOT NULL operator check cell for the non-empty data.

    People are also reading:

    Previous Tutorial Next Tutorial