Search code examples
oracle11gleft-joininner-joinouter-joinright-join

Null values with all types of OUTER-JOIN is it me or is this normal? using Oracle db11g

I got a problem with something and I don't know if the thing I am doing is right.. or deadly wrong.

I am working with the outer-join concept. In my case with all the three of them. As I am typing my query's I get 50% viewable values en 50% null values. Is it normal to get this or are my query's wrong. I show you a couple of examples that I am working with.

Query 1. What I want to do is to get the names of the employees and address of the stores that they are working for.

select m.naam, w.adres, w.winkel_nummer
from medewerkers m, winkel w
where m.naam(+)= w.adres;

result: enter image description here

Query 2. I want to get all the phone numbers. In this case it's from the employees and from the members.

select s.telefoon_nummer, l.telefoon_nummer
from medewerkers s
full outer join lid l on s.telefoon_nummer = l.telefoon_nummer;

result: enter image description here

//note: with the full outer join I coudn't show the whole picture

The problem with both result are 'they are not combined'. Does that mean that I am writing a wrong query or is this normal?

I hope my question is not too vague. I cannot find the solution on the web

Thanks in advance

Solution

  • For #1, it looks like you're barking up the wrong tree.... why would you join the employee name to the address? That makes no sense.

    select m.naam, w.adres, w.winkel_nummer
from medewerkers m, winkel w
where m.STOREKEYFIELD(+)= w.STOREKEYFIELD;

    You have to join the two tables on the key for the store table to get anything meaningful back there.

    For #2, if you just want a full list of phone #s from 2 tables, use UNION:

    select s.telefoon_nummer
from medewerkers s
union
select  l.telefoon_nummer
from lid l

    There is no need to join anything unless you are expecting only things that match in both tables.