asked
Query to find unmatched records
Hi All, Please help me with the below mentioned scenario, Data is-> Table A(int id,name varchar(20) Table B(int id,name varchar(20) A 1,'john' 2,'paul; 3,'Sid' 8,'pol' B 1,'john' 2,'paul; 4,'Max' 6,'Pat' 3,'Sid' I wanna retrieve o/p as below 4,'Max' 6,'Pat', 8,'pol' That is I want unmatched records of both table A and B.
The combination of FULL OUTER JOIN and IS NULL would do the job for you CREATE Table #A(id INT ,name varchar(20) ) Create Table #B( id int ,name varchar(20)) INSERT [#A] ( id ,[name] ) SELECT 1,'john' UNION ALL SELECT 2,'paul' UNION ALL SELECT 3,'Sid' UNION ALL SELECT 8,'pol' INSERT [#B] ( id ,[name] ) SELECT 1,'john' UNION ALL SELECT 2,'paul' UNION ALL SELECT 4,'Max' UNION ALL SELECT 6,'Pat' UNION ALL SELECT 3,'Sid' SELECT ISNULL([A].[id], [B].[id]), ISNULL([A].[name], [B].[name]) FROM [#A] AS A FULL OUTER JOIN [#B] AS B ON [A].[name] = [B].[name] WHERE [A].[name] IS NULL OR [B].[name] IS NULL DROP TABLE [#A],[#B]
There's a handy reference image going round that may help with these problems... ![Handy reference image][1] [1]:
http://media-cdn.pinterest.com/upload/48695239690455741_FTyCDEus_f.jpg
4 People are following this question.
