We help IT Professionals succeed at work.

Data Comparison SQL

Hi Experts,

Today I have the task of comparing data from two tables, this has been the process so far.

Write SQL Select for data source
Write SQL Select for data target

So I have two tables, table A and Table B.

They have the same amount of columns but they have different names (at this point).

What technique is recommended for this process of collecting records which match?

Thank you
Comment
Watch Question

Lara FEA
CERTIFIED EXPERT

Commented:
You need to know on what column(s) you need to compare, and if columns can have nulls.

here is comparison based on 2 columns that do not allow nulls

-- data present in both tables
select a.*, b.*
from A join B on a.ac1=b.bc1 and a.ac2=b.bc2
-- data present in table a only
select a.*, b.*
from A
left join B on a.ac1=b.bc1 and a.ac2=b.bc2
where b.bc1 is null and b.bc2 is null

-- data present in table B only
select a.*, b.*
from B
left join A on a.ac1=b.bc1 and a.ac2=b.bc2
where a.ac1 is null and a.ac2 is null
Project Architect
CERTIFIED EXPERT
Commented:
You might be able to use INTERSECT to find the rows in common between both tables

SELECT * FROM table_A
INTERSECT
SELECT * FROM table_B

ref: http://msdn.microsoft.com/en-us/library/ms188055(v=sql.100).aspx

Explore More ContentExplore courses, solutions, and other research materials related to this topic.