SQL Server 2005 - Order of Inner Joins

SQL Server 2005 - Order of Inner Joins

SQL is declarative, that is, the JOIN order should not matter.

However it can in practice, say, if it's a complex query when the optimiser does not explore all options (which in theory could take months).

Another option is that it's a very different query if you reorder and you get different results, but this is usually with OUTER JOINs.

And it could also be the way the ON clause is specified It has to change if you reorder the FROM clause. Unless you are using the older (and bad) JOIN-in-the-WHERE-clause.

Finally, if it's a concern you could use parenthesis to change evaluation order to make your intentions clear, say, filter on a large table first to generate a derived table.

SQL Server 2005 INNER JOIN

Thanks to Andriy M I found this and it worked!

SQL Server 2005: Multiple INNER JOIN expressions

If INNER JOIN prevents you from getting records back, then you have an issue with the JOIN condition.

The best way to test for this is to use LEFT JOIN then a WHERE clause looking for NULL in the right table, like:

SELECT *
FROM table t1
LEFT JOIN table2 t2
  ON t1.key = t2.key
WHERE t2.key IS NULL

alternately you can use WHERE NOT EXISTS:

SELECT *
FROM table t1
WHERE NOT EXISTS (SELECT NULL
                  FROM Table2 t2
                  WHERE t2.key = t1.key)

Does INNER JOIN performance depends on order of tables?

Aliases, and the order of the tables in the join (assuming it's INNER JOIN) doesn't affect the final outcome and thus doesn't affect performance since the order is replace (if needed) when the query is executed.

You can read some more basic concepts about relational algebra here:
http://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators

SQL Server 2005 Slow Query with Joins and Order By

You need to index your join keys! If you join on multiple fields, add covering indexes that cover all those fields (in the same order they are in the query). That will probably take care of 90% of your performance issues.

It's possible to have a CPU bottleneck on a query like that since the server has to do table scans for all your joins.

Does Sql JOIN order affect performance?

No, the JOIN by order is changed during optimization.

The only caveat is the Option FORCE ORDER which will force joins to happen in the exact order you have them specified.

Do the order of JOINs make a difference?

The order of joins makes no difference.

What does make a difference is ensuring your statistics are up to date.

One way to check your statistics is to run a query in SSMS and include the Actual execution plan. If the Estimated number of rows is very different to the Actual number of rows used by any part of the execution plan, then your statistics are out of date.

Statistics are rebuilt when the related indexes are rebuilt. If your production maintenance window allows, I would update statistics every night.

This will update statistics for all tables in a database:

exec sp_MSforeachtable "UPDATE STATISTICS ?"

join three tables in sql server 2005

You do the same, with the third table:

SELECT O.OrderID,O.CustID,O.OrderTotal,C.Name, OC.OrderAmount
FROM Orders as O 
INNER JOIN Customers as C 
  ON O.CustID=C.CustID 
INNER JOIN OrderItems as OC
  ON O.OrderID=OC.OrderID 

How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

---

Related Topics