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屋！