T-SQL: Manipulating NULL value

NULL values are special in SQL Server. They are not blanks or zeroes, but “Nothing” as they don’t take space in the database (table columns) and in the memory (variables). VBA uses the same word (Nothing) for NULL. The usage of NULL is a great discussion between developers and DBAs and it is a matter of preference. I use the NULL value in my database architecture and development.

Create a test database

USE master;
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

USE master;

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Any declared variable has a value of NULL before a value is SET.
When assigning a value to a variable, I use =, <> (!=) and when I manipulate the variable, I use IS NULL, IS NOT NULL.

-- Declare @Variable1
DECLARE @Variable1 INT;

-- Check @Variable1
SELECT 'Test 1' AS [Test 1], @Variable1 AS [@Variable1];

-- Assign a value to @Variable1
SET @Variable1 = 123;

-- Check @Variable1
SELECT 'Test 2' AS [Test 2], @Variable1 AS [@Variable1];

-- equal to, not equal to or IS NULL IS NOT NULL?
-- Assign a value to @Variable1
SET @Variable1 = NULL;

-- Equal to
IF (@Variable1 = NULL)
	BEGIN SELECT 'IF (@Variable1 = NULL)' AS [Test 3], 'TRUE' AS [IF Returns], @Variable1 AS [@Variable1]; END
ELSE
	BEGIN SELECT 'IF (@Variable1 = NULL)' AS [Test 3], 'FALSE' AS [IF Returns], @Variable1 AS [@Variable1]; END

-- IS NULL
IF (@Variable1 IS NULL)
	BEGIN SELECT 'IF (@Variable1 IS NULL)' AS [Test 4], 'TRUE' AS [IF Returns], @Variable1 AS [@Variable1]; END
ELSE
	BEGIN SELECT 'IF (@Variable1 IS NULL)' AS [Test 4], 'FALSE' AS [IF Returns], @Variable1 AS [@Variable1]; END
GO

-- Declare @Variable1

DECLARE @Variable1 INT;

-- Check @Variable1

SELECT 'Test 1' AS [Test 1], @Variable1 AS [@Variable1];

-- Assign a value to @Variable1

SET @Variable1 = 123;

-- Check @Variable1

SELECT 'Test 2' AS [Test 2], @Variable1 AS [@Variable1];

-- equal to, not equal to or IS NULL IS NOT NULL?

-- Assign a value to @Variable1

SET @Variable1 = NULL;

-- Equal to

IF (@Variable1 = NULL)

BEGIN SELECT 'IF (@Variable1 = NULL)' AS [Test 3], 'TRUE' AS [IF Returns], @Variable1 AS [@Variable1]; END

ELSE

BEGIN SELECT 'IF (@Variable1 = NULL)' AS [Test 3], 'FALSE' AS [IF Returns], @Variable1 AS [@Variable1]; END

-- IS NULL

IF (@Variable1 IS NULL)

BEGIN SELECT 'IF (@Variable1 IS NULL)' AS [Test 4], 'TRUE' AS [IF Returns], @Variable1 AS [@Variable1]; END

ELSE

BEGIN SELECT 'IF (@Variable1 IS NULL)' AS [Test 4], 'FALSE' AS [IF Returns], @Variable1 AS [@Variable1]; END

SELECT FirstName = John, WHERE LastName doesn’t start with ‘Smith’

-- Create test table
IF (OBJECT_ID('PL.dbo.LikeAndNull1', 'U') IS NOT NULL)
	BEGIN DROP TABLE PL.dbo.LikeAndNull1; END
GO

CREATE TABLE PL.dbo.LikeAndNull1
	(
		[ID] INT IDENTITY(1, 1) PRIMARY KEY
		, [FirstName] VARCHAR(16)
		, [LastName] VARCHAR(32)
	);
GO

INSERT PL.dbo.LikeAndNull1 ([FirstName], [LastName])
SELECT 'John', 'Smith'
UNION ALL SELECT 'John', 'Smithson'
UNION ALL SELECT 'John', NULL
UNION ALL SELECT 'John', 'Thompson'
UNION ALL SELECT 'John', 'Anders'
UNION ALL SELECT 'Paul', 'Henderson'
UNION ALL SELECT 'George', 'Mapple'
UNION ALL SELECT 'John', ''
UNION ALL SELECT 'John', '   '
UNION ALL SELECT 'Melanie', '     ';
GO

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 0' AS [Test 0]
FROM PL.dbo.LikeAndNull1
ORDER BY FirstName, LastName;
GO

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 1' AS [Test 1]
FROM PL.dbo.LikeAndNull1
WHERE
	FirstName = 'John'
	AND LastName NOT LIKE 'Smith%'
