Finding Differences between 2 tables

[capitano]

Does anyone know how to search for differences between 2 tables using SQL in a <CFQUERY>? I have 2 tables which are nearly identical except for some minor spelling and a couple additional rows in one table. I want to use SQL to pick out the differences, but having trouble crafting the correct SQL syntax.

I'm trying something along these lines:

<CFQUERY NAME=&quot;getdiff&quot; DATASOURCE=&quot;mydb&quot;>
SELECT a.table1, b.table2
FROM table1 a, table2 b
WHERE a.row <> b.row
</cfquery>

However, this isn't working. It doesn't line the rows up
correctly for comparison and so picks every possible combination on non-matches. I just don't know how to do this. Thanks for any help.

 

[jgroove]

I am not sure if this is the query that you are looking for, but it will look for records that are in one table and not in another. Let me know if it isn't what you are looking for and I can try to help you find a solution.

<cfquery name=&quot;getdiff&quot; datasource=&quot;mydb&quot;>
  select * from a
  where not exists(select 1 from b where a.row=b.row)
</cfquery>

hope this works for you,
jgroove

 

[shammack]

Seems like there are three different kinds of differences you might be interested in. This might require three queries or one query with two unions inside of it.

For the natural inner join between the two tables, i.e., rows with the same key, you can easily extract the rows whose non-key values do not match. Then you want the rows in table A whose keys do not match any keys in table B. Then you would want the rows in table B whose keys do not match any keys in table A.

Assuming that both tables have exactly the same column names and datatypes:

select *
from A,B
where A.key = B.key
and (A.non-key-col1 <> B.non-key-col1 or A.non-key-col2 <> B.non-key-col2 or ...)

union

select *
from A,B
where A.key not in (select B.key from B)

union

select *
from A,B
where B.key not in (select A.key from A)

Using correlated subqueries can be slow. If your tables are over 10,000 records, you might want to look for faster syntax using outer joins.