What is going on inside when I run left join?

Question

I have a question regarding left join on SQL: I would like to know how SQL servers perform left join?

Let's say I have two tables.

PEOPLE

• id
• name

PHONE

• id
• person_id
• phone

When I execute:

   select name, phone 
     from people 
left join phone on people.id = phone.person_id

...I would like to know how SQL servers process the query string.

My guess is:

1. select all rows of people
2. start matching phone rows with on condition. In this case, people.id = phone_person_id.
3. display 'phone' value as null if not found since it is left join.

Am I correct??

In addition, what books should I read to get this kind of information?

Answer 1

Execute your query in SQL Server Management Studio and under the Query menu, choose the option to get the Actual Execution Plan.

After your execute your query, the execution plan will come back as a diagram on a separate tab. It reads from left to right. This plan will show you what happens under the hood.

Read more here

http://msdn.microsoft.com/en-us/library/aa178423%28SQL.80%29.aspx

http://www.simple-talk.com/sql/performance/execution-plan-basics/

Answer 2

What happens (at least in postgresql, but probably other are similar) is that the query planner will take your query and generate multiple strategies for execution, then it will rank those strategy depending on the knowledge it has of the tables (through statistics), and then choose the best execution strategy and execute it.

Look here for several very common strategies : http://en.wikipedia.org/wiki/Join%5F%28SQL%29

Basically:

• Nested Loop: For each people, do an independent lookup in phone (like a lot of small inner SELECTs)
• Merge Join: Scan both table in // if they have index allowing that
• Hash Join: First take the entire phone table content and load it in a hash table, then for each people inspect that table and get the corresponding phone from that temporary hashtable.

Answer 3

For all joins, first the query processor looks at the two sets that are being joined, and based on the data in those tables (as represented by cached database statictics), it combines the rows in both tables, using one of the available merge techniques ( Merge Join, Hash Join, or nested Loops). When done the combined set has a row in it for every combination of a row from one side with a row fropm the other side, which satisfies the join condition.

Then, if it's an outer join, all the rows from the 'Inner' side of the join which did not satisfy the join condition (there was no matching record on the 'Outer' Side) are added back into the combined set, with null values for all columns which would have come from the outer side..