SQL Puzzle | The Replace Puzzle

This puzzle is really simple. You have to join table p1 and p2 on id column. Now the catch is when the value of p1.code is 0 then replace it with value of p2.code. You cannot use UNION or CASE statement. This was asked on experts exchange by Vadim R in 2006.

Please check out the sample input and the expected output below-

Sample Input

Table p1 Data

Table p2 Data

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

create table p1 (id int, code int)
create table p2 (id int, code int)
insert into p1 select 1,0
insert into p1 select 2,1
insert into p2 select 1,5
insert into p2 select 2,5

--

Solution 1 | Join & NULL

--

SELECT p2.id , ISNULL(t.code , p2.code) Code FROM  p2
LEFT OUTER JOIN 
(
	SELECT * FROM p1 WHERE p1.code <> 0
)t
ON p2.id = t.id

--

Solution 2 | NULLIF,ISNULL

--

SELECT p1.id , ISNULL(NULLIF(p1.code,0),p2.code) Code FROM  p1
INNER JOIN p2 ON p1.id = p2.id
GO

--

Solution 3 | Replace

--

select p.id Id,replace(p.code,'0',pp.code) as Code 
from p1 p full join p2 pp on p.id=pp.id

GO

--

Execution Plan Analysis-

Execution Plan Analysis – SQL Puzzle The Replace Puzzle

Clearly the first method is the best one we have…!!

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