SQL NULL Values

By | September 13, 2020
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.

Vamware

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.

Leave a Reply

Your email address will not be published. Required fields are marked *