CODEWITHSUNDEEP
sqloracle-databaseouter-join
Marc
Marc

Reputation: 27

oracle sql full join with table a not in

I have to tables (tbla and tblb) with each one col (id):

select * from tbla;

ID
---
1
3
5#A

select * from tblb;

ID
---
2
3

Now I need a full join:

select a.id, b.id
from tbla a
full outer join tblb b on b.id = a.id;

ID  ID1
--------
1   
3   3
5#A 
    2

... but without entries containing a #-sign in tbla.id

select a.id, b.id
from tbla a
full outer join tblb b on b.id = a.id
where a.id not like '%#%';

ID  ID1
--------
1   
3   3

why is the entry with id 2 from tblb missing?

Upvotes: 1

Views: 96

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Because when you do a full outer join, columns on either side can end up with a NULL value.

Explicitly check for NULL:

select a.id, b.id
from tbla a
full outer join tblb b on b.id = a.id
where a.id not like '%#%' or a.id is null;

(Originally, I had suggested moving the logic to the on clause. Alas, the full outer join keeps records in both tables, even when no records match the condition. So, moving the condition to the on clause doesn't fix anything.)

Upvotes: 3

Frank Schmitt
Frank Schmitt

Reputation: 30845

You are doing a full join, but by specifying a.id in the where clause, you filter your result set afterwards.

To achieve what you want, you can either move the clause into the join condition:

select a.id, b.id
from tbla a
full outer join tblb b 
  on b.id = a.id
  and a.id not like '%#%';

Or you can use nvl:

select a.id, b.id
from tbla a
full outer join tblb b on b.id = a.id
where nvl(a.id, 'n/a') not like '%#%';

Or explicitly allow NULL values for a.id:

select a.id, b.id
from tbla a
full outer join tblb b on b.id = a.id
where (a.id is null or a.id not like '%#%');

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

When you do outer joins, you have to do your filtering in the from clause. If you do it in the where clause your join effectively becomes an inner join.

So change this:

full outer join tblb b on b.id = a.id
where a.id not like '%#%'

to this

full outer join tblb b on b.id = a.id
and a.id not like '%#%'

Upvotes: 0

Related Questions