sql连接语法

本文介绍了sql连接语法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对编写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.