/*-- =================================================
Description:
多表查询
** ---------------------------------------------------
Environment: WIN 7 , SQL SERVER 2008
Database: TEST
Author: CC
Create date: 2012.01.19
** ---------------------------------------------------
explain:
通过实例查询结果来了解数据库各种连接方式
内、外连接、交叉连接、条件过滤连接
-- ================================================ */
USE test
GO
CREATE TABLE province_id(
p_id int,
season varchar(50)
)
INSERT INTO province_id
SELECT 1,'春节'
UNION ALL
SELECT 2,'夏季'
UNION ALL
SELECT 3, '秋季'
UNION ALL
SELECT 4, '冬季'
CREATE TABLE basedata(
id int,
name varchar(20),
province varchar(50)
)
alter table basedata alter column province varchar(15)
INSERT INTO basedata
SELECT 1,'张三','北京'
UNION ALL
SELECT 2,'李四','上海'
UNION ALL
SELECT 2,'王五','深圳'
UNION ALL
SELECT 2,'陈六','南京'
UNION ALL
SELECT 2,'曾七','重庆'
UNION ALL
SELECT 3,'李一','南阳'
UNION ALL
SELECT 4,'陈冬','吉林'
UNION ALL
SELECT 4,'陈醋','长春'
UNION ALL
SELECT 3,'禚高','襄阳'
UNION ALL
SELECT 1,'杨光','苏州'
INSERT INTO basedata
SELECT 5,'春夏',NULL
UNION ALL
SELECT 6,'秋冬',NULL
SELECT ID,name,province FROM basedata
GROUP BY id,name,province
ORDER BY ID
--连接主要分内连接，只返回两个表中的相关记录;另一种是外连接，先返回一个表中的所有行，然后返回第二个表中的相关行
/*--
1、内连接 目的：将一个表中的记录和另一个表中的记录的对应记录进行匹配，前提是两个表的相关列包含相同的值。
如果其中一个表的列值不同，或者根本没有值，查询将不会返回这些行。
inner join ... ON 和 join ... ON 相同 即为等值连接 --*/
SELECT * FROM province_id AS A WITH(NOLOCK)
INNER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
ORDER BY A.p_id
SELECT * FROM province_id AS A WITH(NOLOCK)
JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
ORDER BY A.p_id
SELECT * FROM province_id AS A WITH(NOLOCK), basedata AS B WITH(NOLOCK)
WHERE A.p_id=B.id
ORDER BY A.p_id
--老式写法
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
(10 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
(10 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
(10 行受影响)
/*
2、外链接用于从一个表中返回所有的行，然后匹配相关表中具有相同连接连值的行。外连接与内连接的区别是，在外连接中，查询将返回
第一个表中不匹配的行。。
LEFT OUTER JOIN ...ON 和 LEFT JOIN .... ON 相同
RIGHT OUTER JOIN ... ON 和RIGHT JOIN ... ON 相同
*/
SELECT * FROM province_id AS A WITH(NOLOCK)
LEFT JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
SELECT * FROM province_id AS A WITH(NOLOCK)
LEFT OUTER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
-----------------------------------------------
SELECT * FROM province_id AS A WITH(NOLOCK)
RIGHT JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
SELECT * FROM province_id AS A WITH(NOLOCK)
RIGHT OUTER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
(10 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
(10 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
1 春节 1 杨光 苏州
NULL NULL 5 春夏 NULL
NULL NULL 6 秋冬 NULL
(12 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
1 春节 1 杨光 苏州
NULL NULL 5 春夏 NULL
NULL NULL 6 秋冬 NULL
(12 行受影响)
/*
3、非同行连接
运算符 <> < > <= >=
4、特殊目的的连接运算
一、全连接
全连接或全外连接是一种不偏向两个中表的任务一个的外连接，用于返回连接两端不匹配的值
FULL OUTER JOIN 其实就是相当于查询两个表中的所有字段
*/
SELECT * FROM province_id AS A WITH(NOLOCK)
FULL OUTER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 1 杨光 苏州
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 3 禚高 襄阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
NULL NULL 5 春夏 NULL
NULL NULL 6 秋冬 NULL
(12 行受影响)
/*
二、交叉连接是所有连接的祖先，在使用交叉连接时，不需要指定过行值匹配的连接列，查询只是返回两个表中所有可能的行组合，而
不需要考虑列值的匹配。使用交叉连接会生成所谓的笛卡尔积，产生大量的行，这是用来测试数据填写测试数据库表的高效方法。
CROSS JOIN 其实就相当于 FROM A,B 两个表的无条件连接
*/
SELECT * FROM province_id AS A WITH(NOLOCK)
CROSS JOIN basedata AS B WITH(NOLOCK)
SELECT * FROM province_id AS A WITH(NOLOCK), basedata AS B WITH(NOLOCK)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 2 李四 上海
1 春节 2 王五 深圳
1 春节 2 陈六 南京
1 春节 2 曾七 重庆
1 春节 3 李一 南阳
1 春节 4 陈冬 吉林
1 春节 4 陈醋 长春
1 春节 3 禚高 襄阳
1 春节 1 杨光 苏州
1 春节 5 春夏 NULL
1 春节 6 秋冬 NULL
2 夏季 1 张三 北京
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
2 夏季 3 李一 南阳
2 夏季 4 陈冬 吉林
2 夏季 4 陈醋 长春
2 夏季 3 禚高 襄阳
2 夏季 1 杨光 苏州
2 夏季 5 春夏 NULL
2 夏季 6 秋冬 NULL
3 秋季 1 张三 北京
3 秋季 2 李四 上海
3 秋季 2 王五 深圳
3 秋季 2 陈六 南京
3 秋季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 4 陈冬 吉林
3 秋季 4 陈醋 长春
3 秋季 3 禚高 襄阳
3 秋季 1 杨光 苏州
3 秋季 5 春夏 NULL
3 秋季 6 秋冬 NULL
4 冬季 1 张三 北京
4 冬季 2 李四 上海
4 冬季 2 王五 深圳
4 冬季 2 陈六 南京
4 冬季 2 曾七 重庆
4 冬季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
4 冬季 3 禚高 襄阳
4 冬季 1 杨光 苏州
4 冬季 5 春夏 NULL
4 冬季 6 秋冬 NULL
(48 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
1 春节 1 张三 北京
1 春节 2 李四 上海
1 春节 2 王五 深圳
1 春节 2 陈六 南京
1 春节 2 曾七 重庆
1 春节 3 李一 南阳
1 春节 4 陈冬 吉林
1 春节 4 陈醋 长春
1 春节 3 禚高 襄阳
1 春节 1 杨光 苏州
1 春节 5 春夏 NULL
1 春节 6 秋冬 NULL
2 夏季 1 张三 北京
2 夏季 2 李四 上海
2 夏季 2 王五 深圳
2 夏季 2 陈六 南京
2 夏季 2 曾七 重庆
2 夏季 3 李一 南阳
2 夏季 4 陈冬 吉林
2 夏季 4 陈醋 长春
2 夏季 3 禚高 襄阳
2 夏季 1 杨光 苏州
2 夏季 5 春夏 NULL
2 夏季 6 秋冬 NULL
3 秋季 1 张三 北京
3 秋季 2 李四 上海
3 秋季 2 王五 深圳
3 秋季 2 陈六 南京
3 秋季 2 曾七 重庆
3 秋季 3 李一 南阳
3 秋季 4 陈冬 吉林
3 秋季 4 陈醋 长春
3 秋季 3 禚高 襄阳
3 秋季 1 杨光 苏州
3 秋季 5 春夏 NULL
3 秋季 6 秋冬 NULL
4 冬季 1 张三 北京
4 冬季 2 李四 上海
4 冬季 2 王五 深圳
4 冬季 2 陈六 南京
4 冬季 2 曾七 重庆
4 冬季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
4 冬季 3 禚高 襄阳
4 冬季 1 杨光 苏州
4 冬季 5 春夏 NULL
4 冬季 6 秋冬 NULL
(48 行受影响)
/*
三、使用谓词在连接子句中过滤记录 JOIN ON 连接条件 AND 条件
虽然如此，还是得小心来处理最好还是在内连接的时候使用。如下例：
*/
SELECT * FROM province_id AS A WITH(NOLOCK)
RIGHT JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id AND A.p_id>2
SELECT * FROM province_id AS A WITH(NOLOCK)
RIGHT JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
WHERE A.p_id>2
SELECT * FROM province_id AS A WITH(NOLOCK)
INNER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id
WHERE A.p_id>2
---
SELECT * FROM province_id AS A WITH(NOLOCK)
INNER JOIN basedata AS B WITH(NOLOCK)
ON A.p_id=B.id AND A.p_id>2
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
NULL NULL 1 张三 北京
NULL NULL 2 李四 上海
NULL NULL 2 王五 深圳
NULL NULL 2 陈六 南京
NULL NULL 2 曾七 重庆
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
NULL NULL 1 杨光 苏州
NULL NULL 5 春夏 NULL
NULL NULL 6 秋冬 NULL
(12 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
(4 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
(4 行受影响)
p_id season id name province
----------- -------------------------------------------------- ----------- -------------------- ---------------
3 秋季 3 李一 南阳
4 冬季 4 陈冬 吉林
4 冬季 4 陈醋 长春
3 秋季 3 禚高 襄阳
(4 行受影响)
/*
四、合并查询 连接查询（JOIN） 在水平方向上向上扩展了结果集，这就是说它将多个表的列加入到结果中，使结果变宽。
而UNION(合并)查询则垂直地扩展了结果，它将一个记录堆积在另个记录的顶部。
合并操作可以将多个相同列数的结果组合在一起，但列的数据类型必须是互相兼容的。
UNION DISTINCT 的缩写为UNION
UNION ALL运算符只是将两个结果连接在一起
这个练习在上面的插入数据已经运用了。不再在此举例。
*/
注：SQL 92的标准写法是INNER JOIN ... ON ,LEFT JOIN ...ON 如果只是用FROM 表1，表2 WHERE 连接条件为老式写法。
具体参看《SQL完全手册》