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

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

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

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)), '') = '';

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