CODEWITHSUNDEEP
sqlsql-serverjoin
mkus
mkus

Reputation: 3487

Nested sql joins process explanation needed

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

Upvotes: 3

Views: 2914

Answers (3)

Marcin Seredynski
Marcin Seredynski

Reputation: 7157

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

Upvotes: 1

Nitin Midha
Nitin Midha

Reputation: 2268

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.

Upvotes: 2

Tor Valamo
Tor Valamo

Reputation: 33749

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

Upvotes: 1

Related Questions