Converting a nested sql where-in pattern to joins

I have a query that is returning the correct data to me, but being a developer rather than a DBA I'm wondering if there is any reason to convert it to joins rather than nested selects and if so, 开发者_JS百科what it would look like.

My code currently is

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

Any references to the better use of joins would be appreciated too.

---

Besides any likely performance improvements, I find following much easier to read.

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'        

Test script showing my original fault in ommitting 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'        

---

If you would use joins, it would look like this:

select *
from adjustments
inner join stores on stores.id = adjustments.store_id
inner join stores as stores2 on stores2.original_id = stores.original_id
where stores2.name = 'abcd'

(Apparently you can omit the second SELECT on the stores table (I left it out of my query) because if I'm interpreting your table structure correctly,
select id from stores where original_id = (select original_id from stores where name ='abcd')
is the same as
select * from stores where name ='abcd'.)
--> edited my query back to the original form, thanks to Lieven for pointing out my mistake in his answer!

I prefer using joins, but for simple queries like that, there is normally no performance difference. SQL Server treats both queries the same internally.

If you want to be sure, you can look at the execution plan.
If you run both queries together, SQL Server will also tell you which query took more resources than the other (in percent).

---

A slightly different approach:

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')

---

As say Microsoft here:

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results.

Your case is exactly when Join and subquery gives the same performance.

Example when subquery can not be converted to "simple" JOIN:

  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(*)) 

As you can see, every country can have different name translations so we can not just join and count records (in that case, countries with larger quantities of translations will have more record counts)
Of cource, you can can transform this example to:

1. JOIN with derived table
2. CTE

but it is an other tale-)