sql 连接语法 [英] sql join syntax

问题描述

我对编写 sql 有点陌生，我有一个关于连接的问题.这是一个选择示例:

I'm kind of new to writing sql and I have a question about joins. Here's an example select:

select bb.name from big_box bb, middle_box mb, little_box lb
where lb.color = 'green' and lb.parent_box = mb and mb.parent_box = bb;

假设我正在寻找所有大盒子的名称，这些大盒子在它们内部某处嵌套了一个绿色的小盒子.如果我理解正确的话，上面的语法是另一种获得与使用 'join' 关键字相同的结果的方法.

So let's say that I'm looking for the names of all the big boxes that have nested somewhere inside them a little box that's green. If I understand correctly, the above syntax is another way of getting the same results that we could get by using the 'join' keyword.

问题:上面的 select 语句对于它正在执行的任务是否有效?如果没有，有什么更好的方法来做到这一点?语句是连接的语法糖还是它实际上在做其他事情?

Questions: is the above select statement efficient for the task it's doing? If not, what is a better way to do it? Is the statement syntactic sugar for a join or is it actually doing something else?

如果您有关于该主题的任何好材料的链接，我很乐意阅读它，但由于我不知道这种技术的确切名称，因此我很难在谷歌上搜索它.

If you have links to any good material on the subject I'd gladly read it, but since I don't know exactly what this technique is called I'm having trouble googling it.

推荐答案

您正在使用隐式连接语法.这等效于使用 JOIN 关键字，但最好完全避免这种语法，而是使用显式连接:

You are using implicit join syntax. This is equivalent to using the JOIN keyword but it is a good idea to avoid this syntax completely and instead use explicit joins:

SELECT bb.name
FROM big_box bb
JOIN middle_box mb ON mb.parent_box = bb.id
JOIN little_box lb ON lb.parent_box = mb.id
WHERE lb.color = 'green'

您还缺少连接条件中的列名.我已经猜到该列名为 id.

You were also missing the column name in the join condition. I have guessed that the column is called id.

如果表被正确索引，这种类型的查询应该是有效的.特别是应该有对连接条件的外键约束和 little_box.color 上的索引.

This type of query should be efficient if the tables are indexed correctly. In particular there should be foreign key constraints on the join conditions and an index on little_box.color.

您的查询的一个问题是，如果单个框中有多个绿色框，您将返回重复的行.可以通过在 SELECT 后添加 DISTINCT 来删除这些重复项.

An issue with your query is that if there are multiple green boxes inside a single box you will get duplicate rows returned. These duplicates can be removed by addding DISTINCT after SELECT.

这篇关于sql 连接语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！