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
