match the columns in with 2 tables

Question

HI Expert,

I am comparing 2 tables in with all column match and un match

i.e.

create table Table1(col1 int,col2 int,col3 int,col4 int)

create table Table2(col1 int,col2 int,col3 int,col4 int)

insert into Table1 (2,3,4,5)

insert into Table1 (6,7,8,9)

insert into Table2 (2,3,4,5)

insert into Table2 (6,5,8,9)

expected output1 Matched

Answer 1

Hi @Shambhu Rai

Matched:

;with T1 as(
  select * from table1
  INTERSECT 
  select * from table2)
select * from T1
union all 
select * from T1;

Output:

Un Matched:

;with T1 as(
  select * from table1
  except 
  select * from table2
),T2 as(
  select * from table2
  except 
  select * from table1)
select * from T1
union all
select * from T2;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.