Difference between tables with the same structure

Question

I have two tables with the same structure and with slightly different rows - Table A, and Table B. I would like to extract all the rows that are contained in table A but not in Table B. CAn you help me do that?

By the way - Table A is in definition form, it does not previously created.

And additionaly - I have 15 sql scripts to analyse. I would like to find some software that can help me with visualization of the entire proces (composed of 15 sql scripts). Can you suggest something good?

If `table_a` doesn't exist yet, there are no differences. Also, you should be able to do _some_ sort of anonymization, to give us an example. — Clockwork-Muse, Jul 26 '12 at 15:29

score 7 · Accepted Answer · answered Jul 26 '12 at 15:21

7

try

SELECT * FROM Table_A
EXCEPT
SELECT * FROM Table_B

See http://en.wikipedia.org/wiki/Set_operations_%28SQL%29#EXCEPT_operator

answered Jul 26 '12 at 15:21

filimonov

1,327
1
7
15

1

Huh, I did *not* know about that keyword. And [Teradata appears to support it](http://www.teradataforum.com/teradata_pdf/b035-1101-122a_5.pdf) as well. – Daniel DiPaolo Jul 26 '12 at 15:27
1

If you allow NULL values in any of your columns you may wish to COALESCE them to a known value that is not within your acceptable range of values for that column. (e.g. '~' or negative number) – Rob Paller Jul 26 '12 at 16:15

score 1 · Answer 2 · answered Jul 26 '12 at 15:21

1

One way is to use an left outer join this selects all in the first table and then matches these in the second. If the extra columns coming from the second table a NULL then there is no matching record in the second.

Suppose columns a to c are unique in both tables

select a.*
  from tableA a
  left outer join tableB on a.a = b.a and ... a.c = b.c
  where b.a is null and ... and b.c is null

answered Jul 26 '12 at 15:21

mmmmmm

30,723
26
85
109

this seems interesting. I mentioned my table is in definition form - not created previously. Can you give me suggestion on how to do extraction regarding this matter? – Dantes Jul 26 '12 at 15:24
If tableA is not created yet then there is no data to select as you ask " extract all the rows that are contained in table A but not in Table B" so if no data in tableA there no data to return. Thus I think you need to make what you ask for clearer – mmmmmm Jul 26 '12 at 15:25
Maybe my explanation was little vague. I meant that Tables A and B are written like deffinitions - For example if I have Table C and D - I make table A like: SELECT * FROM TableC, TableD -> This now is defined like my Table A. – Dantes Jul 26 '12 at 15:30
SELECT * FROM TableC, TableD us not valid SQL - whey not act directly on C and D? – mmmmmm Jul 26 '12 at 15:31
I was just trying to picture you the problem. It is not important if it´s valid or not - just that table A is formed from the other existing tbls. And no, I can´t use directly other tables. Maybe Table A can look like this SELECT DISTINCT column-1, column-2, column-3 FROM TableC INNER JOIN TableD ON some-condition – Dantes Jul 26 '12 at 15:35
Why does tableA exist? just select from C and D Note your question ONLY makes sense if A exits otherwise the answer is trivially noting – mmmmmm Jul 26 '12 at 15:38

score 1 · Answer 3 · edited Jan 04 '13 at 23:25

I was facing on a regular basis the same problem so I wrote my own software that can handle large databases (dozens of columns, tens of thousands of line) efficiently. I imagine you solved your problem but I post here if anybody else face the same problem.

The software is in R and can query and save to a MySQL server. To test it out though it may be easier to export your bases to two csv files as configuring the MySQL link (via RMySQL) may take a little time. Check it out on gitHub.

We use it on a very regular basis in my team and are happy with it.

score 0 · Answer 4 · answered Jul 26 '12 at 15:20

0

A pain in the butt to write the query manually, so there are tools (like RedGate's SQL Compare) that do it for you. But...

SELECT
    A.*
   ,B.*
FROM
   A LEFT OUTER JOIN B
       ON A.Field1 = B.Field1
       AND A.Field2 = B.Field2
       ...   -- join on each field
WHERE
   B.Field1 IS NULL OR
   B.Field2 IS NULL OR 
   ...  -- check for any NULL fields in B

If you're not interested in all data differences and only key differences then just change the list of fields you join on and filter on to the key fields.

answered Jul 26 '12 at 15:20

Daniel DiPaolo

51,147
13
111
112

SUrely you only want the left outer join not the full which would get those in tableB with no correspondance in tableA as well – mmmmmm Jul 26 '12 at 15:23
@Mark you're right, I had originally put LEFT and then thought he wanted both but failed to fix the query, thanks – Daniel DiPaolo Jul 26 '12 at 15:24

Difference between tables with the same structure

4 Answers4