Join on 4 table

Question

There are 4 table A,B,C and D. There are all records in A. and some of records in B ,some in C and some in D. I want to select those records from A and which are not in B,C and D. Please tell me sql join query .

Sachin Singh · Answer

what you want is A-(B+C+D) so your query should be select * from table 1 except (SELECT * FROM table 2 UNION SELECT * FROM table 3 UNION SELECT * FROM table 4 )

Nitin Sontakke · Answer

The accepted answer is fine to give in an interview as it technically correct. In real life, however, rarely it is as simple as all tables are having exact same structure and need comparision. When you are faced with that situation, assuming there is a key available for comparision, which it should as it makes no sense to compare unrelated tables, you can use something as shown below: select t1.[Id], t1.[ Name ] , case when t2.[Id] is null then 'Yes' else 'No' end [Missing from B] , case when t3.[Id] is null then 'Yes' else 'No' end [Missing from C] , case when t4.[Id] is null then 'Yes' else 'No' end [Missing from D] from [dbo].[Table1] t1 left outer join [dbo].[Table2] t2 on t1.[Id] = t2.[Id] left outer join [dbo].[Table3] t3 on t1.[Id] = t3.[Id] left outer join [dbo].[Table4] t4 on t1.[Id] = t4.[Id]

Mageshwaran R · Answer

Hi Sonali, Please try this code, SELECT A.Column1,A.Column2,A.Column3 FROM A INNER JOIN B ON B.id = A.id INNER JOIN C ON C.id = A.id; INNER JOIN D ON D.id = A.id;