CODEWITHSUNDEEP
sqlsql-serverquery-performance
SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

Which to Prefer IN vs Join vs Equals in SQL?

I want to get the Manufacturer from tbl_Manufacturer. I wrote three different Queries to get Manufacturers of the Item from tbl_Sales_OrderItems table. I Want to know which one to prefer. Will someone let me know which query should be used over other and why?

Query 1: Using Subquery

SELECT manufacturer
FROM   tbl_Manufacturers
WHERE  ManufacturerID IN(SELECT trc.ManufacturerID
                         FROM   tbl_Sales_RepairCategory trc
                         WHERE  trc.RepairCategoryID IN (SELECT RepairCategoryID
                                                         FROM   tbl_VendorParts
                                                         WHERE  VendorPartID IN (SELECT refid
                                                                                 FROM   tbl_Sales_OrderItems
                                                                                 WHERE  typeid = 2
                                                                                        AND SalesOrderID = 182)))

Query 2: Using Sub-Query with Distinct

SELECT DISTINCT Manufacturer
FROM   tbl_Manufacturers m,
       tbl_VendorParts tvp,
       tbl_Sales_RepairCategory trc
WHERE  tvp.RepairCategoryID = trc.RepairCategoryID
       AND trc.ManufacturerID = m.ManufacturerID
       AND tvp.VendorPartID IN (SELECT Refid
                                FROM   tbl_Sales_OrderItems
                                WHERE  isnull(typeid, 0) = 2
                                       AND SalesOrderID = 182)

Query 3: Using '=' (equals) with Distinct, join

SELECT DISTINCT Manufacturer
FROM   tbl_Manufacturers m,
       tbl_VendorParts tvp,
       tbl_Sales_RepairCategory trc
WHERE  tvp.RepairCategoryID = trc.RepairCategoryID
       AND trc.ManufacturerID = m.ManufacturerID
       AND tvp.VendorPartID = (SELECT Refid
                               FROM   tbl_Sales_OrderItems
                               WHERE  isnull(typeid, 0) = 2
                                      AND SalesOrderID = 182)

here is the Execuition plan: enter image description here

Please suggest which to use?

Suggestions Appreciated!

Upvotes: 1

Views: 461

Answers (2)

Jenn
Jenn

Reputation: 795

I like to use exists in this scenario. You know that you need to check the information in specific table columns, but you do not need to return any information from those tables. This is exactly what an exists is designed for. Exists tends to be more optimal than extra joins. The other nice thing about using exists is you are keeping the code cleaner. It is much easier to see what table you are pulling the necessary information from and that allows for easier maintainability in the future. I would recommend using something like this:

SELECT manufacturer
FROM   tbl_Manufacturers m
WHERE  exists   (SELECT 1
                FROM    tbl_Sales_RepairCategory trc
                join    tbl_VendorParts r
                On      trc.RepairCategoryID = r.RepairCategoryID
                join    tbl_Sales_OrderItems s
                On      s.refid = r.VendorPartID
                Where   m.ManufacturerID  = trc.ManufacturerID
                and     r.typeid = 2
                and     r.salesOrderId = 182)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425208

  • In general, IN == poor performance.
  • In general, joins are the way to go.
  • Pre-SQL92 (old fashioned) joins that have the join condition in the where clause should be coded using the "new" (20+ years old) join syntax

None of your proposed queries would perform as well as this:

SELECT DISTINCT Manufacturer
FROM tbl_Sales_OrderItems s
JOIN tbl_VendorParts tvp ON tvp.VendorPartID = s.Refid
JOIN tbl_Sales_RepairCategory trc ON tvp.RepairCategoryID = trc.RepairCategoryID
JOIN tbl_Manufacturers m ON trc.ManufacturerID = m.ManufacturerID
WHERE (typeid = 2 OR typeid IS NULL)
AND SalesOrderID = 182

Notes:

  • all joins have been converted to proper joins
  • the table join order has been reversed, so the where clause operates on the first table listed, so indexes can be most effectively used and importantly as few row access operations as possible are needed
  • typeid condition has been converted to an OR to avoid the isnull() function being called

Upvotes: 1

Related Questions