本文详细介绍了如何使用SQL的关系代数操作合并数据，涵盖了内连接、外连接（左、右、全外）、交叉连接以及特殊连接的用法，并通过实例展示了子查询的运用，包括使用子查询作为值列表、表以及在复杂查询中的应用。同时讨论了并操作和交操作在数据融合中的作用。
1.使用连接
连接类型 定 义
内连接 只连接匹配的行
左外连接 包含左边的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行
右外连接 包含右边的全部行（不管左边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行
全外连接 包含左、右两个表的全部行，不管在另外一边的表中是否存在与它们匹配的行
θ(theta)连接 使用等值以外的条件来匹配左、右两个表中的行
交叉连接 生成笛卡尔积--它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
1.1 内连接
语法：select * from Table1 join Table2 on Table1.column=Table2.column
1.2 外连接
左外连接：left outer join 右外连接： right outer join
语法：
select * from Table1 left/right outer join Table2 --//outer 关键字为可选项，可以不写
on Table1.字段=Table2.字段 --//条件
注意：
在代码中，左和右用于指定无论是否具有匹配的行都要保留全部行的那个表（外连接表），
在这里左和右指的是表在SQL代码中的顺序，而使用图形化查询工具的位置是没有关系的。
例： 查询所有的客户及他们的订单号：
select j_merchant.shutname as '加盟商',d_sell.sellid as '订单编号'
from j_merchant left join d_sell --//左连接
on j_merchant.merchantid=d_sell.get_mchid --//条件
order by d_sell.sellid --//按单号排序
加盟商 订单编号
-----------------------------------------------------------------------
葫芦岛 PF1001-2006-08-19-0001
佛山 PF1001-2006-09-10-0001
武汉 PF1001-2006-09-10-0002
福州 PF1001-2006-09-10-0003
福州 PF1001-2006-09-10-0004
珠海 PF1001-2006-11-04-0001
武汉 PF1001-2006-11-04-0002
鞍山 PF1001-2007-08-14-0001
福州 PF1001-2007-08-15-0001
……
全外连接：FULL OUTER JOIN
全外连接返回两个表中的所有行，无论它们在另一个表中是否有与之匹配的行
注意：
当在内连接查询中加入条件时，无论是将它加入到 join 子句，还是加入到 where 子句，
其效果是完全一样的，但对于外连接连接情况就不是这样报，当把条件加入到 join 子句时，
SQL 会返回外连接表的全部行，然后使用指定的条件返回第二个表的行；如果将条件放到
where 子句中，SQL 将会首先进行连接操作，然后 where 子句对连接后的行进行筛选。
自身连接： 同一个表自己与自己进行连接
例句：
select a.cr_name as '店铺',b.names as '店员',a.employeeid as '店员编号'
from j_employee a join j_employee b --//自身连接
on a.cr_name=b.cr_name --//条件
where a.cr_name='金光华' --//条件
返回：
店铺 店员 店员编号
----------------------------------------------------------
金光华 朱松华 0002
金光华 朱松华 0003
金光华 朱松华 0004
金光华 程小梅 0002
金光华 程小梅 0003
金光华 程小梅 0004
金光华 黎运蓉 0002
金光华 黎运蓉 0003
金光华 黎运蓉 0004
交叉连接（无限制连接）cross join
语法：
select 字段1，字段2 from 表一 cross join 表二
说明：
用于对两个源表进行纯代数的乘运算，它不使用连接条件来限制结果集合，而是将分别来自两
个源数据表中的行以所有可能的方式进行组合。数据表一中的每个行都要与数据表二中的每个行分别
组合成一个新行。例如表1中有5行，表2中有4行，那么它们进行交叉连接会产生20个行。人们将这
种结果集称为笛卡尔乘积。
特殊连接：
A. 可以使用非等值的 on 条件连接（=,>,<,>=,<=,<>）
B. 可以使用多条件连接如：from A join B on A.col=B.col join C on B.col=C.col and A.col=C.col
C. 非键连接：并非只能使用主键和外部键来进行连接，可以使用任意的列来将一个
数据源中的行与另一个数据源中的行连接在一起，只要这些列具有兼容的数据类型，
并且其数据之间具有匹配关系。
2. 使用子查询
如果子查询只返回一个值,那么就可以在 SQL select 语句中任何可以使用表达式的地方使用它，
包括：列表达式，连接条件，where 条件或者 having 条件。也可以使用普通的运算符（+、=等）
来对子查询返回的单个值进行运算；但是，你也许需要使用 cast() 或者 convert() 函数对它进行数
据类型转换。
例： 要求查询各小类的销售占总销售的百分比
select a.st_xz as '小类',sum(b.s_price*b.nums) as '金额', --//计算小类金额
cast (sum(b.s_price*b.nums)/(select sum(c.s_price*c.nums) from d_retailsub as c)*100 as int) as '百分比' --//计算小类百份比=小类金额/总金额
from d_retailsub b join j_style a on b.styleid=a.styleid --//连接条件
join d_retail d on b.retailid=d.retailid and d.sure=1
group by a.st_xz --//按小类排序
返回：
小类 金额 百分比
---------------------------------------------------------------------------
皮衣 2196346.20 5
围巾 80817.44 0
毛衫 3479297.14 8
牛仔裤 3669109.03 9
领带 10097.62 0
短裤 26291.71 0
休闲裤 5498204.39 14
长袖衬衫 111888.74 0
皮带 623835.17 1
牛仔衫 24419.35 0
皮鞋 1184940.70 3
西装 366554.38 0
休闲便西 2151606.96 5
针织衫 206383.08 0
休闲衫 1502049.42 3
内裤 18134.58 0
皮夹 144500.75 0
针织T恤衫 8760890.97 22
背心 127267.75 0
……
将子查询作为值例表使用
当把子查询作为值列表使用时，它才会焕发出真正的光芒，在外部查询中可以使用 in 运算符
来把单个值（通常是一个例与子查询返回的值例表来进行比较）。当然此时这个子查询也必须只返回一个例。
格式：
select * from table where A in (select A from table2)
例句：要查找到由武汉加盟商订单号为DH1013-2007-08-24-0006所产生的所有发货单
select sellid as '发货单号',get_mchid as '加盟商',setsure_date as '发货日期' from d_sell where quotaid in
(select quotaid from d_quota where indentid='DH1013-2007-08-24-0006')
返回：
发货单号 加盟商 发货日期
-------------------------------------------------------------------------------------------------------------
PF1020-2008-01-10-0036 WH002 2008-01-10
PF1020-2008-01-26-0015 WH002 2008-01-27
PF1020-2008-02-27-0001 WH002 2008-02-27
PF1020-2008-03-04-0001 WH002 2008-03-04
PF1020-2008-03-04-0002 WH002 2008-03-04
PF1020-2008-03-07-0004 WH002 2008-03-07
PF1020-2008-03-10-0012 WH002 2008-03-11
PF1020-2008-03-12-0012 WH002 2008-03-13
PF1036-2008-03-26-0019 WH002 2008-03-26
使用子查询与连接查询哪一个才是最好呢？
这完全取决于你，依据复杂的程度，使用子查询的查询能够比使用连接的查询执行的更快。
因为它们在每一个步骤中都比上一个步骤筛选掉了更多的行，越复杂的子查询往往会在性能上比
大的连接查询执行的速度更快。应当根据自己的风格选择对你来说最易于理解，也是不容易出现
逻辑错误的方法，性能与正确性相比，正确才是第一位！
将子查询作为表使用：
往往可以利用它把复杂的查询问题分解为多个较小且相对容易解决的问题；
将子查询作为导出表使用，可以非常出色地解决一些聚合函数的计算问题，
在创建聚合查询的时候，每个列都必须参与聚合函数的计算，要么出现在 group by
子句中，要么出现聚合函数中，这样的约束使得在查询中返回附加的描述列变得
困难起来，可以使用这种方法：在子查询中执行聚合函数，并将找到的行传递
给外部查询作为导出表。然后使用外部查询返回所需的行
例句语法：
select tableA.id,tableA.name,tableB.sums from tableA join --//tableB.sums 为子查询返回的行
(select id,sum(sums) as sums from table group by id) tableB --//子查询结果作为一个表tableB
on tableA.id=tableB.id --//与子查询连接的条件
例句： 查询各加盟商的总发货额及他所在的地区
select distinct b.merchantid as '加盟商编号',b.shutname as '加盟商',
b.areaid as '地区',c.sums as '总金额' from d_sell a join
(select e.get_mchid,sum(f.set_nums*f.s_price) as sums from d_sell e --//子查询结果为分组各加盟商发货总额
join d_sellsub f on e.sellid=f.sellid and
e.set_sure=1 group by e.get_mchid) c
on a.get_mchid=c.get_mchid join j_merchant b --//与子查询进行连接
on a.get_mchid=b.merchantid
order by b.merchantid --//按加盟商编号进行排序
返回：
加盟商编号 加盟商 地区 总金额
-----------------------------------------------------------------
HL017 葫芦岛 006 1109733.45
JJ003 晋江 004 2722209.30
JL022 庄河 006 140185.80
JL023 吉林 008 1046362.40
JS731 吉首 002 76539.60
JX012 鸡西 005 59014.00
JZ025 锦州 006 390199.20
LPS01 六盘水 100 339114.90
LY021 辽阳 006 867310.40
……
采用子查询后外部查询就无需要再用 group by 进行分组了，
最关键的加明商的名称， 所在地区等不用都加入到 group by 子句了！
相关子查询
相关子查询引用了外部查询中的列,这种用外部查询来限制子查询的方法使得SQL查询变得
更加强大和灵活,因为相关子查询能够引用外部查询,所以它们尤其适合编写复杂的 where 条件;
尽管相关子查询具有一定的难度,但它所具有的灵活性和在提高性能方面的
潜力却值得我们花费精力和时间！
关键字：EXISTS
USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
请参考SQL联机丛书中关于关键字EXISTS的详解
3.使用并操作
并操作不同于连接，用关系代数的术语来说，并是加法而连接是乘法，与在水平方向上
扩展的连接不同，并操作将多个结果集都堆到同一张较大的表中去，在构造并查询时必须遵循
以下规则：
列的名字或者别名是由第一个 select 语句的选择列表决定的；
每个 select 语句选择列表中的列数目必须一样多，而且对应位置上的列必须具有兼容的数据类型；
可以为每个 select 语句都增加一个表示行的数据来源的表达式；
可以将并操作作为 select into 命令的一部份来使用，但是 into 关键字必须放在第一个 select 语句中；
虽然 select 命令在默认情况下不会去掉重复行，除非明确地为它指定 distinct 关键字，但是，并操作却与之相反：在默认情况下，并操作将会去掉重复的行；如果希望返回重复的行，就必须明确指定 all 关键字，建议在使用并操作时应当使用“union all” ；同样，在使用 top 关键字时应当使用“top with ties”。
用来对所有select 语句的并操作结果进行排序的 order by 子句，必须放到最后一个 select 后面，但它所用的排列名必须是第一个 selec 选择列表中的列名；
例句：
select userid,username,[password] as '密码或区域','one'
from j_user union all --//关键字：显示全部
select merchantid,shutname,areaid,'two' from j_merchant
order by userid --//排序
返回：
userid username 密码或区域 one
-------------------------------------------------------------------------------------
1002 蒲少芳 123 one
1003 刘友兵 123 one
1004 李洁 123 one
1005 邱礼芳 123 one
1006 邓淑华 123 one
1007 陈来灿 123 one
1008 刘思一 123 one
……
HL017 葫芦岛 006 two
JJ003 晋江 004 two
JJ688 靖江 007 two
JL022 庄河 006 two
JL023 吉林 008 two
JS731 吉首 002 two
JX012 鸡西 005 two
……
4.交操作
交操作用于查找两个数据集合所共有的行，内连接可以在水平方向上查找那些具有公共数据的行，
而交操作是在垂直方向上查找那些公共的行。
例句：要查找到 j_user.username与d_sell.setcr_name中同时存在的用户名，也叫操作员。
select distinct U.username from
(select distinct username from j_user
union all --//这里使用 union all 而不是union distinct 否则在并操作中就会消除重复的行
select distinct setcr_name from d_sell)U
group by username having count(*)>1 --//如果count(*)的结果大于1，那么说明同一个操作员在两个表中同时存在
说明：在两个 select 语句中都使用了 distinct 消除了各自结果集合中的重复记录，以免交查询因为它们而产生错误！
返回：
username
----------------------
邓淑华
古小琼
韩小素
何青倩
李洁
梁燕清
现将上面的SQL语句做一点更改，就可以查找出只属于第一个表而不属于第二个表的操作员：
select distinct U.cr_name as '只属于Quota表' from
(select distinct cr_name from d_quota
union all
select distinct cr_name from d_quota
union all
select distinct setcr_name from d_sell)U
group by cr_name having count(*)=2
返回：
只属于Quota表
--------------------------
翁伟云
杨静凯
扫一扫
1353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
