T-SQL学习第三天

--连接查询
--连接查询分为：内连接、外连接、交差连接
--内连接：等值连接、自然连接、不等值连接

use mydbtest
go
create table infoT
(
infoTID int IDENTITY(1,1) not null primary key,
UserID int,
content char(20),
)
--1、等值连接
select * from infoT,UserManager where infoT.UserID=UserManager.UserID
--2、自然连接
select infoT.*,UserName from UserManager,InfoT where infoT.UserID=UserManager.UserID
--3、不等值连接
select infoT.*,UserName from infoT,UserManager where infoT.UserID<>UserManager.UserID

--外连接：左外连接(left outer join)、右外连接(right outer join)、全部连接(full outer join)
--左外连接(left outer join):对左边的表不加限制
select infoT.UserID,UserManager.UserID,content,UserName 
from infoT left join UserManager 
on infoT.UserID=UserManager.UserID
--右外连接(right outer join):对右边的表不加限制
select infoT.UserID,UserManager.UserID,content,UserName 
from infoT right join UserManager 
on infoT.UserID=UserManager.UserID
--全部连接(full outer join)
select infoT.UserID,UserManager.UserID,content,UserName 
from infoT full join UserManager 
on infoT.UserID=UserManager.UserID

--交差连接(cross join)
select * from UserManager cross join InfoT--没有实际的意义