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.
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.
We've got a few operators to consider.
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
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
SELECT id, department, name FROM dbo.Example WHERE department IS NULL
OK, so let’s try to use the
IN operator instead.
SELECT id, department, name FROM dbo.Example WHERE department IN ('HR', NULL)
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
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
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.