The order of join tables in SQL 2k

The order of join tables in SQL 2k

By dunglh, 05 Feb 2010

Ho Xa, Vinh Linh, Quang Tri, 17th Parallel

* Hãy nhỏ giọt nước vào ly, ngày nào đó sẽ có giọt nước làm tràn ly..!

-- Created by: dunglh
-- Created date: 05 Feb 2010
-- Purpose: test of the order of join tables
--
-- Conclusion:
-- The order of join tables will go from top down bottom.
-- The test and the test2 tables will do "left join" first.
-- After that, The result of the "left join" action will be a new table, and that new table will do "inner join" with the test3 table.
--
-- Caution:
-- You can misunderstand when you say that the test2 table and the test3 table will do "inner join" first.
-- And the above "inner join" result will do "left join" with the test table.

use master

-- drop tables if it existed already
IF EXISTS (
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'test')
DROP TABLE test

IF EXISTS (
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'test2')
DROP TABLE test2

IF EXISTS (
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'test3')
DROP TABLE test3
GO

-- create table test
create table test(
col1 int
)

insert into test(col1)values (1)
insert into test(col1)values (3)

select *
from test

-- create table test2
create table test2(
col1 int
)

insert into test2(col1)values (11)
insert into test2(col1)values (33)

select *
from test2

-- create table test3, and the test3 table's data is the same test2 table's data
create table test3(
col1 int
)

insert into test3(col1)values (11)
insert into test3(col1)values (33)

select *
from test3

/* test of the order of join tables */
select t.col1,t2.col1,t3.col1
from test t
left join test2 t2 on t.col1 = t2.col1
inner join test3 t3 on t2.col1 = t3.col1