定义就不用说了吧!
测试数据 test_table
create
table
test_table
(u_id int ,u_name varchar ( 20 ))
insert test_table select 1 , ' a1 '
union all select 2 , ' b2 '
union all select 3 , ' c3 ' ;
(u_id int ,u_name varchar ( 20 ))
insert test_table select 1 , ' a1 '
union all select 2 , ' b2 '
union all select 3 , ' c3 ' ;
测试数据 test_table_link
create
table
test_table_link
(u_id int ,u_other varchar ( 50 ))
insert test_table_link select 1 , ' each month '
union all select 1 , ' each day '
union all select 1 , ' each year '
union all select 55 , ' each day '
union all select 55 , ' each year '
union all select 3 , ' each day '
union all select 3 , ' each year '
union all select 88 , ' each day '
union all select 88 , ' each month ' ;
(u_id int ,u_other varchar ( 50 ))
insert test_table_link select 1 , ' each month '
union all select 1 , ' each day '
union all select 1 , ' each year '
union all select 55 , ' each day '
union all select 55 , ' each year '
union all select 3 , ' each day '
union all select 3 , ' each year '
union all select 88 , ' each day '
union all select 88 , ' each month ' ;
结果
select
a.u_id,a.u_name,b.u_other
from test_table a LEFT OUTER JOIN test_table_link b
ON a.u_id = b.u_id;
from test_table a LEFT OUTER JOIN test_table_link b
ON a.u_id = b.u_id;
测试环境为sql server 2005
原本以为只是显示test_table中的每3条记录,实际上不是这样的(为什么多了记录了呢?),只要第一个表(test_table)中有,而第二个表与之相关联得上的都会显示的,但是一定要在第一个表(test_table)中存在,如第二个表(test_table_link)中的uid为55,88的不会出现,若第二个表没有找到与第一个表相匹配的那当然为null了!right outer join就与之相反了.