l******9
发帖数: 579 | 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 | F*******2
发帖数: 371 | 2 你code得到的是在table1 但是不在table2里面的数据
用下面code可以得到不在A B inner join中的数据：
SELECT A.ID1 AS A_ID1, A.ID2 AS A_ID2, A.ID3 as A_ID3, A.Value AS A_Value,
B.Value AS B_Value, B.ID1 AS B_ID1,B.ID2 AS B_ID2
FROM Table1 AS A
FULL OUTER JOIN Table2 AS B
ON A.ID1 = B.ID1 and A.ID2=B.ID2
WHERE A.ID1 IS NULL
OR B.ID1 IS NULL
Check here:
http://www.codeproject.com/Articles/33052/Visual-Representation | F*******2
发帖数: 371 | 3 还有看table1和table2中ID1 ID2 的format是否一致 |
|
