Nested sql joins process explanation needed

Question

Hi,

I want to understand the process of nested join clauses in sql queries. Can you explain this example with pseudo codes? (What is the order of joining tables?)

  FROM 
table1 AS t1 (nolock)
    INNER JOIN table2 AS t2 (nolock)
        INNER JOIN table3 as t3 (nolock)
        ON t2.id = t3.id
    ON t1.mainId = t2.mainId

Answer 1

Try EXPLAIN query.

It tells you exactly what's going on. :)

Of course that doesn't work in SQL Server. For that you can try Razor SQLServer Explain Plan

Or even SET SHOWPLAN_ALL

Answer 2

In SQl basically we have 3 ways to join two tables.

Nested Loop ( Good if one table has small number of rows), Hash Join (Good if both table has very large rows, it does expensive hash formation in memory) Merge Join (Good when we have sorted data to join).

From your question it seems that you want for Nested Loop.

Let us say t1 has 20 rows, t2 has 500 rows.

Now it will be like

For each row in t1 Find rows in t2 where t1.MainId = t2.MainId

Now out put of that will be joined to t3.

Order of Joining depends on Optimizer, Expected Row count etc.

Answer 3

If you're using SQL Server Query Analyzer, look for "Show Execution Plan" under the "Query" menu, and enable it.