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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- 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’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
-- 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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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
|
1 2 3 4 5 6 7 8 9 |
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 |
Keep it simple :-)