CODEWITHSUNDEEP
sqloracle-database
Harry
Harry

Reputation: 89

Finding records which doesn't exists in other oracle table

I am trying to find records which exists in table A but not in table B. If there is only one column to check, then I can use 

select col_A,col_B,......from A where col_A not in (select Col_A from B).

But I have four columns which need to checked.

I did something like this, which works but is not the perfect way

select col_A,col_B,col_C,col_D from A where col_A||col_B||col_C||col_D not in (select col_A||col_B||col_C||col_D from B)

and also it takes lot of time to return results in case of large amount of data.

Please suggest the proper way of doing this.

Thanks.....

Upvotes: 3

Views: 5916

Answers (3)

Olaf H
Olaf H

Reputation: 496

From Oracle documentation

http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries004.htm#SQLRF52341

"MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:"

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

You requirements are a little bit more complicated. I assume, that the combination of cols_a, cols_b, cols_c, cols_d is unique for a record in table A. In that case, the following code should solve your problem:

create table A (
  cols_date DATE,
  cols_a NUMBER,
  cols_b NUMBER,
  cols_c NUMBER,
  cols_d NUMBER
);

create table B (
  cols_a NUMBER,
  cols_b NUMBER,
  cols_c NUMBER,
  cols_d NUMBER
);

insert into A (cols_date, cols_a, cols_b, cols_c, cols_d) values (sysdate, 1, 1, 1, 1);
insert into A (cols_date, cols_a, cols_b, cols_c, cols_d) values (sysdate, 2, 2, 2, 2);

insert into B (cols_a, cols_b, cols_c, cols_d) values (2, 2, 2, 2);
insert into B (cols_a, cols_b, cols_c, cols_d) values (3, 3, 3, 3);
commit;

select a.cols_date, a.cols_a, a.cols_b, a.cols_c, a.cols_d from (
  select cols_a, cols_b, cols_c, cols_d
  from A
  minus 
  select cols_a, cols_b, cols_c, cols_d
  from b
) ma, a
where 1=1
  and ma.cols_a = a.cols_a
  and ma.cols_b = a.cols_b
  and ma.cols_c = a.cols_c
  and ma.cols_d = a.cols_d;

Result is 

COLS_DATE                 COLS_A     COLS_B     COLS_C     COLS_D
--------------------- ---------- ---------- ---------- ----------
01.07.2013 13:20:02            1          1          1          1

NOT EXISTS will also solve the problem. This statement has a better execution plan then the MINUS version.

Thanks to David Aldridge for this solution.

select
  cols_date,
  cols_a, cols_b, cols_c, cols_d
from
  a
where
  not exists (
    select 1
    from   b
    where  1=1
  and b.cols_a = a.cols_a
  and b.cols_b = a.cols_b
  and b.cols_c = a.cols_c
  and b.cols_d = a.cols_d           
);

Upvotes: 6

David Aldridge
David Aldridge

Reputation: 52336

There's an implicit distinct on MINUS that you'd probably want to avoid.

A NOT EXISTS construct would probably be run as a hash anti-join, which would be very efficient.

select
  col1,
  col2,
  col3,
  ... etc
from
  table_a a
where
  not exists (
    select null
    from   table_b b
    where  a.col1 = b.col1 and
           a.col2 = b.col2 and
           a.col3 = b.col3 and
           a.col4 = b.col4)

Upvotes: 3

just somebody
just somebody

Reputation: 19247

WITH mine AS (
  SELECT cols_a, cols_b, cols_c, cols_d FROM a
  MINUS
  SELECT cols_a, cols_b, cols_c, cols_d FROM b
)
SELECT a.cols_date, a.cols_a, a.cols_b, a.cols_c, a.cols_d
FROM a NATURAL JOIN mine

Upvotes: 1

Related Questions