l******9
发帖数: 579 | 1 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.
I use this method :
SELECT * FROM table1 a
WHERE NOT EXISTS (
SELECT table2.ID1
FROM table2 b
WHERE b.ID1 = a.ID1
AND b.ID2 = a.ID2)
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 | k***n
发帖数: 997 | 2 outer join can display difference
select t1id , t2id from
(select table1.id1 t1id , table2.id1 t2id from table1
full join table2 on (table1.id1=table2.id1) )
where t1id is null ; |
|
