SQL连接以及它们在查询中列出的顺序

I've been on the net for about an hour now, but I can't seem to get a straight answer from other places. I have a few questions about joins that I hope you can answer. I'll try to be as simple as possible.

我已经上网了大约一个小时了,但我似乎无法从其他地方得到一个直接的答案。我有几个关于联接的问题,我希望你能回答。我会尝试尽可能简单。

Suppose I have two tables:

假设我有两个表:

A    B
-    -
1    3
2    4
3    5
4    6

Values (1, 2) are unique to A, (3, 4) are common, and (5, 6) are unique to B.

值(1,2)对于A是唯一的,(3,4)是常见的,并且(5,6)对于B是唯一的。

Now I understand the purpose of both inner and outer(left and right) joins. My questions relate to the ORDER in which they're written.

现在我理解内部和外部(左和右)连接的目的。我的问题与他们写的订单有关。

For example, would the following two queries produce the same results?

例如,以下两个查询是否会产生相同的结果?

SELECT * FROM A LEFT JOIN B ON A.A = B.B

SELECT * FROM B LEFT JOIN A ON B.B = A.A

Questions:

1. What determines the main table on the left that will have all entries listed? Is it the order that I write the tables on either side of that "LEFT JOIN" statement? Or is it based on the order of the tables in the ON condition?

   是什么决定左侧的主表将列出所有条目?这是我在“LEFT JOIN”语句两侧写表的顺序吗?或者它是基于ON条件下表的顺序?

2. Does the order of the ON condition matter? Or is it just used to link the tables together on a specific column for comparison?

   ON条件的顺序是否重要?或者它只是用于将表格链接在特定列上以进行比较?

3. What happens when I have more than two tables? Let's say there's a table C that I want to LEFT JOIN to the results of doing an A LEFT JOIN B:

   当我有两张桌子时会发生什么?假设有一个表格C,我想将LEFT JOIN连接到A LEFT JOIN B的结果:

   SELECT * FROM A 
   LEFT JOIN B ON A.A = B.B
   LEFT JOIN C ON ....
   

What goes there? Is it C.C = A.A? Or is it C.C = B.B?

那里有什么?是C.C = A.A?或者是C.C = B.B?

I guess the question is, what table specifically do any additional LEFT JOIN's get joined to?

我想问题是,什么表专门做任何额外的LEFT JOIN加入?

What's on that left side of the join?

加入的左侧是什么?

1 个解决方案

#1

Your two queries would not give you the same results.

您的两个查询不会给您相同的结果。

SELECT * FROM A LEFT JOIN B ON A.A = B.B

would give you

会给你的

1 null

2 null

3 3

4 4

while

SELECT * FROM B LEFT JOIN A ON B.B = A.A

would give you

会给你的

3 3

4 4

5 null

6 null

1. The table before LEFT JOIN is the table that will have all its results listed, no matter what is on the table that appears after the LEFT JOIN statement. The order that you write your ON conditions "on" have no influence on what is displayed in the results.

   LEFT JOIN之前的表是将列出其所有结果的表,无论LEFT JOIN语句之后出现的表是什么。您将ON条件“打开”的顺序对结果中显示的内容没有影响。

2. Order of the ON conditions have no effects on what is returned

   ON条件的顺序对返回的内容没有影响

3. You would do something like the following:

   您可以执行以下操作:

SELECT *
FROM A 
   LEFT JOIN B ON B.ID = A.ID
       LEFT JOIN C ON C.ID = A.ID

In the above statement, all values from table A will be displayed; along with any values from table B or C that are also present in table A.

在上面的语句中,将显示表A中的所有值;以及表A中也存在的表B或C中的任何值。

If you changed

如果你改变了

LEFT JOIN C ON C.ID = A.ID

to

LEFT JOIN C ON C.ID = B.ID

then only values that are present in all three tables would be displayed. You are essentially saying, "give me all information that is present in table A, plus any data corresponding to the same value in table B, plus any data corresponding to the same value in table C". If the data does not exist in table B, then any data in table C would also not be present in the results.

然后只显示所有三个表中存在的值。您实际上是在说“给我表A中的所有信息,加上表B中对应于相同值的任何数据,加上表C中对应于相同值的任何数据”。如果表B中不存在数据,则表C中的任何数据也不会出现在结果中。

LEFT JOIN C ON C.ID = A.ID 

can be visualized as

可视化为

On the other hand,

另一方面,

LEFT JOIN C ON C.ID = B.ID

can be seen as

可以看作是

Now I put down numbers instead of letters in the sketches, but in my Picassos above 1 = A, 2 = B, 3 = C

现在我在草图中放下数字而不是字母,但在我的毕加索上面1 = A,2 = B,3 = C