本文介绍以下内容:
LFET JOIN、RIGHT JOIN、INNER JOIN、UNION、UNION ALL、FULL JOIN等!
测试数据(以下数据未经考证,非真实有效数据,仅作为本次学习的测试数据!)
全国大学排名TOP20
No. | 校名 | 地区 | 校长 | 类型 |
1 | 北京大学 | 北京 | 周其凤 | 综合 |
2 | 清华大学 | 北京 | 顾秉林 | 理工 |
3 | 浙江大学 | 浙江 | 杨卫 | 综合 |
4 | 复旦大学 | 上海 | 杨玉良 | 综合 |
5 | 南京大学 | 江苏 | 陈俊 | 综合 |
6 | 上海交通大学 | 上海 | 张杰 | 综合 |
7 | 武汉大学 | 湖北 | 李晓红 | 综合 |
8 | 中国人民大学 | 北京 | 纪宝成 | 综合 |
9 | 华中科技大学 | 湖北 | 李培根 | 理工 |
10 | 中山大学 | 广东 | 许宁生 | 综合 |
11 | 吉林大学 | 吉林 | 展涛 | 综合 |
12 | 四川大学 | 四川 | 谢和平 | 综合 |
13 | 北京师范大学 | 北京 | 钟秉林 | 师范 |
14 | 南开大学 | 天津 | 龚克 | 综合 |
15 | 中南大学 | 湖南 | 黄伯云 | 综合 |
16 | 山东大学 | 山东 | 徐显明 | 综合 |
17 | 哈尔滨工业大学 | 黑龙江 | 王树国 | 理工 |
18 | 中国科技大学 | 安徽 | 侯建国 | 理工 |
19 | 西安交通大学 | 陕西 | 郑南宁 | 综合 |
20 | 厦门大学 | 福建 | 朱崇实 | 综合 |
数据库表结构和数据SQL(使用Navicat从MYSQL导出):
1 /*
2 Navicat Oracle Data Transfer
3 Oracle Client Version : 11.2.0.2.0
4
5 Source Server : xe
6 Source Server Version : 110200
7 Source Host : 127.0.0.1:1522
8 Source Schema : HR
9
10 Target Server Type : ORACLE
11 Target Server Version : 110200
12 File Encoding : 65001
13
14 Date: 2014-12-04 12:44:54
15 */
16
17
18 -- ----------------------------
19 -- Table structure for "HR"."SCHOOL"
20 -- ----------------------------
21 DROP TABLE "HR"."SCHOOL";
22 CREATE TABLE "HR"."SCHOOL" (
23 "SCHOOLID" VARCHAR2(100 BYTE) NOT NULL ,
24 "SCHOOLNAME" VARCHAR2(50 BYTE) NULL ,
25 "SCHOOLLOCATION" VARCHAR2(20 BYTE) NULL ,
26 "SCHOOLTYPE" VARCHAR2(5 BYTE) NULL ,
27 "SORTKEY" VARCHAR2(2 BYTE) NULL
28 )
29 LOGGING
30 NOCOMPRESS
31 NOCACHE
32
33 ;
34
35 -- ----------------------------
36 -- Records of SCHOOL
37 -- ----------------------------
38 INSERT INTO "HR"."SCHOOL" VALUES ('C15BD0AB3EF4468A96557A870293BA88', '北京大学', '北京', '1', '01');
39 INSERT INTO "HR"."SCHOOL" VALUES ('FCBB60720A9749B1AE72CBE5BE388E22', '清华大学', '北京', '2', '02');
40 INSERT INTO "HR"."SCHOOL" VALUES ('12276D26BC4F4197B42D950681F924AB', '浙江大学', '浙江', '1', '03');
41 INSERT INTO "HR"."SCHOOL" VALUES ('40280309A8784F68ACB786E5B5DE556A', '复旦大学', '上海', '1', '04');
42 INSERT INTO "HR"."SCHOOL" VALUES ('8150DCFA7F6144D6A77A109977538047', '南京大学', '江苏', '1', '05');
43 INSERT INTO "HR"."SCHOOL" VALUES ('44E4F771356C465DB02AD54A01C2ABDE', '上海交通大学', '上海', '1', '06');
44 INSERT INTO "HR"."SCHOOL" VALUES ('4A3763D5528B49C299489C151620CD19', '武汉大学', '湖北', '1', '07');
45 INSERT INTO "HR"."SCHOOL" VALUES ('D27B3A5FB8AA489A987D14288871CC6E', '中国人民大学', '北京', '1', '08');
46 INSERT INTO "HR"."SCHOOL" VALUES ('2B849F065600462C8D45BC2E781301F2', '华中科技大学', '湖北', '2', '09');
47 INSERT INTO "HR"."SCHOOL" VALUES ('54D90CFD0F104EFCB299D5098BF99386', '中山大学', '广东', '1', '10');
48 INSERT INTO "HR"."SCHOOL" VALUES ('0BAEEEBEE1A444EAA33FF61652F80F73', '吉林大学', '吉林', '1', '11');
49 INSERT INTO "HR"."SCHOOL" VALUES ('BF8085E385004A5295F950F390C67475', '四川大学', '四川', '1', '12');
50 INSERT INTO "HR"."SCHOOL" VALUES ('B45EA8CDE6554555921B7D293BAC36F2', '北京师范大学', '北京', '3', '13');
51 INSERT INTO "HR"."SCHOOL" VALUES ('D4D879DAF2E14E6DA129EC8E46F1E5D4', '南开大学', '天津', '1', '14');
52 INSERT INTO "HR"."SCHOOL" VALUES ('8A17B2CC81B64DF78CCE0D75E21BF77B', '中南大学', '湖南', '1', '15');
53 INSERT INTO "HR"."SCHOOL" VALUES ('5C354FDA736C4D189C97BEE8DDD0D0E7', '山东大学', '山东', '1', '16');
54 INSERT INTO "HR"."SCHOOL" VALUES ('292812F98C394CB5846CFA1448094A24', '哈尔滨工业大学', '黑龙江', '2', '17');
55 INSERT INTO "HR"."SCHOOL" VALUES ('0C0D5616B55E42139242625E58C2D389', '中国科技大学', '安徽', '2', '18');
56 INSERT INTO "HR"."SCHOOL" VALUES ('C50259B9240F4C88B38B954575079343', '西安交通大学', '陕西', '1', '19');
57 INSERT INTO "HR"."SCHOOL" VALUES ('977A97CC47C4482BA9A9359AAC4CEF66', '厦门大学', '福建', '1', '20');
58
59 -- ----------------------------
60 -- Table structure for "HR"."SCHOOLMASTER"
61 -- ----------------------------
62 DROP TABLE "HR"."SCHOOLMASTER";
63 CREATE TABLE "HR"."SCHOOLMASTER" (
64 "ID" VARCHAR2(100 BYTE) NOT NULL ,
65 "NAME" VARCHAR2(50 BYTE) NULL ,
66 "SCHOOLID" VARCHAR2(100 BYTE) NULL
67 )
68 LOGGING
69 NOCOMPRESS
70 NOCACHE
71
72 ;
73
74 -- ----------------------------
75 -- Records of SCHOOLMASTER
76 -- ----------------------------
77 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('B4E99F17053B4DD9BFF4EA27D3DB9F6B', '周其凤', 'C15BD0AB3EF4468A96557A870293BA88');
78 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('FA4CE2FB3B264F0A8BFB55B638D46BC1', '顾秉林', 'FCBB60720A9749B1AE72CBE5BE388E22');
79 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('5FD7213082F14FA9A34B59CA614F6CFA', '杨卫', '12276D26BC4F4197B42D950681F924AB');
80 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('ACC1B581908D49538DF97E06D15B9EEB', '杨玉良', '40280309A8784F68ACB786E5B5DE556A');
81 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('5496350FC7F0455A96268FC76AE11A01', '陈俊', '8150DCFA7F6144D6A77A109977538047');
82 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('D1993E4EEB9A4017BA2D46E0EA16D1C6', '张杰', '44E4F771356C465DB02AD54A01C2ABDE');
83 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('CA9525B7E1AA41C984E26DEA95381FD9', '李晓红', '4A3763D5528B49C299489C151620CD19');
84 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('339616FE14BE4D3282C3AA1BCDD18155', '纪宝成', 'D27B3A5FB8AA489A987D14288871CC6E');
85 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('2BE4929EBA0D4AB983B2279D3C12711D', '李培根', '2B849F065600462C8D45BC2E781301F2');
86 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('415F43C2F25C4A26BDEADBA1668E8177', '许宁生', '54D90CFD0F104EFCB299D5098BF99386');
87 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('4D5FD475D8204070B49944A01DB6F768', '展涛', '0BAEEEBEE1A444EAA33FF61652F80F73');
88 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('D49DF755223C442EA99449193D98A465', '谢和平', 'BF8085E385004A5295F950F390C67475');
89 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('5C5E20AEE6B44F9E8042010FB833E2E4', '钟秉林', 'B45EA8CDE6554555921B7D293BAC36F2');
90 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('CC463BB9D6CB4D2DA8B1687A1138E73E', '龚克', 'D4D879DAF2E14E6DA129EC8E46F1E5D4');
91 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('9E57F240DF0B4F57BBC20D0FE1CA118C', '黄伯云', '8A17B2CC81B64DF78CCE0D75E21BF77B');
92 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('49FBE5183F5A43E4B975B654A3399F61', '徐显明', '5C354FDA736C4D189C97BEE8DDD0D0E7');
93 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('89381DEB8AFA482B9769DF8F024F04B2', '王树国', '292812F98C394CB5846CFA1448094A24');
94 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('DD0C1777563B49E7B4E98FCBC3A77E0D', '侯建国', '0C0D5616B55E42139242625E58C2D389');
95 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('921BA4BC616244C68AE017BE623EE33A', '郑南宁', 'C50259B9240F4C88B38B954575079343');
96 INSERT INTO "HR"."SCHOOLMASTER" VALUES ('D49EC73FA4D3470B8F355812B0BD4CA3', '朱崇实', '977A97CC47C4482BA9A9359AAC4CEF66');
97
98 -- ----------------------------
99 -- Table structure for "HR"."SCHOOLTYPE"
100 -- ----------------------------
101 DROP TABLE "HR"."SCHOOLTYPE";
102 CREATE TABLE "HR"."SCHOOLTYPE" (
103 "TYPEID" VARCHAR2(5 BYTE) NOT NULL ,
104 "TYPENAME" VARCHAR2(50 BYTE) NULL
105 )
106 LOGGING
107 NOCOMPRESS
108 NOCACHE
109
110 ;
111
112 -- ----------------------------
113 -- Records of SCHOOLTYPE
114 -- ----------------------------
115 INSERT INTO "HR"."SCHOOLTYPE" VALUES ('1', '综合');
116 INSERT INTO "HR"."SCHOOLTYPE" VALUES ('2', '理工');
117 INSERT INTO "HR"."SCHOOLTYPE" VALUES ('3', '师范');
118 INSERT INTO "HR"."SCHOOLTYPE" VALUES ('4', '其他');
119
120 -- ----------------------------
121 -- Indexes structure for table SCHOOL
122 -- ----------------------------
123
124 -- ----------------------------
125 -- Checks structure for table "HR"."SCHOOL"
126 -- ----------------------------
127 ALTER TABLE "HR"."SCHOOL" ADD CHECK ("SCHOOLID" IS NOT NULL);
128
129 -- ----------------------------
130 -- Primary Key structure for table "HR"."SCHOOL"
131 -- ----------------------------
132 ALTER TABLE "HR"."SCHOOL" ADD PRIMARY KEY ("SCHOOLID");
133
134 -- ----------------------------
135 -- Checks structure for table "HR"."SCHOOLMASTER"
136 -- ----------------------------
137 ALTER TABLE "HR"."SCHOOLMASTER" ADD CHECK ("ID" IS NOT NULL);
138
139 -- ----------------------------
140 -- Indexes structure for table SCHOOLTYPE
141 -- ----------------------------
142
143 -- ----------------------------
144 -- Checks structure for table "HR"."SCHOOLTYPE"
145 -- ----------------------------
146 ALTER TABLE "HR"."SCHOOLTYPE" ADD CHECK ("TYPEID" IS NOT NULL);
147
148 -- ----------------------------
149 -- Primary Key structure for table "HR"."SCHOOLTYPE"
150 -- ----------------------------
151 ALTER TABLE "HR"."SCHOOLTYPE" ADD PRIMARY KEY ("TYPEID");
152
153 -- ----------------------------
154 -- Foreign Key structure for table "HR"."SCHOOL"
155 -- ----------------------------
156 ALTER TABLE "HR"."SCHOOL" ADD FOREIGN KEY ("SCHOOLTYPE") REFERENCES "HR"."SCHOOLTYPE" ("TYPEID");
157
158 -- ----------------------------
159 -- Foreign Key structure for table "HR"."SCHOOLMASTER"
160 -- ----------------------------
161 ALTER TABLE "HR"."SCHOOLMASTER" ADD FOREIGN KEY ("SCHOOLID") REFERENCES "HR"."SCHOOL" ("SCHOOLID") DISABLE;
View Code
利用以下SQL可以查询出以上的表格并了解表之间的关系:
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
修改部分测试数据:
SCHOOL:
BF8085E385004A5295F950F390C67476 四川大学 四川 1 12
5C354FDA736C4D189C97BEE8DDD0D0E6 山东大学 山东 1 16
-->
BF8085E385004A5295F950F390C67475 四川大学 四川 1 12
5C354FDA736C4D189C97BEE8DDD0D0E7 山东大学 山东 1 16
SCHOOLMASTER:
5496350FC7F0455A96268FC76AE11A01 陈俊 8150DCFA7F6144D6A77A109977538047
-->
5496350FC7F0455A96268FC76AE11A01 陈俊 8150DCFA7F6144D6A77A109977538046
继续使用以上SQL查询出高校排名:
查询结果如下表:
01 | 北京大学 | 北京 | 周其凤 | 综合 |
02 | 清华大学 | 北京 | 顾秉林 | 理工 |
03 | 浙江大学 | 浙江 | 杨卫 | 综合 |
04 | 复旦大学 | 上海 | 杨玉良 | 综合 |
05 | 上海交通大学 | 上海 | 张杰 | 综合 |
06 | 武汉大学 | 湖北 | 李晓红 | 综合 |
07 | 中国人民大学 | 北京 | 纪宝成 | 综合 |
08 | 华中科技大学 | 湖北 | 李培根 | 理工 |
09 | 中山大学 | 广东 | 许宁生 | 综合 |
10 | 吉林大学 | 吉林 | 展涛 | 综合 |
11 | 北京师范大学 | 北京 | 钟秉林 | 师范 |
12 | 南开大学 | 天津 | 龚克 | 综合 |
13 | 中南大学 | 湖南 | 黄伯云 | 综合 |
14 | 哈尔滨工业大学 | 黑龙江 | 王树国 | 理工 |
15 | 中国科技大学 | 安徽 | 侯建国 | 理工 |
16 | 西安交通大学 | 陕西 | 郑南宁 | 综合 |
17 | 厦门大学 | 福建 | 朱崇实 | 综合 |
数据库中查询分为:内连接、外连接、全连接,其中外连接分为左外连接(左连接)、右外连接(右连接)。
以下练习只关注表SCHOOL和表SCHOOLMASTER之间的连接关系,不关注表SCHOOL和表SCHOOLTYPE之间的连接关系,所以表SCHOOL和表SCHOOLTYPE之间使用LEFT JOIN进行联合查询。
左外连接(左连接):
使用以上测试数据,查询所有大学的校名和校长:
SQL:
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
LEFT JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
LEFT JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
查询结果:
01 | 北京大学 | 北京 | 周其凤 | 综合 |
02 | 清华大学 | 北京 | 顾秉林 | 理工 |
03 | 浙江大学 | 浙江 | 杨卫 | 综合 |
04 | 复旦大学 | 上海 | 杨玉良 | 综合 |
05 | 南京大学 | 江苏 | | 综合 |
06 | 上海交通大学 | 上海 | 张杰 | 综合 |
07 | 武汉大学 | 湖北 | 李晓红 | 综合 |
08 | 中国人民大学 | 北京 | 纪宝成 | 综合 |
09 | 华中科技大学 | 湖北 | 李培根 | 理工 |
10 | 中山大学 | 广东 | 许宁生 | 综合 |
11 | 吉林大学 | 吉林 | 展涛 | 综合 |
12 | 四川大学 | 四川 | | 综合 |
13 | 北京师范大学 | 北京 | 钟秉林 | 师范 |
14 | 南开大学 | 天津 | 龚克 | 综合 |
15 | 中南大学 | 湖南 | 黄伯云 | 综合 |
16 | 山东大学 | 山东 | | 综合 |
17 | 哈尔滨工业大学 | 黑龙江 | 王树国 | 理工 |
18 | 中国科技大学 | 安徽 | 侯建国 | 理工 |
19 | 西安交通大学 | 陕西 | 郑南宁 | 综合 |
20 | 厦门大学 | 福建 | 朱崇实 | 综合 |
左连接查询:以左表(LEFT JOIN左边的表:表SCHOOL)为主表,即使右表(LEFT JOIN右边的表:表SCHOOLMASTER)没有匹配的数据,也从左表返回所有行数据。
以上SQL还可以写成
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
LEFT OUTER JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
LEFT OUTER JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
这也是左外连接简称为左连接的原因。
或者
SELECT
SCHOOL.SORTKEY,
SCHOOL.SCHOOLNAME,
SCHOOL.SCHOOLLOCATION,
SCHOOLMASTER.NAME,
SCHOOLTYPE.TYPENAME
FROM
SCHOOL,
SCHOOLMASTER,
SCHOOLTYPE
WHERE
SCHOOL.SCHOOLID = SCHOOLMASTER.SCHOOLID(+)
AND SCHOOL.SCHOOLTYPE = SCHOOLTYPE.TYPEID(+)
ORDER BY
SCHOOL.SORTKEY
以上SQL也是用了左连接,因为(+)在右侧,所以“(+)”所在位置的另一侧为连接的方向,不过这种方式已经过时了。
右外连接(右连接):
使用以上测试数据,查询所有校长管理的学校:
SQL:
SELECT
M.NAME,
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
T.TYPENAME
FROM
SCHOOL S
RIGHT JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
LEFT JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
查询结果:
周其凤 | 01 | 北京大学 | 北京 | 综合 |
顾秉林 | 02 | 清华大学 | 北京 | 理工 |
杨卫 | 03 | 浙江大学 | 浙江 | 综合 |
杨玉良 | 04 | 复旦大学 | 上海 | 综合 |
张杰 | 06 | 上海交通大学 | 上海 | 综合 |
李晓红 | 07 | 武汉大学 | 湖北 | 综合 |
纪宝成 | 08 | 中国人民大学 | 北京 | 综合 |
李培根 | 09 | 华中科技大学 | 湖北 | 理工 |
许宁生 | 10 | 中山大学 | 广东 | 综合 |
展涛 | 11 | 吉林大学 | 吉林 | 综合 |
钟秉林 | 13 | 北京师范大学 | 北京 | 师范 |
龚克 | 14 | 南开大学 | 天津 | 综合 |
黄伯云 | 15 | 中南大学 | 湖南 | 综合 |
王树国 | 17 | 哈尔滨工业大学 | 黑龙江 | 理工 |
侯建国 | 18 | 中国科技大学 | 安徽 | 理工 |
郑南宁 | 19 | 西安交通大学 | 陕西 | 综合 |
朱崇实 | 20 | 厦门大学 | 福建 | 综合 |
陈俊 | | | | |
徐显明 | | | | |
谢和平 | | | | |
右连接查询:以右表(RIGHT JOIN右边的表:表SCHOOLMASTER)为主表,即使左表(RIGHT JOIN左边的表:表SCHOOL)没有匹配的数据,也从右表返回所有行数据。
以上SQL还可以写成
SELECT
M.NAME,
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
T.TYPENAME
FROM
SCHOOL S
RIGHT OUTER JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
LEFT JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
这也是右外连接简称为右连接的原因。
或者
SELECT
SCHOOLMASTER.NAME,
SCHOOL.SORTKEY,
SCHOOL.SCHOOLNAME,
SCHOOL.SCHOOLLOCATION,
SCHOOLTYPE.TYPENAME
FROM
SCHOOL,
SCHOOLMASTER,
SCHOOLTYPE
WHERE
SCHOOL.SCHOOLID(+) = SCHOOLMASTER.SCHOOLID
AND SCHOOL.SCHOOLTYPE = SCHOOLTYPE.TYPEID(+)
ORDER BY
SCHOOL.SORTKEY
以上SQL也是用了右连接,因为(+)在左侧,所以“(+)”所在位置的另一侧为连接的方向。
学习完左连接和右连接后,比较一下以下的SQL:
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME
FROM
SCHOOL S
LEFT JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
ORDER BY
S.SORTKEY
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME
FROM
SCHOOLMASTER M
RIGHT JOIN SCHOOL S ON S.SCHOOLID = M.SCHOOLID
ORDER BY
S.SORTKEY
运行以上两句SQL后,发现查询结果是一致的。虽然第一句SQL使用的是LEFT JOIN,而第二句使用的是RIGHT JOIN。
根据上文中左连接查询、右连接查询的定义:
左连接查询:以左表为主表,即使右表没有匹配的数据,也从左表返回所有行数据。
右连接查询:以右表为主表,即使左表没有匹配的数据,也从右表返回所有行数据。
分析以上两句SQL。
第一句(LEFT JOIN):因为使用了左连接查询,所以以左表SCHOOL为主表,返回查询结果;
第二句(RITHT JOIN):因为使用了右连接查询,所以以右表SCHOOL为主表,返回查询结果。
所以主表为同一数据表,查询结果相同。
也就是说在软件开发过程中,针对某一功能需求,使用左连接查询或者右连接查询以及其他查询都是可以的,只要查询结果正确即可。
同时,也可以用多种查询方式检查SQL的正确性。
左连接查询、右连接查询等各种查询方式并没有错误,只要符合功能需求即可。这一观点在后文以及以后的工作中也会得到证实。
内连接查询:
使用以上测试数据,查询所有学校和校长,特别指出的是,没有校长的学校、没有领导学校的校长不要显示:
SQL:
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
查询结果:
01 | 北京大学 | 北京 | 周其凤 | 综合 |
02 | 清华大学 | 北京 | 顾秉林 | 理工 |
03 | 浙江大学 | 浙江 | 杨卫 | 综合 |
04 | 复旦大学 | 上海 | 杨玉良 | 综合 |
06 | 上海交通大学 | 上海 | 张杰 | 综合 |
07 | 武汉大学 | 湖北 | 李晓红 | 综合 |
08 | 中国人民大学 | 北京 | 纪宝成 | 综合 |
09 | 华中科技大学 | 湖北 | 李培根 | 理工 |
10 | 中山大学 | 广东 | 许宁生 | 综合 |
11 | 吉林大学 | 吉林 | 展涛 | 综合 |
13 | 北京师范大学 | 北京 | 钟秉林 | 师范 |
14 | 南开大学 | 天津 | 龚克 | 综合 |
15 | 中南大学 | 湖南 | 黄伯云 | 综合 |
17 | 哈尔滨工业大学 | 黑龙江 | 王树国 | 理工 |
18 | 中国科技大学 | 安徽 | 侯建国 | 理工 |
19 | 西安交通大学 | 陕西 | 郑南宁 | 综合 |
20 | 厦门大学 | 福建 | 朱崇实 | 综合 |
内连接查询:如果表中有至少一个匹配,则返回行数据。
以上SQL还可以写成
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
INNER JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
INNER JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
也就是说INNER JOIN 等同于 JOIN,为了方便起见,一般直接使用JOIN。
UNION、UNION ALL:
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL:
(
SELECT
S.SCHOOLID ID,
S.SCHOOLNAME NAME
FROM
SCHOOL S
)
UNION
(
SELECT
M . ID ID,
M . NAME NAME
FROM
SCHOOLMASTER M
)
UNION
(
SELECT
TYPEID ID,
TYPENAME NAME
FROM
SCHOOLTYPE
)
查询结果:
0BAEEEBEE1A444EAA33FF61652F80F73 | 吉林大学 |
0C0D5616B55E42139242625E58C2D389 | 中国科技大学 |
1 | 综合 |
12276D26BC4F4197B42D950681F924AB | 浙江大学 |
2 | 理工 |
292812F98C394CB5846CFA1448094A24 | 哈尔滨工业大学 |
2B849F065600462C8D45BC2E781301F2 | 华中科技大学 |
2BE4929EBA0D4AB983B2279D3C12711D | 李培根 |
3 | 师范 |
339616FE14BE4D3282C3AA1BCDD18155 | 纪宝成 |
4 | 其他 |
40280309A8784F68ACB786E5B5DE556A | 复旦大学 |
415F43C2F25C4A26BDEADBA1668E8177 | 许宁生 |
44E4F771356C465DB02AD54A01C2ABDE | 上海交通大学 |
49FBE5183F5A43E4B975B654A3399F61 | 徐显明 |
4A3763D5528B49C299489C151620CD19 | 武汉大学 |
4D5FD475D8204070B49944A01DB6F768 | 展涛 |
5496350FC7F0455A96268FC76AE11A01 | 陈俊 |
54D90CFD0F104EFCB299D5098BF99386 | 中山大学 |
5C354FDA736C4D189C97BEE8DDD0D0E6 | 山东大学 |
5C5E20AEE6B44F9E8042010FB833E2E4 | 钟秉林 |
5FD7213082F14FA9A34B59CA614F6CFA | 杨卫 |
8150DCFA7F6144D6A77A109977538047 | 南京大学 |
89381DEB8AFA482B9769DF8F024F04B2 | 王树国 |
8A17B2CC81B64DF78CCE0D75E21BF77B | 中南大学 |
921BA4BC616244C68AE017BE623EE33A | 郑南宁 |
977A97CC47C4482BA9A9359AAC4CEF66 | 厦门大学 |
9E57F240DF0B4F57BBC20D0FE1CA118C | 黄伯云 |
ACC1B581908D49538DF97E06D15B9EEB | 杨玉良 |
B45EA8CDE6554555921B7D293BAC36F2 | 北京师范大学 |
B4E99F17053B4DD9BFF4EA27D3DB9F6B | 周其凤 |
BF8085E385004A5295F950F390C67476 | 四川大学 |
C15BD0AB3EF4468A96557A870293BA88 | 北京大学 |
C50259B9240F4C88B38B954575079343 | 西安交通大学 |
CA9525B7E1AA41C984E26DEA95381FD9 | 李晓红 |
CC463BB9D6CB4D2DA8B1687A1138E73E | 龚克 |
D1993E4EEB9A4017BA2D46E0EA16D1C6 | 张杰 |
D27B3A5FB8AA489A987D14288871CC6E | 中国人民大学 |
D49DF755223C442EA99449193D98A465 | 谢和平 |
D49EC73FA4D3470B8F355812B0BD4CA3 | 朱崇实 |
D4D879DAF2E14E6DA129EC8E46F1E5D4 | 南开大学 |
DD0C1777563B49E7B4E98FCBC3A77E0D | 侯建国 |
FA4CE2FB3B264F0A8BFB55B638D46BC1 | 顾秉林 |
FCBB60720A9749B1AE72CBE5BE388E22 | 清华大学 |
以上SQL仅仅为了说明UNION的功能,没有具体意义。
当列数据有重复时,UNION只能查询出一条记录,这时候就需要使用UNION ALL进行查询。
全连接查询:
SQL:
SELECT
S.SORTKEY,
S.SCHOOLNAME,
S.SCHOOLLOCATION,
M.NAME,
T.TYPENAME
FROM
SCHOOL S
FULL JOIN SCHOOLMASTER M ON S.SCHOOLID = M.SCHOOLID
LEFT JOIN SCHOOLTYPE T ON S.SCHOOLTYPE = T.TYPEID
ORDER BY
S.SORTKEY
查询结果:
01 | 北京大学 | 北京 | 周其凤 | 综合 |
02 | 清华大学 | 北京 | 顾秉林 | 理工 |
03 | 浙江大学 | 浙江 | 杨卫 | 综合 |
04 | 复旦大学 | 上海 | 杨玉良 | 综合 |
05 | 南京大学 | 江苏 | | 综合 |
06 | 上海交通大学 | 上海 | 张杰 | 综合 |
07 | 武汉大学 | 湖北 | 李晓红 | 综合 |
08 | 中国人民大学 | 北京 | 纪宝成 | 综合 |
09 | 华中科技大学 | 湖北 | 李培根 | 理工 |
10 | 中山大学 | 广东 | 许宁生 | 综合 |
11 | 吉林大学 | 吉林 | 展涛 | 综合 |
12 | 四川大学 | 四川 | | 综合 |
13 | 北京师范大学 | 北京 | 钟秉林 | 师范 |
14 | 南开大学 | 天津 | 龚克 | 综合 |
15 | 中南大学 | 湖南 | 黄伯云 | 综合 |
16 | 山东大学 | 山东 | | 综合 |
17 | 哈尔滨工业大学 | 黑龙江 | 王树国 | 理工 |
18 | 中国科技大学 | 安徽 | 侯建国 | 理工 |
19 | 西安交通大学 | 陕西 | 郑南宁 | 综合 |
20 | 厦门大学 | 福建 | 朱崇实 | 综合 |
| | | 谢和平 | |
| | | 陈俊 | |
| | | 徐显明 | |
全连接查询:只要其中某个表存在匹配,就返回行数据。