What is NULL in SQL? How to use Nulls in Query? Example Tutorial

Hello guys, NULL is a very important concept in SQL as it indicate absence of values. A column can be Nullable or not Nullable, which means it can either contains null values or not. For a column which contains optional values like Car in Person table, you can use Null to indicate a person doesn't have car. 

While this part is easy, the real trouble comes when you need to query and deal with values which may have nulls. For an SQL developer it's important to find all rows where a certain column is Null, now how to compare Null as simply checking with = or != is not enough. 

This article will describe the complications that might arise when working with SQL NULL values, as well as some tips for resolving them.

What are NULL in SQL? 

The definition of a NULL is one of the most difficult things to grasp while studying SQL. Programming languages have traditionally had no idea of missing or unknown data. Most programmers were familiar with the 'not applicable' flags in spreadsheets, or the traditional TBD flags in printouts for uncertain teachers, places, or other items.

A NULL value in the relational database model denotes an unknown value. If we broaden this theoretical explanation, the NULL value denotes an unknown value, which is not the same as a zero value or a field containing spaces.

 
Because of the nature of NULL values, standard comparison operators (=, >, and >) cannot be used in queries. In reality, employing the WHERE clause as seen below will produce empty result sets according to SQL Standards.

When attempting to compare a NULL to something, including another NULL, SQL's three-value logic TRUE, FALSE, UNKNOWN is well-known. NULL > NULL is UNKNOWN since it is not a value, but NULL = NULL is as well! 

To check for a NULL, we use the predicate phrase> IS [NOT] NULL. This predicate, along with the EXISTS() method, is one of the few in SQL that solely returns TRUE or FALSE.

IS NULL condition in SQL

The IS NULL condition returns rows that contain NULL values in a column and has the following syntax:

SELECT * FROM table_name 
WHERE column_name IS NULL;

This query returns all the entries of a table that has a particular column value is NULL. Now, Let's
try to recreate this with the help of an example. And for that, we will need to set up a table. Let's
create a sample Employees table with the following query with the result as shown below :

CREATE TABLE Employees(EmployeeID INT(50) PRIMARY KEY, LastName varchar(50), FirstName varchar(50), BirthDate Date, Photo Blob, Notes varchar(256));  Now, you guys can enter any values that you want. Let's say, enter 10 values into the table. But, let's say we insert around 3 values for the Notes column as NULL. This would look something like below : Note: I have shown only 5 records, you guys can try out with any number of records as you please. Now, continuing our IS NULL query that we discussed previously, let's write a similar query for the same. SELECT * FROM [Employees] WHERE Notes IS NULL; The result of this would be as expected, the first 3 values from the above picture. Similarly, the IS NOT NULL condition is used to return rows in which a column has non-NULL values. The following query will return rows from the Person database where the value of the Notes column is not NULL. The result of that returns all the rows except the top 3 ones in our example. Handling SQL NULL values using functions  SQL Server has a few functions that can assist you to deal with NULL values. ISNULL(): The ISNULL() function accepts two arguments and allows us to replace NULL values with a given value. ISNULL(expression, replacement) The expression argument specifies the expression against which NULL values should be checked. The value to replace the NULL values is specified by the replacement argument. The ISNULL() function, for example, substitutes NULL values in the row with the supplied value in the following query. SELECT ISNULL(Notes, "New Note") AS NEW_NOTES FROM Employees; The result would be something like below. Note that I have shown the whole table for better understanding :  COALESCE() returns the first non-null expression in a list with an unlimited number of parameters. COALESCE(value1, value2,...,valueN); Let's try it out with an example then. The BMW is returned by the COALESCE() function in the following query because it is the first non-null result in the list. COALESCE(NULL, NULL, BMW, AUDI, NULL); Difference between IS NULL and = NULL When two expressions' values are compared, the equal (=) operator is employed, and if the compared values are equal, the comparison result is true. When we try to locate the rows that have null values using the equal operator, however, the result set returns nothing. The following query, for example, will not produce any results. The origin of this problem is due to the structure of the SQL NULL, which does not indicate an explicit value, resulting in a misleading result when comparing an unknown value to an accurate value. Any comparison or computation done with NULL values must result in NULL, according to ANSI (American National Standard Institute) guidelines, as we just said. The SET ANSI NULLS command, on the other hand, may be used to disable this behavior pattern.  The equals (=) and not equal (>) comparison operators are not applicable to the ANSI-SQL standard by default, but we may deactivate this option at the session level. Conclusion The plain reality is that NULLs cannot be avoided in SQL. Consider them, in the same manner, you  would any other data design option. Is it really necessary? Is it sufficient to state the model's nature?  Is it simple enough for someone who will have to maintain it after you're gone to understand?