SQL SERVER表联接查询的各种写法

经常在网络上看到各种的联接写法，经常接触到full,outer join ,inner join ,left join,right join,in,exists等。一直不是很清楚各种关键字的意思；今天总结下，一自己解惑，二分享给享给同样疑惑的朋友.

1、用","区分表，系统自动用inner join 方式连接

select * from Inventory a,InventoryClass b,bas_part c 

where a.cInvCCode=b.cInvCCode and a.cInvCode=c.InvCode

 

2、内联接 inner join 

select * from Inventory a inner join InventoryClass b on a.cInvCCode=b.cInvCCode

inner join  bas_part c on a.cInvCode=c.InvCode

 

3、in查找方式，系统自动用inner join方式配匹

select * from Inventory a where a.cInvCCode in(select b.cInvCCode from InventoryClass b)

and cInvCode in(select c.InvCode from bas_part c)

注：结果集比较大的情况下则用join的效率高于用in。ps来源网络

in多表查询“执行计划”显示优于inner join ，但查询结集只返回a表的数据。

作用不相同比较速度是没意义的。

 

4、隐式join，系统自动用inner join（效果同inner join)

SELECT TOP 10 * FROM inventory a join InventoryClass b on a.cInvCCode=b.cInvCCode

 

5、left join左联接，左表记录全部显示

SELECT  * FROM inventory a left outer join InventoryClass b on a.cInvCCode=b.cInvCCode

 

6、right join右联接，右表记录全部显示

SELECT  * FROM inventory a right outer join InventoryClass b on a.cInvCCode=b.cInvCCode

 

7、两表外联接 full join或full outer join，两种写法都一样。两表记录全部显示

select  * from Inventory a full outer join InventoryClass b on a.cInvCCode=b.cInvCCode

select  * from Inventory a full join InventoryClass b on a.cInvCCode=b.cInvCCode

联接查询可使用多条件。

例如：

select * from Inventory a,InventoryClass b where a.cInvCCode=b.cInvCCode and a.cInvName=b.cInvCName

或：select * from Inventory a inner join InventoryClass b on a.cInvCCode=b.cInvCCode and  a.cInvName=b.cInvCName