Geek Answers Handbook

Do not I understand that joins?

I am trying to learn the ansi-92 SQL standard, but I do not seem to understand it completely (I am new to the ansi-89 standard, as well as to databases in general).

In my example, I have three kingdom tables - <family - species (biology classifications).

  • There may be kingdoms without species and families.
  • There may be families without species and courtesies.
  • There may be species without a kingdom or families.

Why could this happen?

Tell the biologist that he discovers a new species, but he did not classify it into a kingdom or family, creates a new family that has no species and does not know which kingdom it belongs to, etc.

here is the fiddle (see last request): http://sqlfiddle.com/#!4/015d1/3

I want to make a request that every kingdom, every species returns to me, but not those families that do not have species, so I do it.

    select *
    from reino r
         left join (
             familia f             
             right join especie e
                 on f.fnombre = e.efamilia
                 and f.freino = e.ereino
         ) on r.rnombre = f.freino 
           and r.rnombre = e.ereino;

I think this will do:

  • join the family and species as the right association, so it brings all kinds, but not those families that do not have species. So, if a species has not been classified in a family, it will appear to be family zero.

  • Then join the kingdom with the result as a left union, so it brings every kingdom, even if there are no families or species in this kingdom.

Am I really wrong? Does this not show me those species that have not been classified? If I make an internal request, it brings what I want. Is there a problem when I group things?

+5
source share
4 answers

โ„–1... 2.

left join ( ), , ( )... , ( ), .

:

select *
    from reino r
         full join (
             familia f             
             right join especie e
                 on f.fnombre = e.efamilia
                 and f.freino = e.ereino
         ) on r.rnombre = f.freino 
           and r.rnombre = e.ereino;

, left join full join...

, , , ... , , .

, , ...

: :

select *
from 
    especie e
    left join familia f 
        on f.fnombre = e.efamilia
        and f.freino = e.ereino
    full join reino r
        on r.rnombre = f.freino 
        and r.rnombre = e.ereino;

, , RIGHT JOIN, ... , , , , .

+2

, :

, [ OUTER JOIN] : It - - , , . , , , , . , :

SELECT SNO , PNO 
FROM   SP 
UNION  
SELECT SNO , 'nil' AS PNO 
FROM   S 
WHERE  SNO NOT IN ( SELECT SNO FROM SP )

:

SELECT SNO , COALESCE ( PNO , 'nil' ) AS PNO 
FROM   S NATURAL LEFT OUTER JOIN SP

: SQL : SQL C. J.

+2

, , , LEFT FULL. r.rnombre = f.freino ON:

select *
from reino r
     full join                      --- instead of LEFT JOIN
         familia f             
         right join especie e
             on f.fnombre = e.efamilia
             and f.freino = e.ereino
       on r.rnombre = e.ereino;
                                ---removed the:    r.rnombre = f.freino
+1

Try using this:

select * 
from reino r 
join especie e on (r.rnombre = e.ereino) 
join familia f on (f.freino = e.ereino and f.fnombre = e.efamilia)

Could it be that you changed the etamilia and enombre in the especie table?

0
source

More articles:


All Articles