A pattern to match correctly on a value or a NULL

3

August 5, 2015 by Kenneth Fisher

As you may have noticed if you read my blog I learn a lot from trolling various forums. In this particular case I was looking at dba.stackexchange.com and came across this great code pattern. Now the answer comes from Andriy M but in the comments they give credit to Paul White (b/t). So what is this amazing pattern?

Well have you ever needed to match two values? Specifically where you need to match the values even if they are NULLs? Something like this.

CREATE TABLE Lookup1 (Code char(2), Description varchar(500));
INSERT INTO Lookup1 VALUES (NULL, 'Unknown'), ('AA','Division1'),
						('BB','Division2'), ('CC','Division3');
GO
CREATE TABLE SourceTable (
	Id_SourceTable int NOT NULL IDENTITY(1,1) CONSTRAINT pk_SourceTable PRIMARY KEY,
	Code char(2),
	MoreColumns varchar(50),
	MoreColumns2 varchar(50)
	);
GO
CREATE INDEX ix_SourceTable_Code ON SourceTable(Code);
GO
INSERT INTO SourceTable VALUES
	(NULL,'One','One'), ('AA','Two','One'), ('BB',NULL,'One'), ('CC','Four','One'),
	(NULL,'One','Two'), ('AA','Two',NULL), ('BB','Three','Two'), ('CC','Four','Two'),
	(NULL,'One','Three'), ('AA','Two','Three'), ('BB','Three',NULL), ('CC','Four','Three'),
	(NULL,'One','Four'), ('AA',NULL,NULL), ('BB','Three','Four'), ('CC','Four','Four');
GO

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON SourceTable.Code = Lookup1.Code
	OR (SourceTable.Code IS NULL AND Lookup1.Code IS NULL)
GO

This isn’t the best data model in the world since it’s using a NULL as an actual value but it will do as an example.

Some people try code like this:

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON ISNULL(SourceTable.Code,'NULL') = ISNULL(Lookup1.Code,'NULL')
GO

But I wouldn’t. SQL won’t use any indexes and that’s going to slow everything down.

The new interesting version looks like this:

SELECT SourceTable.*, Lookup1.Description
FROM SourceTable
JOIN Lookup1
	ON EXISTS (SELECT SourceTable.Code INTERSECT SELECT Lookup1.Code)
GO

In terms of speed/query plan it’s basically the same as the first query. It just looks better (IMHO). It get’s even better when you start dealing with multiple values.

DECLARE @Code char(2) = 'AA';
DECLARE @MoreColumns varchar(50) = NULL;

SELECT *
FROM SourceTable
WHERE EXISTS 
	(SELECT Code, MoreColumns INTERSECT
	SELECT @Code, @MoreColumns);

-- VS

SELECT *
FROM SourceTable
WHERE (Code = @Code OR
		(Code IS NULL AND @Code IS NULL))
	AND (MoreColumns = @MoreColumns OR
		(MoreColumns IS NULL AND @MoreColumns IS NULL));
GO

Still the same query plan but much easier to read and maintain. Pretty cool hu? I’ll be curious to see if this improves/worsens performance under more complex circumstances. It’s probably not a huge game changer but still rather interesting. And still matches the current best pattern so it’s worth keeping in your toolbox.

Note: You can use EXCEPT to to do not equals.

3 thoughts on “A pattern to match correctly on a value or a NULL

  1. davidbainbridge83 says:

    Wow that is elegant!

  2. Tess says:

    Great post! Although…
    “As you may have noticed if you read my blog I learn a lot from trolling various forums”
    Trolling or trawling?? Big difference 🙂

    • Good point 🙂 Although trolling (at least among the group I hung out with) did mean the same thing way back when the meaning is certainly different now. I’ll be more careful in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: