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 | r*g
发帖数: 3159 | 2 SELECT *
FROM table1 LEFT OUTER JOIN table2
ON table1.ID1 = table2.ID1 AND table1.ID2 = table2.ID2
WHERE table2.ID1 IS NULL
OR table2.ID2 IS NULL | S*********N
发帖数: 6151 | 3
从他问的几个问题看，他根本没系统学过数据库和算法，自学了几个语法来解决
自己的问题。
【在 r*g 的大作中提到】
: SELECT *
: FROM table1 LEFT OUTER JOIN table2
: ON table1.ID1 = table2.ID1 AND table1.ID2 = table2.ID2
: WHERE table2.ID1 IS NULL
: OR table2.ID2 IS NULL
|
|
