将嵌套sql where-in模式转换为连接

Question

我有一个查询返回给我正确的数据，但作为一名开发人员而不是DBA，我想知道是否有任何理由将其转换为联接而不是嵌套选择，如果有，它将是什么样子。

我目前的代码是

select * from adjustments where store_id in (
    select id from stores where original_id = (
        select original_id from stores where name ='abcd'))

任何有关更好地使用joins的参考也将不胜感激。

Answer 1

除了任何可能的性能改进之外，我发现下面的内容更容易阅读。

SELECT  * 
FROM    adjustments a
        INNER JOIN stores s ON s.id = a.store_id
        INNER JOIN stores s2 ON s2.original_id = s.original_id
WHERE   s.name = 'abcd'        

显示我在忽略original_id时的原始错误的测试脚本

DECLARE @Adjustments TABLE (store_id INTEGER)
DECLARE @Stores TABLE (id INTEGER, name VARCHAR(32), original_id INTEGER)

INSERT INTO @Adjustments VALUES (1), (2), (3)
INSERT INTO @Stores VALUES (1, 'abcd', 1), (2, '2', 1), (3, '3', 1)

/* 
   OP's Original statement returns store_id's 1, 2 & 3 
   due to original_id being all the same
*/
SELECT  * FROM @Adjustments WHERE store_id IN (
  SELECT id FROM @Stores WHERE original_id = (
    SELECT original_id FROM @Stores WHERE name ='abcd'))

/* 
   Faulty first attempt with removing original_id from the equation
   only returns store_id 1
*/
SELECT  a.store_id
FROM    @Adjustments a
        INNER JOIN @Stores s ON s.id = a.store_id
WHERE   s.name = 'abcd'        

Answer 2

略有不同的方法：

select * from adjustments a where exists
(select null from stores s1, stores s2 
where a.store_id = s1.id and s1.original_id = s2.original_id and s2.name ='abcd')

Answer 3

比如微软的here

许多包含子查询的Transact-SQL语句也可以表示为联接。其他问题只能用子查询提出。在Transact-SQL中，包含子查询的语句和不包含子查询的语义等效版本之间通常没有性能差异。但是，在某些必须检查是否存在的情况下，连接会产生更好的性能。否则，必须为外部查询的每个结果处理嵌套查询，以确保消除重复项。在这种情况下，连接方法会产生更好的结果。

您的情况恰好是连接和子查询提供相同性能的情况。

子查询不能转换为“简单”连接的例子：

  select Country,TR_Country.Name as Country_Translated_Name,TR_Country.Language_Code
    from Country
JOIN TR_Country ON Country.Country=Tr_Country.Country
    where country =
       (select top 1 country 
             from Northwind.dbo.Customers C 
                          join
                  Northwind.dbo.Orders O
                  on C.CustomerId = O.CustomerID
        group by country
        order by count(*)) 

正如您所看到的，每个国家都可以有不同的名称翻译，所以我们不能只连接和统计记录(在这种情况下，翻译数量较大的国家将有更多的记录计数)

当然，您可以将此示例转换为：

1. JOIN与派生表
2. CTE

但这是另一个故事-)