我会误会加入吗? [英] Do I misunderstand joins?

问题描述

我正在尝试学习ansi-92 SQL标准，但是我似乎并没有完全理解它(我也是ansi-89标准的新手，并且在数据库中也是新手.)

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).

• 可能有没有物种或家庭的王国.
• 可能有没有物种或亲缘关系的家庭.
• 可能有没有王国或家庭的物种.

为什么会发生这种情况?

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.

这是一个小提琴(请参阅上一个查询): http://sqlfiddle.com/# ！4/015d1/3

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;

我认为这将会是:

1. 作为正确的联接加入家族和物种，因此它带来了每个物种，但没有带来那些没有物种的家族.因此，如果一个物种没有被归为一个家族，那么它将在家族中显示为空.

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.

然后，以左连接的形式加入王国，因此即使没有该王国的任何家族或物种，它也带给每个王国.

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?

推荐答案

您对#1的描述是正确的...查询问题在步骤#2上.

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

当您执行从王国到(家庭和物种)的left join时，您将请求每个王国，即使没有匹配的(家庭和物种)...但是，这不会给您任何回报(家庭和物种)组合没有匹配的王国.

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.

更仔细的查询是:

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 ...

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

但是，这只会返回与物种相关的科目……不会返回与王国相关但不与物种相关的任何科目.

重新阅读您的问题后，这实际上是您想要的...

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

经进一步思考，您可以像这样重新编写查询:

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;

我认为这是可取的，因为您消除了RIGHT JOIN，后者通常因样式不佳而皱眉...和括号，这对于人们正确解析以确定结果将是棘手的

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.

这篇关于我会误会加入吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！