Tuesday, January 3, 2017

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 of 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 if does contains 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 query using IS NULL and IS NOT NULL predicates.



Right way to compare values in column which 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 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 programmer 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 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 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

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 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.

Similarly to test for values which are not null, instead of using != operator use IS NOT NULL operator. For example, 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 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 query.

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