第四章SQL数据库开发--TSQL—联接查询和UNION合并结果集

第四章SQL数据库开发--TSQL—联接查询和UNION合并结果集

4 联接查询的逻辑处理过程

   交叉联接：笛卡尔乘积，where条件。

   内联接：笛卡尔乘积，ON筛选器，where条件。

   外联接：笛卡尔乘积，ON筛选器，添加外部行，where条件。

 

4.1 内联接—最常用

内连接是一种最常用的连接类型。内连接查询实际上是一种任意条件的查询。使用内连接时，如果两个表的相关字段满足连接条件，就从这两个表中提取数据并组合成新的记录，也就是在内连接查询中，只有满足条件的数据才能出现在结果关系中。

在内查询中on子句与where一条一视同仁的。

 

FROM  join_table join_type join_table [ON (join_condition 联接条件)]

FROM join_table  inner(省略) join  join_table [ON (join_condition联接条件)]

 

SELECT TS.sname,TS.sex,ts.Class,tc.mark FROM TStudent TS inner join TScore TC on TS.StudentID=TC.StudentID and TS.StudentID=1;

 

SELECT TS.sname,TS.sex,ts.Class,tc.mark FROM TStudent TS, TScore TC where  TS.StudentID=TC.StudentID and TS.StudentID=1;

 

郑广凡 男   测试      69

郑广凡 男   测试      87

郑广凡 男   测试      97

4.2 外联接

  一种联接，包括满足搜索条件的联接表的所有行，甚至包括在所联接的表中没有匹配行的表中的行。对于当一个表中的行与另一个表中的行不匹配时所返回的数据行，将为解析到没有相应行的表中的所有结果集列提供 NULL 值。

外连接分为左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）、右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）、全连接（FULL JOIN）或全外连接（FULL OUTER JOIN）。我们就简单的叫：左连接、右连接和全连接。

在外联接中ON子句和where子句是由区别的，在外联接中先进行ON子句，后进行where子句。

 

测试表数据

p_departmentt                    t_departmentt

1    老潘      总裁部               

2    老王      秘书部                2   老王      秘书部

3    老张      设计部                3   老张      设计部

4    老李      运营部                5   老刘      人事部

                                 6 老黄      生产部

4.2.1 左外联接LEFT JOIN

概念：返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

在外联接中ON子句和where子句是由区别的，在外联接中先进行ON子句，后进行where子句。

 

SELECT * FROM p_departmentt p left join  t_departmentt t on t.t_id=p.p_id;; 是左外联接

 

SELECT * FROM p_departmentt p left join  t_departmentt t on t.t_id=p.p_id and p_id<3; 是左外联接。

 

SELECT * FROM p_departmentt p left join  t_departmentt t on  t.t_id=p.p_id where  p_id<3;结果是先进行ON子句，后进行where子句的数据。

 

4.2.2 右外联接RIGHT JOIN

   概念：恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值

在外联接中ON子句和where子句是由区别的，在外联接中先进行ON子句，后进行where子句。

 

SELECT * FROM p_departmentt p right join  t_departmentt t on t.t_id=p.p_id; 是右外联接

 

SELECT * FROM p_departmentt p left join  t_departmentt t on  t.t_id=p.p_id where  p_id<3; 结果是先进行ON子句，后进行where子句的数据。

 

 

4.2.3 全外联接 FULL JOIN

概念：返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值

SELECT * FROM p_departmentt p full join  t_departmentt t on  t.t_id=p.p_id

 

4.3  单表自联接

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

 

4.3.1 使用同一列进行自联接

 

SELECT a.user_num,b.user_num,a.user_name FROM A70_USERS_ZH a join A70_USERS_ZH b on a.user_name=b.user_name and a.user_id<>b.user_id（查找同名工作者）

 

 

4.3.2 使用不同列进行自连接

增加领导列 1 所长 alter table A70_USERS_ZH add leadid varchar(10)

修改这个人为所长update A70_USERS_ZH set leadid=680363 where user_id<>680363

   SELECT a.user_num,a.user_name,b.user_name as 所长 FROM A70_USERS_ZH a left join A70_USERS_ZH b on a.leadid=b.user_id.

4.4 交叉联接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，M*N行

第一种写法

SELECT count(*) FROM A70_USERS_ZH a cross join A70_USERS_ZH b;

第二种写法

SELECT count(*) FROM A70_USERS_ZH a, A70_USERS_ZH b;

 

4.5多表联接查询

4.5.1 多表联接查询

 

4.6UNION合并多个查询结果

将两个或更多个查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。 UNION 运算不同于使用联接合并两个表中的列的运算。UNION，指定合并多个结果集并将其作为单个结果集返回。

UNION 内部的

1 SELECT 语句必须拥有相同数量的列。

2 列也必须拥有相似的数据类型。

3 同时每条 SELECT 语句中的列的顺序必须相同。

4 列的名称或者别名是由第一个select语句的选择列表决定的。

5 ALL将全部行并入结果中，其中包括重复行，如果未指定该参数，则删除重复行。

6 所有SELECT语句的合并操作结果进行排序的ORDER BY子句，必须放到最后一个SELECT后面， 但他所使用的排序名必须是第一个SELECT选择列表中的列名。

 

 

SELECT * FROM TStudent where sex='女'

union all

SELECT * FROM TStudent where Birthday>'1989-01-01' order by studentid

 

4.6.1 UNION中的自动数据类型转换

合并表的时候，两个表的列数据类型必须相同吗？不，只要数据类型兼容就可以。

例如字符串类型，长度不一致，合并表的时候，短的长度等于长度长的。

其次是数值烈性，长度不一致，合并表的时候，短的长度等于长度长的。

 

4.6.2 union合并不同类型的数据

需要借助数据类型转换函数

SELECT SNAME,SEX from TNAME

Union all

SELECT CNAME,str(num) from course

4.6.3 UNION 合并不同列数的表

 

SELECT  top 10 unit_id,ac_when,null FROM P01_TOD_INDEX

union

SELECT top 10 unit_id,occur_time,cash_sum FROM P04_TOD_REALCASH

 

4.6.4 UNION多表合并与合并顺序改变

  SELECT  top 10 unit_id,ac_when,null FROM P01_TOD_INDEX

union

SELECT top 10 unit_id,occur_time,cash_sum FROM P04_TOD_REALCASH

union

SELECT top 10 unit_id,occur_time,sheet_num FROM P08_TOD_SENDTICKET

 

 

合并顺序改变

(SELECT  top 10 unit_id,ac_when,null FROM P01_TOD_INDEX

union

SELECT top 10 unit_id,occur_time,cash_sum FROM P04_TOD_REALCASH)

union

SELECT top 10 unit_id,occur_time,sheet_num FROM P08_TOD_SENDTICKET