aone又跌回去了# Stock
l*9
1 楼
【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
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
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
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
