SQL性能：嵌套SELECT与INNER JOIN [英] SQL performance: Nested SELECT vs. INNER JOIN

问题描述

Hello All -


我想知道是否有人对

性能视角哪个更好有所了解：嵌套的Select语句或者内部加入。

例如，我可以执行以下任一操作：


SELECT supplier_name

FROM供应商

WHERE supplier_ID IN（SELECT supplier_ID FROM products WHERE

product_ID = 22）;


VS.


SELECT supplier.supplier_name

FROM供应商INNER JOIN产品ON supplier.supplier_ID =

products.supplier_ID

WHERE products.product_ID = 22;

哪个更快？


谢谢。

Brian

解决方案

2003年9月25日11:37:05 -0700 comp.databases.ms-access，
br ********* @ yahoo.com （Brian）写道：

大家好 -

我是想知道是否有人有任何想法从性能角度来看更好：嵌套的Select语句或内部联接。

例如，我可以执行以下任一操作：

（下面的空气代码，一定要发布你的发现:)


Dim strSQL（1到2）As String

Dim i只要j，长期

Dim varStart作为变体

Dim rs As Recordset

Dim db作为数据库


strSQL（1）=" SELECT supplier_name" &安培; _

来自供应商 &安培; _

" WHERE supplier_ID IN（SELECT supplier_ID FROM products WHERE" _

" product_ID = 22）;"


strSQL（2）=" SELECT supplier.supplier_name" &安培; _

来自供应商INNER JOIN产品ON supplier.supplier_ID =" &安培; _

" products.supplier_ID" &安培; _

" WHERE products.product_ID = 22;


设置db = currentdb

对于i = 1到2

varStart = now（）

for j = 1 to 1000

set rs = db.openrecordset（strSQL（i），dbopensnapshot）

rs.close

set rs = nothing

next j

debug.print" Method" &安培;我和我" " &安培; DateDiff（" s"，varStart，Now（））

下一个我

设置db =无


-

A）bort，R）etry，I）用大锤子影响。

" Brian" <峰; br ********* @ yahoo.com>在留言中写道

新闻：是************************** @ posting.google.c om ...

Hello All -

我想知道是否有人对从性能角度来看哪个更好有所了解：嵌套的Select语句或内部联接。 br />
例如，我可以执行以下任一操作：

SELECT supplier_name
FROM供应商
WHERE supplier_ID IN（SELECT supplier_ID FROM products WHERE
product_ID = 22）;

VS.

SELECT supplier.supplier_name
FROM供应商INNER JOIN产品ON supplier.supplier_ID =
products.supplier_ID
WHERE products.product_ID = 22;

哪个更快？

试试看看吧！一个聪明的优化器应该生成相同的查询计划。

然而，我经常看到的一个错误是子查询为每个供应商生成多行的
，（对于例如，如果您想查看销售某种产品​​类型的所有

供应商）。在这种情况下，查询1优于
，因为查询处理器可以在找到第一个后立即停止搜索行。 < rant>一些初学者会使用查询2并放入一个

DISTINCT子句来消除重复。显然，这是一件糟糕的，糟糕的事情，但我已经多次看过它了。不仅是初学者，而且是由b / b
认为经验丰富的开发人员。< / rant>


BTW，无论如何，我会使用EXISTS代替IN ，所以查询1将

成为：


SELECT s.supplier_name

来自供应商AS s

在哪里存在

（

SELECT * FROM产品AS p

WHERE p.product_ID = 22

AND p .Supplier_ID = s.Supplier_ID

）


但是，正如我所说，查询2也一样好。

" John Winterbottom" <乔*************** @ hotmail.com>在消息中写道

news：bk ************ @ ID-185006.news.uni-berlin.de ...

" ;布赖恩" <峰; br ********* @ yahoo.com>在消息中写道
新闻：是************************** @ posting.google.c om ...

大家好 -

我想知道是否有人对从性能角度来看哪个更好有所了解：嵌套的Select语句或内部联接。

例如，我可以执行以下任一操作：

SELECT supplier_name
FROM供应商
WHERE supplier_ID IN（SELECT supplier_ID FROM products WHERE
product_ID = 22 ）;

VS.

SELECT supplier.supplier_name
FROM供应商INNER JOIN产品ON supplier.supplier_ID =
products.supplier_ID
在哪里products.product_ID = 22;

哪个更快？

只是添加到我之前提到的内容，这两个示例都使用

Northwind数据库查找销售海鲜产品的供应商。


正确的方式

----- --------------------------------

选择s.Com panyName

来自供应商

如果存在

（

select * from products p

内部联接类别c on p.CategoryID = c.CategoryID

其中c.CategoryName =" Seafood"

和p.SupplierID = s.SupplierID

）

----------------------------------- -----

错误的方式

-------------------------- ----------------------