ORDER BY FirstName, LastName;
GO
-- NULL is not selected

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 2' AS [Test 2]
FROM PL.dbo.LikeAndNull1
WHERE
	FirstName = 'John'
	AND
	(
		LastName IS NULL
		OR
			(
				LastName NOT LIKE 'Smith%'
				--AND LastName NOT LIKE 'Abc%' -- condition 2
			)
	)
ORDER BY FirstName, LastName;
GO
-- NULL is selected

-- Create test table

IF (OBJECT_ID('PL.dbo.LikeAndNull1', 'U') IS NOT NULL)

BEGIN DROP TABLE PL.dbo.LikeAndNull1; END

CREATE TABLE PL.dbo.LikeAndNull1

(

[ID] INT IDENTITY(1, 1) PRIMARY KEY

, [FirstName] VARCHAR(16)

, [LastName] VARCHAR(32)

);

INSERT PL.dbo.LikeAndNull1 ([FirstName], [LastName])

SELECT 'John', 'Smith'

UNION ALL SELECT 'John', 'Smithson'

UNION ALL SELECT 'John', NULL

UNION ALL SELECT 'John', 'Thompson'

UNION ALL SELECT 'John', 'Anders'

UNION ALL SELECT 'Paul', 'Henderson'

UNION ALL SELECT 'George', 'Mapple'

UNION ALL SELECT 'John', ''

UNION ALL SELECT 'John', ' '

UNION ALL SELECT 'Melanie', ' ';

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 0' AS [Test 0]

FROM PL.dbo.LikeAndNull1

ORDER BY FirstName, LastName;

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 1' AS [Test 1]

FROM PL.dbo.LikeAndNull1

WHERE

FirstName = 'John'

AND LastName NOT LIKE 'Smith%'

ORDER BY FirstName, LastName;

-- NULL is not selected

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 2' AS [Test 2]

FROM PL.dbo.LikeAndNull1

WHERE

FirstName = 'John'

AND

(

LastName IS NULL

(

LastName NOT LIKE 'Smith%'

--AND LastName NOT LIKE 'Abc%' -- condition 2

)

ORDER BY FirstName, LastName;

-- NULL is selected

Filtering NULL and blank (empty string)

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 1' AS [Test 1]
FROM PL.dbo.LikeAndNull1
WHERE LastName = '';
-- Trimmed

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 2' AS [Test 2]
FROM PL.dbo.LikeAndNull1
WHERE LastName = NULL;
-- Selects nothing

SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 3' AS [Test 3]
FROM PL.dbo.LikeAndNull1
WHERE LastName IS NULL;
-- Good practice IS NULL

-- Combines blank and NULL v1
SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 4' AS [Test 4]
FROM PL.dbo.LikeAndNull1
WHERE (LastName IS NULL OR LastName = '');

-- Combines blank and NULL v2
SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 5' AS [Test 5]
FROM PL.dbo.LikeAndNull1
WHERE isnull(LastName, '') = '';

-- Combines blank and NULL v3
SELECT
	[ID]
	, [FirstName]
	, '>' + [LastName] + '<' AS LastName
	, 'Test 6' AS [Test 6]
FROM PL.dbo.LikeAndNull1
WHERE isnull(ltrim(rtrim(LastName)), '') = '';

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 1' AS [Test 1]

FROM PL.dbo.LikeAndNull1

WHERE LastName = '';

-- Trimmed

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 2' AS [Test 2]

FROM PL.dbo.LikeAndNull1

WHERE LastName = NULL;

-- Selects nothing

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 3' AS [Test 3]

FROM PL.dbo.LikeAndNull1

WHERE LastName IS NULL;

-- Good practice IS NULL

-- Combines blank and NULL v1

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 4' AS [Test 4]

FROM PL.dbo.LikeAndNull1

WHERE (LastName IS NULL OR LastName = '');

-- Combines blank and NULL v2

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 5' AS [Test 5]

FROM PL.dbo.LikeAndNull1

WHERE isnull(LastName, '') = '';

-- Combines blank and NULL v3

SELECT

[ID]

, [FirstName]

, '>' + [LastName] + '<' AS LastName

, 'Test 6' AS [Test 6]

FROM PL.dbo.LikeAndNull1

WHERE isnull(ltrim(rtrim(LastName)), '') = '';

Clean up test database

USE master;
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

USE master;

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

Keep it simple :-)