Sql-server – Interpretation of multiple join order

join;sql server

I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.

If I use joins in between, I don't know how to use Left, Right, etc. in the SQL statement from priority aspect. I mean is it organized with parentheses or something else?

Relations for example are like following:

enter image description here

My query is as following:

SELECT T1.col1, T2.col2, T3.col3, T4.col1 AS Expr1
FROM T1  
INNER JOIN T1_T2 ON T1.id = T1_T2.T1_FK 
INNER JOIN T2 ON T1_T2.T2_FK = T2.id 
LEFT JOIN T2_T3 ON T2.id = T2_T3.T2_FK 
INNER JOIN T3 ON T2_T3.T3_FK = T3.id 
LEFT JOIN   T3_T4 ON T3.id = T3_T4.T3_FK 
INNER JOIN T4 ON T3_T4.T4_FK = T4.id

How does SQL Server interpret the joins? Can one represent it like execution priorities with parentheses? I mean: ((T1 INNER Join T1_T2) INNER JOIN T2) LEFT JOIN T2_T3) ?

Best Answer

The join order appears unimportant to the query final result given your mapped table equalities.

From MSDN:

The order of table sources after the FROM keyword does not affect the result set that is returned.

That said, you could, if order is very important to your process, or results, consider nested Common Table Expressions

Related Solutions

SQL Server Join Processing – Understanding Join/Where Processing Order

The logical processing of a query is on MSDN (written by Microsoft SQL Server team, not 3rd parties)

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

A derived table follows this, then the outer query does it again etc etc

This is logical though: not actual. No matter how SQL Server actually does it, these semantics are honoured to the letter. The "actual" is determined by the Query Optimiser (QO) and you avoid the intermediate Cartesion product you mentioned.

It's worth mentioning that SQL is declarative: you say "what" not "how" like you would for a procedural/imperative programming (Java, .net). So saying "this happens before that" is wrong in many cases (eg assumption of short circuits or L-to-R WHERE order)

In your case above, the QO will generate the same plan no matter how it is structured because it is a simple query.

However, the QO is cost based and for a complex query it may take 2 weeks to generate the ideal plan. So it does "good enough" which actually isn't.

So your first case may help the optimiser find a better plan because the logical processing order is different for the 2 queries. But it may not.

I have used this trick on SQL Server 2000 to get 60x speed performance improvement on reporting queries. As the QO improves version to version it gets better at working these things out.

And the book you mentioned: there is some dispute over it
See SO and the subsequent links: https://stackoverflow.com/q/3270338/27535

SQL Server – Using Table Name Contained in Another Table’s Column

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.

Best Answer

Related Solutions

SQL Server Join Processing – Understanding Join/Where Processing Order

SQL Server – Using Table Name Contained in Another Table’s Column

Related Question