sql中的join的使用

Question

表 Employees:
Employee_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari
表 Orders:
Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03
select Employees.Employee_ID,Name,Product from Employees Xjoin Orders
则各种联接的结果集是什么：（full outer join,inner join, left join, right join） 联接条件是ON Employees.Employee_ID=Orders.Employee_ID

Answer 1

以下部分是我抄的
给个通俗的解释吧.
例表a
aid adate
1 a1
2 a2
3 a3
表b
bid bdate
1 b1
2 b2
4 b4
两个表a,b相连接,要取出id相同的字段
select * from a ,b where a.aid = b.bid这是仅取出匹配的数据.
此时的取出的是:
1 a1 b1
2 a2 b2
那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
3 a3 空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
4 空字符 b4

以下是我的补充
完整外联接就是包含左向外联接和右向外联接的连接，我想聪明的你一定可以举一反三，我也就不多费唇舌了。如果还不理解，给我消息，我会很清楚的给你解释你不懂的地方

Answer 2

declare @employees table(Employee_ID int , Name nvarchar(20))
insert into @employees select 01, 'Hansen Ola' union all
select 02, 'Svendson Tove' union all
select 03, 'Svendson Stephen' union all
select 04, 'Pettersen Kari'
declare @Orders table(Prod_ID int, Product nvarchar(10),Employee_ID int)
insert into @Orders select 234,'Printer',01 union all
select 657,'Table',03 union all
select 865,'Chair',03

--full outer join
select a.Employee_ID,Name,Product from @Employees as a full outer join @Orders as b on a.Employee_ID=b.Employee_ID
/*结果
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
2 Svendson Tove NULL
3 Svendson Stephen Table
3 Svendson Stephen Chair
4 Pettersen Kari NULL
*/

--inner join
select a.Employee_ID,Name,Product from @Employees as a inner join @Orders as b on a.Employee_ID=b.Employee_ID
/*结果
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
3 Svendson Stephen Table
3 Svendson Stephen Chair

*/

--left join
select a.Employee_ID,Name,Product from @Employees as a left join @Orders as b on a.Employee_ID=b.Employee_ID
/*
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
2 Svendson Tove NULL
3 Svendson Stephen Table
3 Svendson Stephen Chair
4 Pettersen Kari NULL
*/

--right join
select a.Employee_ID,Name,Product from @Employees as a right join @Orders as b on a.Employee_ID=b.Employee_ID
/*
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
3 Svendson Stephen Table
3 Svendson Stephen Chair
*/

Answer 3

连接两个表时的条件放到on后
1=1的话就是说任何条件都成立
比如select
*
from
table1
t1
join
table2
t2
on
t1.code=t2.code