How to Select NULL Values in SQL Database: Standard SQL Operators: IS NULL IS NOT NULL Table of Contents Problem: Example: Solution: Discussion: Problem: You want to select rows with the NULL value in a given column. Example: Let's see a table named Employees with the following columns: EmployeeID, EmployeeName, Dept, and Salary. Assume the Salary column allows NULL values. EmployeeIDEmployeeNameDeptSalary 1Jack RusselManager5600 2Jan KowalskiHRnull 3John DoeHR4500 4Mark RusselSales9000 5Jane DoeSalesnull Now, let’s retrieve all records where the Salary column contains NULL values. Solution: To select rows with NULL in a given column, use a special operator IS NULL: SELECT EmployeeID, EmployeeName, Salary FROM Employees WHERE Salary IS NULL; Here’s the result of the query: EmployeeIDEmployeeNameSalary 2Jan Kowalskinull 5Jane Doenull Discussion: NULL in SQL is used to indicate a missing or unknown value. NULL is special: it is different from all other values, including zero and an empty text field. To test for NULL values specifically in SQL, you must use a special operator IS NULL. It is impossible to test for the NULL value using any of the usual comparison (such as = or <>) operators. A comparison with NULL using a regular comparison operator (=, <, <=, >, >=, or <>) always returns NULL, rather than TRUE or FALSE. For example, this query returns no rows: SELECT EmployeeID, EmployeeName, Salary FROM Employees WHERE Salary = NULL; While this query: SELECT EmployeeID, EmployeeName, Salary FROM Employees WHERE Salary > 0; returns only the rows with non-NULL positive salaries. The employees with NULL salaries are omitted in the result: EmployeeIDEmployeeNameSalary 1Jack Russel5600 3John Doe4500 4Mark Russel9000 SQL also has a similar operator for checking for non-NULL values: IS NOT NULL. Let’s now do the opposite of the example above and use this operator to select employees whose Salary column contains a non-NULL value. SELECT EmployeeID, EmployeeName, Salary FROM Employees WHERE Salary IS NOT NULL; This is the result of the query: EmployeeIDEmployeeNameSalary 1Jack Russel5600 3John Doe4500 4Mark Russel9000 Recommended courses: SQL Basics Standard SQL Functions Recommended articles: Standard SQL Functions Cheat Sheet What Is a NULL in SQL? How to Use Comparison Operators with NULLs in SQL See also: How to Find Records with NULL in a Column How to Filter Rows without NULL in a column Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query