SELECT DISTINCT Suppliers.CompanyName

FROM Suppliers INNER JOIN（分类INNER） JOIN产品ON

Categories.CategoryID = Products.CategoryID）ON Suppliers.SupplierID =

Products.SupplierID

WHERE（（（Categories.CategoryName ）=海鲜））

---------------------------------- --------------

Hello All -

I am wondering if anyone has any thoughts on which is better from a
performance perspective: a nested Select statement or an Inner Join.
For example, I could do either of the following:

SELECT supplier_name
FROM supplier
WHERE supplier_ID IN (SELECT supplier_ID FROM products WHERE
product_ID =22);

VS.

SELECT supplier.supplier_name
FROM supplier INNER JOIN products ON supplier.supplier_ID =
products.supplier_ID
WHERE products.product_ID = 22;
Which is faster?

Thanks.
Brian

解决方案

On 25 Sep 2003 11:37:05 -0700 in comp.databases.ms-access,
br*********@yahoo.com (Brian) wrote:

Hello All -

I am wondering if anyone has any thoughts on which is better from a
performance perspective: a nested Select statement or an Inner Join.
For example, I could do either of the following:

(air code below, be sure to post your findings :)

Dim strSQL(1 to 2) As String
Dim i as long, j as long
Dim varStart As variant
Dim rs As Recordset
Dim db As Database

strSQL(1)="SELECT supplier_name " & _
"FROM supplier " & _
"WHERE supplier_ID IN (SELECT supplier_ID FROM products WHERE " & _
"product_ID =22);"

strSQL(2)="SELECT supplier.supplier_name " & _
"FROM supplier INNER JOIN products ON supplier.supplier_ID = " & _
"products.supplier_ID " & _
"WHERE products.product_ID = 22; "

set db = currentdb
For i = 1 to 2
varStart=now()
for j=1 to 1000
set rs=db.openrecordset(strSQL(i),dbopensnapshot)
rs.close
set rs=nothing
next j
debug.print "Method " & i & " " & DateDiff("s",varStart,Now())
Next i
set db=nothing

--
A)bort, R)etry, I)nfluence with large hammer.

"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello All -

I am wondering if anyone has any thoughts on which is better from a
performance perspective: a nested Select statement or an Inner Join.
For example, I could do either of the following:

SELECT supplier_name
FROM supplier
WHERE supplier_ID IN (SELECT supplier_ID FROM products WHERE
product_ID =22);

VS.

SELECT supplier.supplier_name
FROM supplier INNER JOIN products ON supplier.supplier_ID =
products.supplier_ID
WHERE products.product_ID = 22;
Which is faster?

Try them and see! A clever optimizer should produce identical query plans.
However, a mistake that I''ve seen very often is where the subquery generates
multiple rows for each supplier, (for example, if you wanted to see all
suppliers that sold a certain product type). In this case query 1 is
superior, because the query processor can stop searching for rows as soon as
it finds the first one. <rant>Some beginners would use query 2 and put a
DISTINCT clause in to eliminate duplicates. Obviously, this is a bad, bad
thing to do, yet I''ve seen it done many times. Not only by beginners, but by
supposedly experienced developers.</rant>

BTW, I would, in any case, use EXISTS instead of IN, so query 1 would
become:

SELECT s.supplier_name
FROM supplier AS s
WHERE EXISTS
(
SELECT * FROM products AS p
WHERE p.product_ID =22
AND p.Supplier_ID = s.Supplier_ID
)

but, as I said, query 2 would be just as good.

"John Winterbottom" <jo***************@hotmail.com> wrote in message
news:bk************@ID-185006.news.uni-berlin.de...

"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello All -

I am wondering if anyone has any thoughts on which is better from a
performance perspective: a nested Select statement or an Inner Join.
For example, I could do either of the following:

SELECT supplier_name
FROM supplier
WHERE supplier_ID IN (SELECT supplier_ID FROM products WHERE
product_ID =22);

VS.

SELECT supplier.supplier_name
FROM supplier INNER JOIN products ON supplier.supplier_ID =
products.supplier_ID
WHERE products.product_ID = 22;
Which is faster?

Just to add to what I was syaing earlier, these two examples both use the
Northwind database to find suppliers that sell seafood products.

The right way
-------------------------------------
select s.CompanyName
from Suppliers as s
where exists
(
select * from products p
inner join categories c on p.CategoryID = c.CategoryID
where c.CategoryName = "Seafood"
and p.SupplierID=s.SupplierID
)
----------------------------------------
The wrong way
------------------------------------------------
SELECT DISTINCT Suppliers.CompanyName
FROM Suppliers INNER JOIN (Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID) ON Suppliers.SupplierID =
Products.SupplierID
WHERE (((Categories.CategoryName)="Seafood"))
------------------------------------------------

这篇关于SQL性能：嵌套SELECT与INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！