How to check for Null in SQL Query? IS NULL Example Tutorial

One of the most common SQL Interview questions on Programming interviews is to select some rows from a table that 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 SQL 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 interviews, you will be given a table that 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(1)
INSERT #test VALUES(2)
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
id
2

Why? Why was 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. You can further see these best SQL and Database courses for beginners to learn more about them.


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

Output
id
2
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
id
2

You can see it just return 1 row, the row with Id=NULL was not included again. Always remember null != null in SQL whether it's Oracle, MySQL, PostgreSQL, or Microsoft SQL Server. 

How to test for not null values in SQL? IS NOT NULL Example

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

SELECT * FROM #temp WHERE id != NULL
(0 row(s) affected)

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

SELECT * FROM #temp WHERE id IS NOT NULL
id
1
2

That's all about the right way to check for NULL values in the WHERE clause in the 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.


Other SQL Server articles and resources you may like:

• 5 Web sites to learn SQL online for FREE? (resource)
• 5 Books to Learn SQL? (books)
• How to find duplicate records from a table? (solution)
• How to join more than two tables in one SQL query? (solution)
• How to find the length of String in MSSQL? (example)
• 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? (answer)
• 50 SQL Server Phone Interview Question (SQL server questions)
• 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)

Thanks a lot for reading this article so far. If you like this SQL Null Tutorial then please share with your friends and colleague.s If you have any questions or feedback then please drop a note. 

P. S. - If you are new to SQL and want to learn SQL and database concepts from start and looking for free online courses then you can also check out these free SQL and database courses to start your SQL journey. They are the best free courses to learn SQL from Udemy and Coursera.