Oracle SQL query only not matching values from 2 tables-CodePudding

I need a fairly simple query that would only return values from table A that do not have matching values in table B.

For example I have

Table A:

ID	Desc
12345	aaa
25678	bbb

Table B:

ID	Desc
12345	aaa

Query result should return as below since the ID 25678 is not present in table B.

ID	Desc
25678	bbb

I tried using the below sql but it returns an empty result and I'm not sure why:

select distinct
a.id,
a.desc

from 
table a

where 
date = (:a) 
and not exists (select b.id, b.desc from table b where date (:a))

Could anyone point me to the right direction here?

Thank you!

CodePudding user response：

I'd go with not exists:

SQL> with
  2  tablea (id, description) as
  3    (select 12345, 'aaa' from dual union all
  4     select 25678, 'bbb' from dual
  5    ),
  6  tableb (id, description) as
  7    (select 12345, 'aaa' from dual)
  8  --
  9  select *
 10  from tablea a
 11  where not exists (select null
 12                    from tableb b
 13                    where b.id = a.id
 14                   );

        ID DES
---------- ---
     25678 bbb

SQL>

Or, minus set operator:

select * from tablea
minus
select * from tableb;

Or, not in:

select *
from tablea a
where a.id not in (select b.id
                   from tableb b
                  );

Or, join:

select a.*
from tablea a join tableb b on a.id <> b.id;

CodePudding user response：

This below query may help you.

select a.* from A as a LEFT JOIN B on a.id = B.id where B.id is NULL;