Do I misunderstand joins?

Question

I'm trying to learn the the ansi-92 SQL standard, but I don't seem to understand it completely (I'm new to the ansi-89 standard as well and in databases in general).

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

• There may be kingdoms without species nor families.
• There may be families without species nor kindgoms.
• There may be species without kingdom or families.

Why this may happen?

Say a biologist, finds a new species but he has not classified this into a kingdom or family, creates a new family that has no species and is not sure about what kingdom it should belong, etc.

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

I want to make a query that retrieves me every kingdom, every species, but not those families that have no species, so I make this.

    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;

What I think this would do is:

1. join family and species as a right join, so it brings every species, but not those families that have no species. So, if a species has not been classified into a family, it will appear with null on family.

2. Then, join the kingdom with the result as a left join, so it brings every kingdom, even if there are no families or species classified on that kingdom.

Am I wrong? Shouldn't this show me those species that have not been classified? If I do the inner query it brings what I want. Is there a problem where I'm grouping things?

Answer 1

You're right on your description of #1... the issue with your query is on step #2.

When you do a left join from kingdom to (family & species), you're requesting every kingdom, even if there's no matching (family & species)... however, this won't return you any (family & species) combination that doesn't have a matching kingdom.

A closer query would be:

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;

Notice that the left join was replaced with a full join...

however, this only returns families that are associated with a species... it doesn't return any families that are associated with kingdoms but not species.

After re-reading your question, this is actually want you wanted...

---

EDIT: On further thought, you could re-write your query like so:

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;

I think this would be preferrable, because you eliminate the RIGHT JOIN, which are usually frowned upon for being poor style... and the parenthesis, which can be tricky for people to parse correctly to determine what the result will be.