This one is the interview question faced by my friend during project allocation interview.
We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.
The query needs to be written to produce the output given below
My first reaction was to use UNION and then think about displaying pseudo Result Column. I tried using it for 2-3 minutes and it wasn't going anywhere so changed my approach came up with the answer..
First let's prepare the table with the required data.
We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.
The query needs to be written to produce the output given below
My first reaction was to use UNION and then think about displaying pseudo Result Column. I tried using it for 2-3 minutes and it wasn't going anywhere so changed my approach came up with the answer..
First let's prepare the table with the required data.
DECLARE @TAB1 TABLE(ID1 SMALLINT
)
DECLARE @TAB2 TABLE(ID2 SMALLINT
)
INSERT INTO @TAB1SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3
INSERT INTO @TAB2SELECT 4 UNION ALLSELECT 2 UNION ALLSELECT 3
--Given below are the 3 approaches that I could think of right now
-- First Attempt using FULL OUTER JOIN
SELECT CASE
WHEN T1.ID1 IS NULL THEN T2.ID2
ELSE T1.ID1
END AS ID,CASE
WHEN T1.ID1 IS NULL THEN 'IN T2'
WHEN T2.ID2 IS NULL THEN 'IN T1'
ELSE 'IN BOTH'END AS Result
FROM @TAB1 T1
FULL OUTER JOIN @TAB2 T2
ON T1.ID1 = T2.ID2
-- Second attempt using INTERSECT,EXCEPT and UNION ALL
SELECT * FROM (
(
SELECT *,'IN T1' AS RESULT FROM @TAB1
EXCEPT
SELECT *,'IN T1' FROM @TAB2)
UNION ALL
(
SELECT *,'IN BOTH' FROM @TAB1
INTERSECT
SELECT *,'IN BOTH' FROM @TAB2)
UNION ALL
(
SELECT *,'IN T2' FROM @TAB2
EXCEPT
SELECT *,'IN T2' FROM @TAB1)
) TEST
-- Third attempt using IN, NOT IN AND UNION ALL
SELECT *,'IN T1' AS RESULT FROM @TAB1WHERE ID1 NOT IN ( SELECT ID2 FROM @TAB2 )
UNION ALL
SELECT ID1,'IN BOTH' FROM @TAB1WHERE ID1 IN (SELECT ID2 FROM @TAB2)
UNION ALL
SELECT *,'IN T2' FROM @TAB2
WHERE ID2 NOT IN ( SELECT ID1 FROM @TAB1 )
No comments:
Post a Comment