SQL Server Performance Forum – Threads Archive

Inner Join or Subquery

Hi guyz, please see if you can help me…. I have the following query which joins 4 tables. SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type
FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN D ON A.Application_Key = D.Application_Key
INNER JOIN E ON B.Julian_Month = E.Julian_Month WHERE D.valid_application_flag = ‘Y’
Now if I rewrite this query by using a subquery instead of a Join to the ‘D’ table, the query takes half the time to execute. SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type AS Requested_Product_Credit_Type, C.Period
FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN E ON B.Julian_Month = E.Julian_Month WHERE A.Application_Key In (Select Application_Key From D
Where valid_application_flag = ‘Y’)
Now my question is, is there any issue in using a subquery instead of an Inner Join here? Also is there any chance that it will affect the functionality in the future (because there is one more join after the ‘D’ table Join). Thanks in advance…..

Welcome to the Forum. I donÂ´t see any issue. Did you see both execution plan to check what indexes are used in one case and in other?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thnx for the reply. In the execution plan, ‘Clustered Index Scan’ is used when Im using Join and ‘Clustered Index Seek’ is used when the subquery is used. Application Key is the clustered Index column on table ‘D’. I am just concerned with this…. If we see the sequence of execution of statements in the first query, SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN D ON A.Application_Key = D.Application_Key
INNER JOIN E ON B.Julian_Month = E.Julian_Month WHERE D.valid_application_flag = ‘Y’ 1. A is joined with B
2. B is joined with C
3. A is joined with D
4. B is joined with E
5. The resultset is Filtered with the ‘Where’ clause (WHERE D.valid_application_flag = ‘Y’). Now in the query, which I rewrote, SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type AS Requested_Product_Credit_Type, C.Period FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN E ON B.Julian_Month = E.Julian_Month WHERE A.Application_Key In (Select Application_Key From D
Where valid_application_flag = ‘Y’) the sequence will be like this… 1. A is joined with B
2. B is joined with C
3. B is joined with E
4. The resultset is then Filtered with the ‘Where’ clause (WHERE A.Application_Key In (Select Application_Key From D Where valid_application_flag = ‘Y’)). Im just worried, whether this will affect the functionality, because the sequence of operation is not the same in both the cases….. I am getting the same results in both cases though. Thanks….

Hi ya, You can also try SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type
FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN D ON A.Application_Key = D.Application_Key
AND D.valid_application_flag = ‘Y’
INNER JOIN E ON B.Julian_Month = E.Julian_Month
I’d say that the reason for the change is that SQL is not filtering the D table at the time that it is being joined, instead opting to filter the entire resultset at the end Cheers
Twan

.. or rewrite with derived table, see what happens to execution plan. SELECT A.julian_month, B.month_year, C.lienholder, C.product_credit_type
FROM A INNER JOIN B ON A.Orig_Day_Key = B.Day_Key
INNER JOIN C ON A.Requested_Product_Key = C.Product_Key
INNER JOIN (SELECT * FROM D WHERE D.valid_application_flag = ‘Y’) AS D ON A.Application_Key = D.Application_Key
INNER JOIN E ON B.Julian_Month = E.Julian_Month All variations of T-SQL listed here will return the same rows, don’t worry about that!

The sub-querey may indeed be faster. However, since SQL read left to right you may want to try changing your join order putting the most restrictive clauses/joins first. For example: SELECT
A.julian_month,
B.month_year,
C.lienholder,
C.product_credit_type
FROM
D
INNER JOIN
A
ON A.Application_Key = D.Application_Key
AND D.valid_application_flag = ‘Y’
INNER JOIN
B
ON A.Orig_Day_Key = B.Day_Key
INNER JOIN
C
ON A.Requested_Product_Key = C.Product_Key
INNER JOIN
E
ON B.Julian_Month = E.Julian_Month

The order joins are written doesn’t guarantee the join order unless "option (force order)" clause is used.

]]>