SQL Server 2012 多表连接查询功能实例代码

废话不多说了，直接给大家贴代码了，具体代码如下所示：

-- 交叉连接产生笛卡尔值 (x*y)
select *
from  student
    cross join dbo.classinfo 
--另外一种写法
select *
from  student , classinfo 
-- 内连接 (inner 可以省略)
select *
from  student
    join dbo.classinfo on dbo.student.class = dbo.classinfo.id;
-- inner join
select *
from  student
    inner join dbo.classinfo on dbo.student.class = dbo.classinfo.id;
   -- on 条件，通常是 主外键，但是不限于主外键
   -- on 条件，允许有多个，而且可能是针对某个表的
select *
from  student
    inner join dbo.classinfo on dbo.student.class = dbo.classinfo.id
    and dbo.student.class in (1,2) -- 针对student表增加查询条件
-- 不等于 (笛卡尔值减去 相等的值)
select *
from  student
    inner join dbo.classinfo on dbo.student.class <> dbo.classinfo.id;
--自连接 从class表中查询到class所在的系
select t1.* ,t2.classname from dbo.classinfo as t1
    inner join dbo.classinfo as t2 on t1.pid=t2.id
-- left join
select *
from  student
    left join dbo.classinfo on dbo.student.class = dbo.classinfo.id;
-- right join
select *
from  student
    right  join dbo.classinfo on dbo.student.class = dbo.classinfo.id;
-- 全外连接
select *
from  student
    full outer join dbo.classinfo on dbo.student.class = dbo.classinfo.id;
-- 外连接和内连接的区别是：
-- 内连接，on条件不符合的话，会过滤掉数据
-- 外连接，以保留表为主，on条件成立显示数据，否则显示null
--union 纵向连接
select stuid,stuname,stuenname,stuage,stubirthday
from  student where stuid<=2
union
select stuid,stuname,stuenname,stuage,stubirthday
from dbo.student where stuid>2
--union 去重复
select stusex
from  student where stuid<=2
union
select stusex
from dbo.student where stuid>2
--union 显示全部
select stusex
from  student where stuid<=2
union all
select stusex
from dbo.student where stuid>2
--except 差集,排除
select stuid,stuname,stuenname,stuage,stubirthday
from  student
except
select stuid,stuname,stuenname,stuage,stubirthday
from dbo.student where stuid<=2
--intersect 交集
select stuid,stuname,stuenname,stuage,stubirthday
from  student where stuid>=2
except
select stuid,stuname,stuenname,stuage,stubirthday
from dbo.student where stuid<=3

以上所述是小编给大家介绍的sql server 2012 多表连接查询功能实例代码，希望对大家有所帮助