本文介绍以下内容:
LFET JOIN、RIGHT JOIN、INNER JOIN、UNION、UNION ALL、FULL JOIN等!
测试数据(以下数据未经考证,非真实有效数据,仅作为本次学习的测试数据!)
|
数据库表结构和数据SQL(使用Navicat从MYSQL导出):
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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;
利用以下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查询出高校排名:
查询结果如下表:
|
数据库中查询分为:内连接、外连接、全连接,其中外连接分为左外连接(左连接)、右外连接(右连接)。
以下练习只关注表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
查询结果:
|
左连接查询:以左表(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
查询结果:
|
右连接查询:以右表(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
查询结果:
|
内连接查询:如果表中有至少一个匹配,则返回行数据。
以上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 )
查询结果:
|
以上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
查询结果:
|
全连接查询:只要其中某个表存在匹配,就返回行数据。