CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008netezza
user3448011
user3448011

Reputation: 1599

check the likeness between two very large data tables on SQL

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:

Get super set records not contained in a subset (mutual exclusion)

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

Upvotes: 0

Views: 202

Answers (1)

J_Arthur
J_Arthur

Reputation: 75

NETEZZA

http://www.techonthenet.com/sql/minus.php

SELECT * FROM TABLE_1
MINUS
SELECT * FROM TABLE_2

Upvotes: 0

Related Questions