check the likeness between two very large data tables on SQL

Question

I need to check the likeness between two data tables on SQL. I am working on Aginity Workbench for Netezza on Win 7.

The tables are very large. One of them has 100 million rows and 4 columns; another one has 1500 million rows and 3 columns.

Example, table1

  ID1    ID2      ID3        Value   
  xxxx   xxxxxx   xxxxxxxx    xxx.xxxxxx  // here x is 0-9 int

table2:

  ID1    ID2          Value   
  xxxx   xxxxxx     xxx.xxxxxx

the ID1 and ID2 may be duplicated but Values are not duplicated in the same table.

I need to check whether table1 is a subset of table2 and find the rows that are avaialble in table1 but not in table2 and vice versa.

I am new to SQL. How to design the efficient SQL queries ? I need to do the same tasks frequently, so en efficient query may be more helpful.

UPDATE I used solution at:

But, the results are 0 rows. I also checked that there are no duplicated rows in the two tables. Why table2 is much larger than table1 ?

Are there other ways to find their differences ?

Thanks

Mysql or SqlServer? Seems unlikely it's both. What have you tried is a question. Also is this a one off task, and every now and then, or something you want to do very frequently? — Tony Hopkinson, May 06 '14 at 16:19
@Dan Bracuk, I am new to SQL. How to design the efficient SQL queries ? Thanks — user3448011, May 06 '14 at 16:21
MySql or sql server???? This is two basic outer join queries, after adding a couple of indexes there's not much you can do on the software front. You'd be better off finding a way to avoid doing this at all, frequently would be inadvisable... — Tony Hopkinson, May 06 '14 at 16:38
@Tony Hopkinson, Netezza SQL, would you please tell me how to add index for queries ? thanks ! — user3448011, May 06 '14 at 16:45
@Tony Hopkinson, the ID1 and ID2 may be duplicated but Values are not duplicated in the same table. — user3448011, May 06 '14 at 16:51

score 0 · Answer 1 · answered Feb 06 '15 at 01:19

0

NETEZZA

SELECT * FROM TABLE_1
MINUS
SELECT * FROM TABLE_2

answered Feb 06 '15 at 01:19

J_Arthur

1 Answers1