CODEWITHSUNDEEP
sqloracle-database
user3789200
user3789200

Reputation: 1186

SQL query with both table columns-Oracle

There are two tables named, for an example A and B.

A and B has a unique column named key_ref. and key_ref is the primary key of both tables

I need to find records in A but not in B. Therefore I wrote a query like, 

SELECT a.*,b* from A a,B b WHERE key_ref NOT IN (SELECT key_ref from B)

The issue with this is, I do not get the empty columns of table B for the result. My result should include all the columns of A and B.

If I write my query like following my results get wrong. Is there any way , where I can achieve this even with a join condition. 

SELECT a.* from A a WHERE key_ref NOT IN (SELECT key_ref from B)

Please refer following example.

Table A                            Table B

key ref  col1                   key ref  col2

 A        aaa                    A        aaa
 B        bbb                    B        bbb
 C        ccc                    C        ccc
 D        ddd

My answer should be,

key ref  col1   col2

D        ddd

Upvotes: 0

Views: 69

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If the second table tableB doesn't have a record associated with first table, then how would you display record from second table tableB which are not exists in the first table tableA.

So, the one way is to include NULL as columns ref. to tableB like that :

select a.*, null col2
from tableA a
where not exists (select 1 from tableB b where b.key_ref = a.key_ref);

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Is this what you need:

select * from A
except
select * from B

?

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15389

If I understand your request:

You can use a LEFT OUTER JOIN operation so you'll get all rows in A not present in B (with condition in WHERE b.key_ref IS NULL)

Try this:

SELECT *
FROM a
LEFT OUTER JOIN b
ON a.key_ref = b.key_ref
WHERE b.key_ref IS NULL

Upvotes: 4

Related Questions