How to use intersect (and maybe minus) on this example

Question

lets say I have two tables:

TableA
ID,    Time,   Size

0      5:00    600
1      8:00    800
2      7:00    100
3      1:50    140
4      2:40    300 
12     3:40    300

TableB
ID,    Time,   Size

8      5:00    600
1      8:00    800
2      8:00    900
3      1:50    140
5      2:40    300 
12     3:40    300

Now I would like to compare these two Tables: give all rows from Table A and B where the IDs are same (or lets say where the IDs are existing in both of the Tables) but lower then 12 - so that would kick out table A ID 0, 4, 12 and from table B ID 8, 5, 12

After that I would have only 3 rows left. Now I would like to kick out all rows where there is one or more difference between the whole TableA row ID == TableB row ID

At the end there would be (if I see that correct) as output:

ID,    Time,   Size
1      8:00    800
3      1:50    140

For this solution I need for sure intersect and maybe minus. At first I tought this SQL statement would do what I want:

select * from TableA where ID < 12 intersect select * from TableB where ID < 12 
minus 
select * from TableB where ID < 12;

But this is not working that well. Ist because of the intersect, Im using intersect for the whole row, I should use intersect for only the IDs and IF I have the intersect of the IDs (that would be 1,2,3) then I have to use ID TableA with ID 1,2,3 minus TableB with ID 1,2,3. But how? Or do I miss something? Any ideas? Thank you

score 3 · Answer 1 · answered May 29 '12 at 14:15

3

From your requirements and the testdata you've provided, you don't need intersect at all. A mere INNER JOIN would suffice.

SELECT a.* 
FROM   TableA a 
       INNER JOIN TableB b ON b.ID = a.ID AND b.Time = a.Time AND b.Size = a.Size

answered May 29 '12 at 14:15

Lieven Keersmaekers

57,207
13
112
146

What If there would be more then 3 columns (not only ID, Time and Size), lets say there are 100? :) It would be a very large SQL statement with Inner Join right? But its a good one, good to know. – sabisabi May 29 '12 at 14:17
2

In that case I would use `CHECKSUM(*)` and join on that. – Lieven Keersmaekers May 29 '12 at 14:18
@sabisabi - strike that last comment, it seems that there's no (easy to use) checksum function for Oracle. – Lieven Keersmaekers May 29 '12 at 14:21
Ah ok, too bad I was on the way to try it with checksum ^^ – sabisabi May 29 '12 at 14:23
@sabisabi - you can try some of the alternative suggestions posted [here at SO](http://stackoverflow.com/questions/8827184/is-there-an-md5-sum-function-in-pl-sql) – Lieven Keersmaekers May 29 '12 at 14:25
I would take the list of columns, dump them into Excel, and create the following expressions: cast(col1 as varchar)||';'+cast(col2 as varchar) . . . – Gordon Linoff May 29 '12 at 16:05

Romil Kumar Jain · Accepted Answer · 2012-05-30T02:10:39.707

1

select id from TableA where ID < 12 and id in (Select id from TableB)
union 
select id from TableB where ID < 12   and id in (Select id from TableA)

or

SELECT id, 
       time, 
       size 
FROM   (SELECT * 
        FROM   tablea 
        WHERE  id < 12 
        UNION ALL
        SELECT * 
        FROM   tableb 
        WHERE  id < 12) a 
GROUP  BY id, 
          time, 
          size 
HAVING Count(*) = 2

or

SELECT * 
FROM   tablea A 
WHERE  EXISTS (SELECT 1 
               FROM   tableb b 
               WHERE  b.id = a.id 
                      AND b.time = a.time 
                      AND b.size = a.size)

edited May 30 '12 at 02:10

answered May 29 '12 at 14:14

Romil Kumar Jain

20,239
9
63
92

Hi. This `id in (select..)` looks nice. But why is there union, is minus not better to get that what I want? – sabisabi May 29 '12 at 15:03
Mysql Minus is used to see only those records from table which are not in another table. – Romil Kumar Jain May 29 '12 at 16:43

How to use intersect (and maybe minus) on this example

2 Answers2