How to check for NULL values in SQL Query? The Right way Example

One of the most common SQL Interview question on Programming interview is to select some rows from a table which also contains null values. Since many SQL developers are used to using = and != operator on WHERE clause, they often tend to forget the fact that column allows NULL or not. Using = or != is perfectly fine if your column has NOT NULL constraint and you know for sure that there are no NULL values in that column, but it does contain NULLs then your query will return the incorrect result at times. This is one of the most common mistakes but at the same time hard to find SQL bugs if it managed to get into the real environment. In this article, you will learn the right way to check NULL values in SQL queries using IS NULL and IS NOT NULL predicates.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It's a great course to start with T-SQL and SQL queries in SQL Server.

The right way to compare values in a column that allows NULL

In most of the SQL interview, you will be given a table which contains both NULL and non-null values and you need to write some SQL queries to retrieve data from those tables. For example, consider the following table which just contains one column, Id, and the following values.

CREATE TABLE #test (id int)
INSERT #test VALUES(null)

Now, the question is, how many records the following query will return?

SELECT * FROM #test WHERE id != 1

Many SQL programmers will answer that it will return 2 records, which is wrong. This query will only return one record, the row with Id=2 as shown below:

SELECT * FROM #test WHERE id != 1

Why? Why the row with NULL Id was not returned? Because when you compare something with NULL the result is Unknown, not true or false. SQL uses three value logic, true, false, and unknown. See Microsoft SQL Server T-SQL Fundamentals to learn more about those.

In order to check for NULL values, you must use IS NULL or IS NOT NULL clause. For example, to include the row with Id as NULL, you can modify your SQL query like

SELECT * FROM #temp WHERE id != 1 OR id IS NULL


You can see that it returned both rows. Remember even comparing NULL to NULL doesn't work, it also returns unknown e.g. if you try to write the above query using = operator it will not work as shown below:

SELECT * FROM #temp WHERE id != 1 OR id = NULL

You can see it just return 1 row, the row with Id=NULL was not included again.

Similarly to test for values that are not null, instead of using the!= operator use IS NOT NULL operator. For example, the following query which we have written to return all the rows where Id is NOT NULL will not work because we are using != operator

(0 row(s) affected)

Instead, you should use IS NOT NULL as shown below:


That's all about the right way to check for NULL values in the WHERE clause in SQL query. Don't use = or != operator to compare values if your column allows NULLs, it may not return what you expect because comparing NULL with anything else returns Unknown. Instead, you should always use IS NULL and IS NOT NULL to check for null values in SQL queries.

How to check for NULL values in SQL Query?

Other SQL Server articles you may like:
  • How to replace NULL with empty String in SQL Server? (tutorial)
  • Difference between row_number, rank, and dense_rank in SQL Server? (answer)
  • What is the difference between WHERE and HAVING clause in SQL Server? (answer)
  • How to split String in SQL SERVER 2008? (answer)
  • The difference between char, varchar, nchar and nvarchar in SQL SERVER? (answer)
  • How to create an Identity column in SQL Server? (example)
  • 5 tips while migrating from Oracle to Microsoft SQL Server? (tips)
  • How to find the second highest salary of an employee in SQL Server? (query)
  • How to join more than two tables in one SQL query? (solution)
  • How to find duplicate records from a table? (solution)
  • How to find the length of String in MSSQL? (example)
  • 5 Web sites to learn SQL online for FREE? (resource)
  • 5 Books to Learn SQL? (books)

No comments :

Post a Comment