NULL in SQL

NULL in SQL is a very interesting creature. First of all, it is important to understand that NULL is not a value, so the expression "null value" is incorrect. NULL is a mark for a missing value but it is not a value itself.

Let me illustrate this with some examples created in SQL Server.

Variable Example

DECLARE @x varchar(30);
DECLARE @y varchar(30);
SET @x = NULL;
SET @y = NULL;
SELECT
	CASE
		WHEN @x = @y THEN 'True'
		WHEN @x <> @y THEN 'False'
		ELSE 'Unknown'
	END AS [answer]

The answer is 'Unknown'. Therefore, we can see SQL basic characteristics.

SQL is based on mathematical predicate logic; however, the logic is modified. In math, there are only two possible values for predicates: true or false. However, in SQL, three-valued predicate logic is available (true, false, or unknown). Unknown is a result of a fact that it is not possible to compare whether a missing value is equal to another missing value.

This script will give exactly the same result if you change this line:

SET @y = 'Kanpak';

Again, it is because SQL cannot distinguish if a missing value is equal to a known value.

Table Example

We've got a few operators to consider.

= Operator

Let’s create a sample table to show this example.

IF OBJECT_ID(N'dbo.Example', N'U') IS NOT NULL
	DROP TABLE dbo.Example;
GO

IF OBJECT_ID('dbo.ExampleSeq', N'SO') IS NOT NULL
	DROP SEQUENCE dbo.ExampleSeq;
GO

CREATE TABLE dbo.Example(
	id int NOT NULL
	,name nvarchar(20) NULL
	,department nvarchar(20) NULL
);
GO

CREATE SEQUENCE dbo.ExampleSeq
	AS bigint 
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1;
GO

ALTER TABLE dbo.Example
ADD CONSTRAINT pc_dbo_Example PRIMARY KEY(id)
GO

ALTER TABLE dbo.Example
ADD CONSTRAINT dc_dbo_Example_name DEFAULT (NEXT VALUE FOR dbo.ExampleSeq) FOR id
GO

INSERT INTO dbo.Example(name, department)
VALUES ('John', 'HR')
	,('Adam', 'Finance')
	,('Mateo', NULL)
	,('Kate', NULL)
	,('Sebastian', 'HR');
GO

Table:

First of all, if we will try to query all NULL s with this query:

SELECT id, department, name
FROM dbo.Example
WHERE department = NULL

...then there will be an empty result because, as shown before, = NULL is giving the Unknown outcome.

Exactly the same will be shown if we try:

WHERE department <> NULL

Naturally, if you want to see NULL, you should remember to use the expression IS NULL:

SELECT id, department, name
FROM dbo.Example
WHERE department IS NULL

Result:

IN Operator

OK, so let’s try to use the IN operator instead.

SELECT id, department, name
FROM dbo.Example
WHERE department IN ('HR', NULL)

But the IN operator does not return a match with NULL because it is based on the = operator. You can write the same with =:

SELECT id, department, name
FROM dbo.Example
WHERE department = 'HR' OR department = NULL

So the result is only people from HR:

NOT IN Operator

On the other hand, let’s take a look at NOT IN:

SELECT id, department, name
FROM dbo.Example
WHERE department NOT IN ('HR', NULL)

The result is empty because at least one value is empty. It happens because you are trying to query “Department” column, which doesn’t equal Unknown (<>).

The same result would appear if you would wrote:

SELECT id, department, name
FROM dbo.Example
WHERE department <> 'HR' AND department <> NULL

Normally, if you have a subquery, you should always think about choosing NOT EXISTS over NOT IN. More on this can be found here.

Note: It is important to remember that. this example was shown with SET ANSI_NULLS ON. If you will switch it off, the query will generate a different result. You can read more here. 

But on the other hand… let’s assume that we would like to take a headcount of people in the departments. Naturally, we would write:

SELECT department, COUNT(*) AS [# of employees]
FROM dbo.Example
GROUP BY department

Result:

So, as you can see, in such a case, NULL is counted in one group. We can say that the values are grouped and the group NULL is the Missing Values Group.