I don't think it makes any difference to the database, but when joining tables, which order do you prefer to write the condition:
我不认为它对数据库有任何影响,但在连接表时,您更喜欢编写条件:
SELECT
...
FROM AAA
INNER JOIN BBB ON AAA.ID=BBB.ID
WHERE ...
OR
SELECT
...
FROM AAA
INNER JOIN BBB ON BBB.ID=AAA.ID
WHERE ...
13 个解决方案
#1
I always do
我经常做
From TABLE_A A
JOIN TABLE_B B ON A.Column = B.Column
#2
I prefer the second example (B=A) because in the join I am listing the criteria the determines which B rows should be included. In other words, I want all rows from B where "X" is true of B. This is also consistent when I need to check for criteria beyond just FKs. For example:
我更喜欢第二个例子(B = A),因为在连接中我列出了确定应包括哪些B行的标准。换句话说,我想要B中的所有行,其中“X”对于B是正确的。当我需要检查超出FK的标准时,这也是一致的。例如:
SELECT
some_columns
FROM
Table_A A
INNER JOIN Table_B B ON
B.a_id = A.a_id AND
B.active = 1
In my opinion it wouldn't have the same readability if I had:
在我看来,如果我有以下情况,它将具有相同的可读性:
1 = B.active
Also consider the cases where you're join criteria includes more than one table:
还要考虑您加入条件的情况包括多个表:
SELECT
some_columns
FROM
Table_A A
INNER JOIN Table_B B ON
B.a_id = A.a_id AND
B.active = 1
INNER JOIN Table_C C ON
C.a_id = A.a_id AND
C.b_id = B.b_id AND
C.category = 'Widgets'
To me that makes it very clear as to the criteria on which rows from C should be included.
对我而言,非常清楚应该包括来自C的行的标准。
#3
I prefer the second option where the most recently written table comes first.
我更喜欢第二个选项,其中最近写的表是第一个。
I think Linq requires it to be the other-way-round, though (option 1).
我认为Linq要求它是另一种方式(选项1)。
#4
It doesn't really matter, both are correct. My preference is for the second.
这并不重要,两者都是正确的。我的偏好是第二个。
My preference is based on the idea that table BBB is the table I'm adding into the result set, and the job at hand is tying columns (expressions) from the new table BBB to other columns already in the result set. It may make more sense in a different example:
我的偏好是基于表BBB是我正在添加到结果集中的表的想法,并且手头的工作是将新表BBB中的列(表达式)绑定到结果集中已有的其他列。在另一个例子中,它可能更有意义:
SELECT ...
FROM AAA a
JOIN BBB b ON (b.AAA_ID = a.ID)
JOIN CC c ON (c.AAA_ID = b.AAA_ID AND UPPER(c.FEE) IN ('FI','FO'))
JOIN DDD d ON (d.CC_ID = c.ID AND LEFT(d.DAH,2) = c.FEE)
Yes, this is an arbitarily complex example, but sometimes real code does get this complicated. When referencing multiple predicates in the join condition, I find it helpful when each predicate references first (on the left side) expressions from the table most recently joined.
是的,这是一个非常复杂的例子,但有时真正的代码会让这变得复杂。当在连接条件中引用多个谓词时,我发现当每个谓词首先(在左侧)引用最近加入的表时的表达式时,它会很有用。
There are other patterns that help as well, for example, when the primary key of each table is a single column named "ID" and foreign key columns are typically named PARENTTABLE_ID, such that when I see a construct like a.ID=b.ID, what I'm seeing is a pattern for a primary key joined to a primary key (a one-to-one relationship, which is not the normative pattern). And when I see b.FOREIGN_ID = c.FOREIGN_ID, what I'm seeing is a foreign key being joined to a foreign key. Again, not the usual pattern, indicating this may be a many-to-many join, or maybe a shortcut join for performance. The usual pattern I'm looking for in a parent-child join is like child.PARENT_ID = parent.ID
还有其他模式也有帮助,例如,当每个表的主键是名为“ID”的单个列,而外键列通常命名为PARENTTABLE_ID时,这样当我看到类似a.ID = b的结构时。 ID,我看到的是一个主键加入主键的模式(一对一的关系,这不是规范模式)。当我看到b.FOREIGN_ID = c.FOREIGN_ID时,我看到的是外键加入外键。同样,不是通常的模式,表明这可能是多对多连接,或者可能是性能的快捷连接。我在父子连接中寻找的通常模式就像child.PARENT_ID = parent.ID
These patterns aren't right or wrong, just a preference. I find that these patterns don't make code that is right look pretty, but does make code that is "odd" stand out.
这些模式不对或错,只是偏好。我发现这些模式不会使代码看起来很漂亮,但确实使得“奇怪”的代码脱颖而出。
#5
I prefer the first option. You're going from A to B, so the order of the fields seems more appropriate.
我更喜欢第一种选择。你从A到B,所以字段的顺序似乎更合适。
#6
I don't think it really makes a difference, but I prefer
我不认为这确实有所作为,但我更喜欢
INNER JOIN BBB ON AAA.ID=BBB.ID
because it is consistent with linq.
因为它与linq一致。
#7
Another mild voice for AAA.ID = BBB.ID. It seems to make more sense to me, but it doesn't really matter.
另一个温和的声音为AAA.ID = BBB.ID.这对我来说似乎更有意义,但这并不重要。
On a tangentially related note, however, I've recently begun questioning how I write equality tests. I've always preferred:
然而,在一个切线相关的说明中,我最近开始质疑如何编写相等测试。我一直都喜欢:
If ValueInQuestion = TestValue Then
...
That is,
If fullMoonsThisMonth = 2 Then
...
In others' code I've frequently seen this test reversed, and it bugged me for a while. I came to realize that my preference is based soleley on which formulation sounds "better" in English, and that there is sometimes a good reason for putting the invariant vlaue on the left. In languages with only one operator for both equality testing and assignment (such as VB, in case you didn't recognize the samples...), the compiler will then stop you from accidentally making an assignment when you meant to do a test.
在其他人的代码中,我经常看到这个测试被颠倒过来,它让我感到困扰了一段时间。我开始意识到我的偏好是基于哪种配方在英语中听起来“更好”,并且有时候有充分的理由将不变的值放在左边。在只有一个运算符进行相等性测试和赋值的语言中(例如VB,如果您没有识别样本......),编译器将阻止您在意图进行测试时意外地进行赋值。
#8
No difference, but I'd go for "AAA INNER JOIN BBB ON AAA.ID=BBB.ID" for clarity (but with aliases)
没有区别,但为了清晰起见,我会选择“AAA INNER JOB BBB在AAA.ID = BBB.ID”(但是使用别名)
#9
I use the first syntax AAA.ID = BBB.ID. It makes code easier to read in my opinion as the join participants follow the table order.
我使用第一个语法AAA.ID = BBB.ID.在我看来,它使代码更容易阅读,因为加入参与者遵循表顺序。
#10
I tend to use both as it makes no difference at all.
我倾向于使用两者,因为它没有任何区别。
#11
It doesn't matter which order you write the join in as long as the tables you're referencing have already been mentioned in the query.
只要您引用的表已在查询中提及,您编写连接的顺序无关紧要。
I personally prefer to list the most recent table second (option 1). This convention helps because I have decided to always use a LEFT OUTER JOIN when needed (rather than a RIGHT OUTER JOIN) and I don't have to think about which table is going to be on the right or the left.
我个人更喜欢列出最新的表格第二(选项1)。这个约定很有帮助,因为我决定在需要的时候总是使用LEFT OUTER JOIN(而不是RIGHT OUTER JOIN),而且我不必考虑哪个表将在右侧或左侧。
#12
Let's remember here that LINQ is not SQL, so why is it even being mentioned here? SQL questions should NOT be answered with LINQ answers. That is just odd and quite irrelevant in my opinion!!
让我们记住LINQ不是SQL,为什么它在这里被提到呢?不应该使用LINQ答案来回答SQL问题。在我看来,这只是奇怪而且无关紧要!!
Both examples are acceptable. I prefer the second example, as most SQL developers would. It also depends on the type of SQL JOIN you want to use. The question title is simply for "JOIN" but your example uses an INNER JOIN.
两个例子都可以接受。我更喜欢第二个例子,就像大多数SQL开发人员一样。它还取决于您要使用的SQL JOIN的类型。问题标题仅用于“JOIN”,但您的示例使用INNER JOIN。
In this instance, for an INNER JOIN, it does not matter. But keep in mind, that both examples will produce a HASH MATCHED JOIN, which is not ideal when dealing with indexes. A Loop Join is much more efficient. Just make sure you consider your indexes as Hash Joins are an indicator of inefficient indexing.
在这种情况下,对于INNER JOIN,无所谓。但请记住,两个示例都会生成HASH MATCHED JOIN,这在处理索引时并不理想。循环连接效率更高。只需确保将索引视为哈希联接是低效索引的指标。
#13
Couldn't it make a performance difference depending on how the two tables are indexed and on what field they are joined?
难道它不会产生性能差异取决于两个表的索引方式以及它们加入的字段?
The query optimizer in some DB engines might be able to do the right thing regardless of what order you specify, but only some time with the query plan and testing would answer for sure.
某些数据库引擎中的查询优化器可能能够做正确的事情,无论您指定的是什么顺序,但只有一些时间与查询计划和测试肯定会回答。
#1
I always do
我经常做
From TABLE_A A
JOIN TABLE_B B ON A.Column = B.Column
#2
I prefer the second example (B=A) because in the join I am listing the criteria the determines which B rows should be included. In other words, I want all rows from B where "X" is true of B. This is also consistent when I need to check for criteria beyond just FKs. For example:
我更喜欢第二个例子(B = A),因为在连接中我列出了确定应包括哪些B行的标准。换句话说,我想要B中的所有行,其中“X”对于B是正确的。当我需要检查超出FK的标准时,这也是一致的。例如:
SELECT
some_columns
FROM
Table_A A
INNER JOIN Table_B B ON
B.a_id = A.a_id AND
B.active = 1
In my opinion it wouldn't have the same readability if I had:
在我看来,如果我有以下情况,它将具有相同的可读性:
1 = B.active
Also consider the cases where you're join criteria includes more than one table:
还要考虑您加入条件的情况包括多个表:
SELECT
some_columns
FROM
Table_A A
INNER JOIN Table_B B ON
B.a_id = A.a_id AND
B.active = 1
INNER JOIN Table_C C ON
C.a_id = A.a_id AND
C.b_id = B.b_id AND
C.category = 'Widgets'
To me that makes it very clear as to the criteria on which rows from C should be included.
对我而言,非常清楚应该包括来自C的行的标准。
#3
I prefer the second option where the most recently written table comes first.
我更喜欢第二个选项,其中最近写的表是第一个。
I think Linq requires it to be the other-way-round, though (option 1).
我认为Linq要求它是另一种方式(选项1)。
#4
It doesn't really matter, both are correct. My preference is for the second.
这并不重要,两者都是正确的。我的偏好是第二个。
My preference is based on the idea that table BBB is the table I'm adding into the result set, and the job at hand is tying columns (expressions) from the new table BBB to other columns already in the result set. It may make more sense in a different example:
我的偏好是基于表BBB是我正在添加到结果集中的表的想法,并且手头的工作是将新表BBB中的列(表达式)绑定到结果集中已有的其他列。在另一个例子中,它可能更有意义:
SELECT ...
FROM AAA a
JOIN BBB b ON (b.AAA_ID = a.ID)
JOIN CC c ON (c.AAA_ID = b.AAA_ID AND UPPER(c.FEE) IN ('FI','FO'))
JOIN DDD d ON (d.CC_ID = c.ID AND LEFT(d.DAH,2) = c.FEE)
Yes, this is an arbitarily complex example, but sometimes real code does get this complicated. When referencing multiple predicates in the join condition, I find it helpful when each predicate references first (on the left side) expressions from the table most recently joined.
是的,这是一个非常复杂的例子,但有时真正的代码会让这变得复杂。当在连接条件中引用多个谓词时,我发现当每个谓词首先(在左侧)引用最近加入的表时的表达式时,它会很有用。
There are other patterns that help as well, for example, when the primary key of each table is a single column named "ID" and foreign key columns are typically named PARENTTABLE_ID, such that when I see a construct like a.ID=b.ID, what I'm seeing is a pattern for a primary key joined to a primary key (a one-to-one relationship, which is not the normative pattern). And when I see b.FOREIGN_ID = c.FOREIGN_ID, what I'm seeing is a foreign key being joined to a foreign key. Again, not the usual pattern, indicating this may be a many-to-many join, or maybe a shortcut join for performance. The usual pattern I'm looking for in a parent-child join is like child.PARENT_ID = parent.ID
还有其他模式也有帮助,例如,当每个表的主键是名为“ID”的单个列,而外键列通常命名为PARENTTABLE_ID时,这样当我看到类似a.ID = b的结构时。 ID,我看到的是一个主键加入主键的模式(一对一的关系,这不是规范模式)。当我看到b.FOREIGN_ID = c.FOREIGN_ID时,我看到的是外键加入外键。同样,不是通常的模式,表明这可能是多对多连接,或者可能是性能的快捷连接。我在父子连接中寻找的通常模式就像child.PARENT_ID = parent.ID
These patterns aren't right or wrong, just a preference. I find that these patterns don't make code that is right look pretty, but does make code that is "odd" stand out.
这些模式不对或错,只是偏好。我发现这些模式不会使代码看起来很漂亮,但确实使得“奇怪”的代码脱颖而出。
#5
I prefer the first option. You're going from A to B, so the order of the fields seems more appropriate.
我更喜欢第一种选择。你从A到B,所以字段的顺序似乎更合适。
#6
I don't think it really makes a difference, but I prefer
我不认为这确实有所作为,但我更喜欢
INNER JOIN BBB ON AAA.ID=BBB.ID
because it is consistent with linq.
因为它与linq一致。
#7
Another mild voice for AAA.ID = BBB.ID. It seems to make more sense to me, but it doesn't really matter.
另一个温和的声音为AAA.ID = BBB.ID.这对我来说似乎更有意义,但这并不重要。
On a tangentially related note, however, I've recently begun questioning how I write equality tests. I've always preferred:
然而,在一个切线相关的说明中,我最近开始质疑如何编写相等测试。我一直都喜欢:
If ValueInQuestion = TestValue Then
...
That is,
If fullMoonsThisMonth = 2 Then
...
In others' code I've frequently seen this test reversed, and it bugged me for a while. I came to realize that my preference is based soleley on which formulation sounds "better" in English, and that there is sometimes a good reason for putting the invariant vlaue on the left. In languages with only one operator for both equality testing and assignment (such as VB, in case you didn't recognize the samples...), the compiler will then stop you from accidentally making an assignment when you meant to do a test.
在其他人的代码中,我经常看到这个测试被颠倒过来,它让我感到困扰了一段时间。我开始意识到我的偏好是基于哪种配方在英语中听起来“更好”,并且有时候有充分的理由将不变的值放在左边。在只有一个运算符进行相等性测试和赋值的语言中(例如VB,如果您没有识别样本......),编译器将阻止您在意图进行测试时意外地进行赋值。
#8
No difference, but I'd go for "AAA INNER JOIN BBB ON AAA.ID=BBB.ID" for clarity (but with aliases)
没有区别,但为了清晰起见,我会选择“AAA INNER JOB BBB在AAA.ID = BBB.ID”(但是使用别名)
#9
I use the first syntax AAA.ID = BBB.ID. It makes code easier to read in my opinion as the join participants follow the table order.
我使用第一个语法AAA.ID = BBB.ID.在我看来,它使代码更容易阅读,因为加入参与者遵循表顺序。
#10
I tend to use both as it makes no difference at all.
我倾向于使用两者,因为它没有任何区别。
#11
It doesn't matter which order you write the join in as long as the tables you're referencing have already been mentioned in the query.
只要您引用的表已在查询中提及,您编写连接的顺序无关紧要。
I personally prefer to list the most recent table second (option 1). This convention helps because I have decided to always use a LEFT OUTER JOIN when needed (rather than a RIGHT OUTER JOIN) and I don't have to think about which table is going to be on the right or the left.
我个人更喜欢列出最新的表格第二(选项1)。这个约定很有帮助,因为我决定在需要的时候总是使用LEFT OUTER JOIN(而不是RIGHT OUTER JOIN),而且我不必考虑哪个表将在右侧或左侧。
#12
Let's remember here that LINQ is not SQL, so why is it even being mentioned here? SQL questions should NOT be answered with LINQ answers. That is just odd and quite irrelevant in my opinion!!
让我们记住LINQ不是SQL,为什么它在这里被提到呢?不应该使用LINQ答案来回答SQL问题。在我看来,这只是奇怪而且无关紧要!!
Both examples are acceptable. I prefer the second example, as most SQL developers would. It also depends on the type of SQL JOIN you want to use. The question title is simply for "JOIN" but your example uses an INNER JOIN.
两个例子都可以接受。我更喜欢第二个例子,就像大多数SQL开发人员一样。它还取决于您要使用的SQL JOIN的类型。问题标题仅用于“JOIN”,但您的示例使用INNER JOIN。
In this instance, for an INNER JOIN, it does not matter. But keep in mind, that both examples will produce a HASH MATCHED JOIN, which is not ideal when dealing with indexes. A Loop Join is much more efficient. Just make sure you consider your indexes as Hash Joins are an indicator of inefficient indexing.
在这种情况下,对于INNER JOIN,无所谓。但请记住,两个示例都会生成HASH MATCHED JOIN,这在处理索引时并不理想。循环连接效率更高。只需确保将索引视为哈希联接是低效索引的指标。
#13
Couldn't it make a performance difference depending on how the two tables are indexed and on what field they are joined?
难道它不会产生性能差异取决于两个表的索引方式以及它们加入的字段?
The query optimizer in some DB engines might be able to do the right thing regardless of what order you specify, but only some time with the query plan and testing would answer for sure.
某些数据库引擎中的查询优化器可能能够做正确的事情,无论您指定的是什么顺序,但只有一些时间与查询计划和测试肯定会回答。