SQL Puzzle | Find Different Values Puzzle

Puzzle Statement

In this puzzle you have to find rows where one of the column value is 1. We don’t need values where both column have values 1. Please check sample input and expected output for details. One of my old friend asks this question in all the technical interviews he used to take.

Sample Input

Expected output

Script

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

--

CREATE TABLE TestXOR
(
	 a SmallINT 
	,b SmallINT
)
GO


INSERT TestXOR
VALUES 
(0,1),
(1,2),
(1,1),
(2,3),
(5,3)
GO

--

Rules/Restrictions

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

SOLUTION 1 | Using Simple SELECT

--

SELECT a,b FROM TestXOR
WHERE ( a = 1 AND b != 1 ) OR ( a != 1 AND b = 1 )

--

SOLUTION 2 | Using XOR Operator & Case

--

SELECT a,b FROM TestXOR
WHERE (CASE WHEN a = 1 THEN 1 ELSE 0 END) ^ 
	  ( CASE WHEN b = 1 THEN 1 ELSE 0 END ) = 1

--

SOLUTION 3 | Using Case Trick

--

SELECT a,b FROM TestXOR
WHERE (CASE WHEN a = 1 THEN 1 ELSE 0 END) +
	  ( CASE WHEN b = 1 THEN 1 ELSE 0 END ) = 1

--

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