THe below 2 queries performs the same operation, but wondering which would be the fastest and most preferable?
以下2个查询执行相同的操作,但想知道哪个是最快和最优选的?
NUM is the primary key on table1 & table2...
NUM是table1和table2上的主键...
select *
from table1 tb1,
table2 tb2
where tb1.num = tb2.num
select *
from table1 tb1
inner join
table2 tb2
on tb1.num = tb2.num
4 个解决方案
#1
1
The 2 SQL statements are equivalent. You can look at the execution plan to confirm. As a rule, given 2 SQL statements which affect/return the same rows in the same way, the server is free to execute them the same way.
2个SQL语句是等效的。您可以查看执行计划以进行确认。通常,给定2个以相同方式影响/返回相同行的SQL语句,服务器可以以相同的方式*执行它们。
#2
1
They're equivalent queries - both are inner joins, but the first uses an older, implicit join syntax. Your database should execute them in exactly the same way.
它们是等效的查询 - 都是内连接,但第一个使用较旧的隐式连接语法。您的数据库应以完全相同的方式执行它们。
If you're unsure, you could always use the SQL Management Studio to view and compare the execution plans of both queries.
如果您不确定,可以始终使用SQL Management Studio查看和比较两个查询的执行计划。
#3
1
They are the same query. The first is an older alternate syntax, but they both mean do an inner join.
它们是相同的查询。第一种是较旧的替代语法,但它们都意味着进行内连接。
You should avoid using the older syntax. It's not just readability, but as you build more complex queries, there are things that you simply can't do with the old syntax. Additionally, the old syntax is going through a slow process of being phased out, with the equivalent outer join syntax marked as deprecated in most products, and iirc dropped already in at least one.
您应该避免使用旧语法。这不仅仅是可读性,但是当您构建更复杂的查询时,有些事情您根本无法使用旧语法。此外,旧语法经历了逐步淘汰的缓慢过程,在大多数产品中标记为弃用的等效外连接语法,并且iirc已经至少丢失了一个。
#4
0
The first example is what I have seen referred to as an Oracle Join. As mentioned already there appears to be little performance difference. I prefer the second example from a readability standpoint because it separates join conditions from filter conditions.
第一个例子是我所看到的被称为Oracle Join的例子。如前所述,似乎没有什么性能差异。从可读性的角度来看,我更喜欢第二个例子,因为它将连接条件与过滤条件分开。
#1
1
The 2 SQL statements are equivalent. You can look at the execution plan to confirm. As a rule, given 2 SQL statements which affect/return the same rows in the same way, the server is free to execute them the same way.
2个SQL语句是等效的。您可以查看执行计划以进行确认。通常,给定2个以相同方式影响/返回相同行的SQL语句,服务器可以以相同的方式*执行它们。
#2
1
They're equivalent queries - both are inner joins, but the first uses an older, implicit join syntax. Your database should execute them in exactly the same way.
它们是等效的查询 - 都是内连接,但第一个使用较旧的隐式连接语法。您的数据库应以完全相同的方式执行它们。
If you're unsure, you could always use the SQL Management Studio to view and compare the execution plans of both queries.
如果您不确定,可以始终使用SQL Management Studio查看和比较两个查询的执行计划。
#3
1
They are the same query. The first is an older alternate syntax, but they both mean do an inner join.
它们是相同的查询。第一种是较旧的替代语法,但它们都意味着进行内连接。
You should avoid using the older syntax. It's not just readability, but as you build more complex queries, there are things that you simply can't do with the old syntax. Additionally, the old syntax is going through a slow process of being phased out, with the equivalent outer join syntax marked as deprecated in most products, and iirc dropped already in at least one.
您应该避免使用旧语法。这不仅仅是可读性,但是当您构建更复杂的查询时,有些事情您根本无法使用旧语法。此外,旧语法经历了逐步淘汰的缓慢过程,在大多数产品中标记为弃用的等效外连接语法,并且iirc已经至少丢失了一个。
#4
0
The first example is what I have seen referred to as an Oracle Join. As mentioned already there appears to be little performance difference. I prefer the second example from a readability standpoint because it separates join conditions from filter conditions.
第一个例子是我所看到的被称为Oracle Join的例子。如前所述,似乎没有什么性能差异。从可读性的角度来看,我更喜欢第二个例子,因为它将连接条件与过滤条件分开。