What are the steps followed by sql engine to execute the query..??

Question

My question is not how to use inner join in sql. I know about how it matches between table a and table b.

I'd like to ask how is the internal working of inner working. What algorithm it involves? What happens internally when joining multiple tables?

Answer 1

There are different algorithms, depending on the DB server, indexes and data order (clustered PK), whether calculated values are joined or not etc.

Have a look at a query plan, which most SQL systems can create for a query, it should give you an idea what it does.

Answer 2

In MS Sql, different join algorithms will be used in different situations depending on the tables (their size, what sort of indexes are available, etc). I imagine other DB engines also use a variety of algorithms.

The main types of join used by Ms Sql are:
- Nested loops joins
- Merge joins
- Hash joins

You can read more about them on this page: Msdn -Advanced Query Tuning Concepts

If you get SQL to display the 'execution plan' for your queries you will be able to see what type of join is being used in different situations.

Answer 3

It depends on what database you're using, what you're joining (large/small, in sequence/random, indexed/non-indexed etc).

For example, SQL Server has several different join algorithms; loop joins, merge joins, hash joins. Which one is used is determined by the optimizer when it is working out an execution plan. Sometimes it makes a misjudgement and you can then force a specific join algorithm by using join hints.

You may find the following MSDN pages interesting:
http://msdn.microsoft.com/en-us/library/ms191318.aspx (loop)
http://msdn.microsoft.com/en-us/library/ms189313.aspx (hash)
http://msdn.microsoft.com/en-us/library/ms190967.aspx (merge)
http://msdn.microsoft.com/en-us/library/ms173815.aspx (hints)