SQL Server join介绍

介绍Inner Join(可以省略Inner,平常经常inner,就是inner join)， Full Out Join，Cross Join，Left Join， Right Join区别。

 

create table Customers (Cust_Id int, Cust_Name varchar(10))
insert Customers values (1, 'Craig')
insert Customers values (2, 'John Doe')
insert Customers values (3, 'Jane Doe')
 
create table Sales (Cust_Id int, Item varchar(10))
insert Sales values (2, 'Camera')
insert Sales values (3, 'Computer')
insert Sales values (3, 'Monitor')
insert Sales values (4, 'Printer')

Customers 表数据：

Sales 表数据：


1、inner join

两边都有的才筛选出来(Inner join 对表无顺序概念)

--Inner Join
--两边都有的才筛选出来(Inner join 对表无顺序概念)
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from Sales S inner join Customers C
on S.Cust_Id = C.Cust_Id

--平时经常就简单使用单使用join字段，就是inner join
--如下实例： 结果集跟使用inner join 一模一样
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from Customers c join sales s
on c.cust_id = s.cust_id

 

2、Full Out Join

两边都列出来，能匹配就匹配，不匹配的用NULL列出 (Full Out Join 对表无顺序概念)

--Full Out Join
--两边都列出来，能匹配就匹配，不匹配的用NULL列出 (Full Out Join 对表无顺序概念)
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from Sales S full outer join Customers C
on S.Cust_Id = C.Cust_Id

3、Cross Join

列出两边所有组合，也叫笛卡尔集A.Rows * B.Rows (Cross Join 对表无顺序概念)

--Cross Join
--列出两边所有组合，也叫笛卡尔集A.Rows * B.Rows (Cross Join 对表无顺序概念)
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from Sales S cross join Customers C

4、Left Join

以左边的表为主表，列出主表所有记录，能匹配就匹配，不匹配的用NULL列出
Left Join 对表有顺序概念  前面是主表 后面是副表

--Left Join
--以左边的表为主表，列出主表所有记录，能匹配就匹配，不匹配的用NULL列出
--Left Join 对表有顺序概念  前面是主表 后面是副表
-- 实例-1、Sales 为主表 （两个表的数据都显示）
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from sales s left join Customers c 
on c.cust_id = s.cust_id

2）、Customers 为主表

-- 实例-2、Customers 为主表
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from  Customers c left join sales s
on c.cust_id = s.cust_id

5、Right Join

 以右边的表为主表，列出主表所有记录，能匹配就匹配，不匹配的用NULL列出
 Right Join 对表有顺序概念 前面是副表 后面是主表

 

--Right Join
--以右边的表为主表，列出主表所有记录，能匹配就匹配，不匹配的用NULL列出
--Right Join 对表有顺序概念  前面是副表 后面是主表
-- 实例.1、 sales 为主表
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from  sales s right join Customers c 
on c.cust_id = s.cust_id

 

2）、Salves为主表

-- 实例.2、 sales 为主表
select 
s.Cust_id as Sales_Cust_id ,S.item as Sales_item,
C.Cust_id as Customers_Cust_id,c.Cust_name as Customers_Cust_name
from Customers c right join sales s
on c.cust_id = s.cust_id