{"id":305,"date":"2016-03-28T07:37:41","date_gmt":"2016-03-28T07:37:41","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=305"},"modified":"2025-02-07T20:36:21","modified_gmt":"2025-02-08T03:36:21","slug":"sql-is-null","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-is-null\/","title":{"rendered":"SQL IS NULL Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you learn about the NULL<\/code> and how to use the SQL\u00a0 IS NULL<\/code> operator to test if a value is NULL<\/code> or not.<\/p>\n\n\n\n

Introduction to NULL in databases #<\/a><\/h2>\n\n\n\n

In the database world, NULL<\/code> is a marker that indicates unknown or missing data in the database.<\/p>\n\n\n\n

For example, if you don’t know the phone numbers of employees when you save the employee’s records, you can use NULL<\/code> to represent unknown phone numbers.<\/p>\n\n\n\n

The NULL<\/code> is special because you cannot compare it with any value even with the NULL<\/code> itself.<\/p>\n\n\n\n

The following statement compares NULL<\/code> with the number 5<\/code> and results in NULL<\/code>:<\/p>\n\n\n

SELECT<\/span>\n  NULL<\/span> = 5<\/span> AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NULL<\/code> is not equal to itself, as shown in the following statement:<\/p>\n\n\n
SELECT<\/span>\n  NULL<\/span> = NULL<\/span> AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You cannot use the equal to (=<\/code>) to check if a value is NULL<\/code> or not.<\/p>\n\n\n\n
For example, the following statement attempts to find employees who do not have phone numbers:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  phone_number\nFROM<\/span>\n  employees\nWHERE<\/span>\n  phone_number = NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name | phone_number\n------------+-----------+--------------<\/span>\n(0 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IS NULL operator #<\/a><\/h2>\n\n\n\n
To test if a value is NULL<\/code> or not, you use the IS NULL<\/code> operator:<\/p>\n\n\n
expression IS NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IS NULL<\/code> operator returns true<\/code> if the result of the expression is NULL<\/code>. Otherwise, it returns false<\/code>.<\/p>\n\n\n\n
The IS NOT NULL<\/code> negates the result of the IS NULL<\/code> operator:<\/p>\n\n\n
expression IS NOT NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IS NOT NULL<\/code> returns false<\/code> if the expression is NULL<\/code> or true<\/code> otherwise.<\/p>\n\n\n\n
SQL IS NULL operator example #<\/a><\/h3>\n\n\n\n
We’ll use the  employees<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"employees_table\"<\/figure>\n\n\n\n
The following query uses the IS NULL<\/code> operator to find the employees who do not have phone numbers:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  phone_number\nFROM<\/span>\n  employees\nWHERE<\/span>\n  phone_number IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 first_name | last_name  | phone_number\n------------+------------+--------------<\/span>\n John       | Russell    | NULL\n Karen      | Partners   | NULL\n Jonathon   | Taylor     | NULL\n Jack       | Livingston | NULL\n Kimberely  | Grant<\/span>      | NULL<\/span>\n Charles    | Johnson    | NULL<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find all employees who have phone numbers, you use IS NOT NULL<\/code> operator as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  phone_number\nFROM<\/span>\n  employees\nWHERE<\/span>\n  phone_number IS<\/span> NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  | phone_number\n-------------+-------------+--------------<\/span>\n Steven      | King        | 515.123.4567\n Neena       | Kochhar     | 515.123.4568\n Lex         | De Haan     | 515.123.4569\n Alexander   | Hunold      | 590.423.4567\n Bruce       | Ernst       | 590.423.4568\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n