sql query between 2 tables

0

I have the person table:

id nombre sexo
 1   paco  1
 2   elena 2
 3   alien null

I have the table sex:

 id  genero 
 1   hombre  
 2   mujer 

Starting from the bas that is a DB of the client that I can not touch, I make the following query:

select p.nombre as name, g.genero as gender from persona p, sexo g 
where p.nombre = 'paco' and g.id = p.sexo ;   => esto funciona sin problemas

DOUBT:

select p.nombre as name, g.genero as gender from persona p, sexo g 
where p.nombre = 'alien' and g.id = p.sexo ;     

= > this DOES NOT WORK, I guess it will be because of the null it has but how can I     do to get results?

    
asked by sirdaiz 08.02.2017 в 12:10
source

2 answers

2

You have not explained in which sense one query works and the other does not. The truth is that, as Pablo explained, none of the 2 queries will work correctly because you have not correctly defined the conditions of the join.

The best way to avoid this type of problem is to use the most standard notation for joins to avoid this type of forgetting:

select p.nombre as name, 
       g.genero as gender
  from persona p
  join sexo g
    on g.id = p.sexo
where p.nombre = 'alien'

Then, changing this to handle the null becomes trivial, since you only need to change the join for a left join :

select p.nombre as name, 
       g.genero as gender
  from persona p
  left join sexo g
    on g.id = p.sexo
where p.nombre = 'alien'

It is best to avoid the notation of (+) for left joins if possible, since it is an old and abandoned syntax that Oracle itself does not recommend using.

    
answered by 08.02.2017 / 12:45
source
1

First, in your query you are not relating the two tables, it should be:

select p.nombre as name, g.genero as gender from persona p, sexo g 
where g.id = p.sexo and p.nombre = 'alien';

Second, to return results even if there are no values in the sex table for a id=null you have to use the operator (+):

select p.nombre as name, g.genero as gender from persona p, sexo g 
where g.id(+) = p.sexo and p.nombre = 'alien';

As you see, the operator is placed in the condition that relates the two tables, specifically on the side of the table where there may not be a specific value (null in this case).

    
answered by 08.02.2017 в 12:32