SQL Puzzle | The ISNULL Puzzle

In this puzzle you have two tables – MatchingA and MatchingB. NULL in a record from first table should match with the NULL in a record in another table. The catch is you have to find out the matching records but usage of ISNULL function is not allowed. This has been in a recent technical interview.

Please check out the sample input values and sample expected output below.

Sample Input

Table 1

Table 2

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE MatchingA
(
	 Id INT
	,location VARCHAR(10)	
)
GO

INSERT INTO MatchingA VALUES (1,'A'),(2,'B'),(3,NULL),(4,NULL),(5,'C')
GO

CREATE TABLE MatchingB
(
	 Id INT
	,location VARCHAR(10)
	,data VARCHAR(100)	
)
GO

INSERT INTO MatchingB VALUES (1,'A','Data1'),(2,'B','Data2'),(3,NULL,'Data3'),(4,NULL,'Data4')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1 – Full use of Indexes

--

SELECT b.* FROM 
MatchingA a
INNER JOIN MatchingB b ON a.Id = b.Id
AND ( ( a.location = b.location ) OR ( a.location IS NULL AND b.location IS NULL ) )

--

** – Note : Please do not use functions in a WHERE or JOIN clause if they can be avoided, because you will prevent the full use of all possible indexes. In this case you can easily avoid the use of ISNULL or Coalesce function in a WHERE or JOIN condition.

Solution 2

--

SELECT b.* FROM 
MatchingA a
INNER JOIN MatchingB b ON a.Id = b.Id
AND ISNULL(a.location,'') = ISNULL(b.location,'')

--

** – Note : You can use above also if you do not consider performance or you just wanted to run the query once or twice.

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com