#头条创作挑战赛#

联接查询:通过join关键字将多个表基于某些条件进行连接,从而获得满足条件的数据

内联结(Inner join)仅返回两个联接表中都具有匹配项的行

示例1:查询所有学生的姓名,学生班级编号,班级编号与班级名称

--前两项在student表中,班级名称在class表中
--内联接后要显示的数据基于两表的主外键都对应的数据才会显示
--简单来说只会查询出两表中学生班级编号等于班级编号的学生数据
--如果学生表中的 班级编号=66  而班级表中 没有 班级编号=66 
--就不会显示班级编号=66 的学生数据
SELECT 
stu.SName N'学生姓名',
stu.SCId N'学生比较编号',
cls.CId N'班级编号',
cls.CName N'班级名称'
FROM dbo.Student AS stu  --学生表
INNER JOIN dbo.Class AS cls  --以内联接的方式,连接班级表
ON Cls.CId = stu.SCId  --相当于where条件如where SCId in(所有的班级编号)

--ON Cls.CId = stu.SCId AND stu.SId<6 
两个条件:不仅主外键一致还要学生编号<6的学生数据

表1 inner join 表2 on 条件 固定格式

两表中的别名:可省略
列名前缀(stu/cls):如果两表中的列名没有一样的可省略
如果两表中有相同的列名(假设都是 SName)需要加前缀,以示区别是哪个表的列名

一般情况下在效率上Where可能具有和Inner join一样的效率,但是在多表连接时,不推荐使用where语句

外联结(Outer join)

外联接是内部联接的扩展,即使外联接在联接表中没有相关行,外联接也会返回这些行

外联接共有三种类型：左联接(left join)右联接(right join)和完全联接(full join)

1 左联接(left [outer] join)返回左表(即from语句中的表)中的所有行,同时返回右表(left join语句中的表)中匹配的行,如果右表中没有匹配的行,则返回 NULL 值

--创建示例表 成绩表
CREATE TABLE Score 
(
  SId INT IDENTITY(1,1) PRIMARY KEY,--自动编号
  SSId INT,--学生编号
  SChinese INT,--语文
  SMath INT,--数学
  SHistory INT,--历史
  SCredit INT --学分
)
--插入数据
INSERT INTO dbo.Score(SSId,SChinese,SMath,SHistory,SCredit)
SELECT 1,90,88,95,5 UNION
SELECT 2,70,58,55,4 UNION
SELECT 3,30,81,36,3 UNION
SELECT 5,90,79,67,3 UNION
SELECT 7,60,28,75,1 UNION
SELECT 8,53,58,85,2 UNION
SELECT 9,50,87,29,5

--以学生表为主的左联接
--显示的最终行数 以学生表中的行数为准
--假设符合条件的 学生表行数为 20 就会显示20条数据
--简单来说:首先筛选出学生表中符合条件的数据
--成绩表中的数据去匹配学生表中的数据即stu.SId=sco.SSId
--匹配上的显示其成绩,匹配不上的显示为 NULL
SELECT 
 stu.SName N'学生姓名',
 sco.SSId N'学生编号',
 sco.SChinese N'语文成绩',
 sco.SMath N'数学成绩',
 sco.SHistory N'历史成绩' 
FROM dbo.Student AS stu
LEFT OUTER JOIN dbo.Score sco
ON stu.SId=sco.SSId

--以成绩表为主的左联接
--显示的最终行数 以成绩表中的行数为准
--假设符合条件的 成绩表行数为 10 就会显示10条数据
SELECT 
 stu.SName N'学生姓名',
 sco.SSId N'学生编号',
 sco.SChinese N'语文成绩',
 sco.SMath N'数学成绩',
 sco.SHistory N'历史成绩' 
FROM dbo.Score sco
LEFT OUTER JOIN dbo.Student AS stu
ON sco.SSId=stu.SId

--LEFT OUTER JOIN 语句中的 OUTER 可省略
注意:左联接时以哪个表为主会影响最终显示数据的行号

2 右联接(right [outer] join)返回右表(即right join语句中的表)中的所有行,同时返回右表(from语句中的表)中匹配的行,如果右表中没有匹配的行,则返回 NULL 值

示例将左联接的 left 替换为 right 结果与上图中的正好相反(自行测试)

注意:不论左联接还是右联接最终显示的行数以为主的表中行数为准

3 完全联接(full join) 返回左表和右表中的所有行,如果某个表中没有匹配的行,则返回 NULL 值

SELECT * --显示两个表中的所有列名
FROM dbo.Student AS stu
FULL OUTER JOIN dbo.Score sco
ON sco.SSId=stu.SId

交叉联接(cross join)

没有表的联接条件,一个表的每一行与另一表的每一行合并,这种类型的结果集称为笛卡尔乘积或叉积(了解即可)

--学生表 24条数据
--班级表 8条数据
--显示总数据行数 192
SELECT *
FROM dbo.Student 
CROSS JOIN dbo.Class