由于种种原因,今天比较晚了,不过还是得坚持写下去……
今天把多表联查的知识写下来,然后SELECT就差不多可以结束了,但如果后续发现有漏写的也会补上的。
多表联查很多人都觉得很难,的确,相对于单表查询,多表联查充满了各种可能,对数据的处理逻辑也会变得更加丰富。但是,鄙人想说的是,多表联查其实并不难,关键是要掌握方法。
多表联查大致分为一下几种:
1.并列查询
在ORACLE中,如果两条查询语句的结果列的数量相同,则可以做并 UNION、交 INTERSECT、差 MINUS的操作。例如:
SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '1' UNION SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '2';--查询两位学生上的所有课程编号。
SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '1' INTERSECT SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '2';--查询两位学生上的共同课程编号。
SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '1' MINUS SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = '2';--查询两位学生上的不同课程编号。
UNION和UNION ALL的区别:UNION会自动压缩多个结果集合中的重复结果,而UNION ALL则将所有的结果全部显示出来,不管是不是重复。性能上UNION优。
UNION:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
UNION ALL:对两个结果集进行并集操作,包括重复行,不进行排序;
2.笛卡尔积
笛卡尔积简单来说就是将连接的表中的记录做全组合操作,表现形式:列和列相加、行和行相乘(组合)。语法格式:
SELECT * FROM 表1,表2 WHERE 条件;
笛卡尔积的结果数是两表的元组数之积。例如:
SELECT E.LAST_NAME,D.NAME FROM EMP E,DEPT D WHERE E.DEPT_ID = D.ID AND E.LAST_NAME = 'Biri';
其中又有些特殊的情况,称之为非等值连接。例如:
SELECT E.ENAME,E.JOB,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
还有自连接。当表1和表2是同一张表时,就是属于自连接了。例如:
SELECT WORKER.LAST_NAME||'WORK FOR'||MANAGER.LAST_NAME FROM EMP WORKER, EMP MANAGER WHERE WORKER.MANAGER_ID = MANAGER.MANAGER_ID;
3.内连接
内连接的关键字是INNER JOIN,其中INNER可省略。语法格式:
SELECT * FROM 表1 INNER JOIN 表2 ON 连接条件;
内连接其实本质上和笛卡尔积是一样的,但是必须有连接条件、关键字是ON。
内连接一次只能针对两个表,如果针对多个表都需要内连接,则需要先将两张表连接后再与第三张表进行关联。语法格式:
SELECT * FROM 表1 JOIN 表2 ON 连接条件1 JOIN 表3 ON 连接条件2;
例如:
SELECT * FROM STUDENT T1,SCORE T2,COURSE T3 WHERE TI.STUDENT_ID = T2.STUDENT_ID AND T2.COURSE_ID = T3.COURSE_ID;--笛卡尔积的写法。
SELECT * FROM STUDENT T1 JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID JOIN COURSE T3 ON T2.COURSE_ID = T3.COURSE_ID;--内连接的写法。
需要注意的是,内连接和笛卡尔积对于不满足连接条件的记录都不会获取。
4.外连接
外连接分为左连接、右连接和全连接。其中,左外连接和右外连接没有本质上的区别,全连接是前两者的并集。
左连接:
关键字是LEFT OUTER JOIN,其中OUTER可省略。语法格式:
SELECT * FROM 表1 LEFT JOIN 表2 ON 连接条件;
以表1为基准表,将表2中符合连接条件的记录与左表进行连接。如果左表中的记录在右表中找不到符合连接条件的记录,则会使用空记录和左表的记录进行连接。左连接保证了最终的结果集中肯定会包含左表中的所有记录。
右连接:
关键字是RIGHT OUTER JOIN,其中OUTER可省略。语法格式:
SELECT * FROM 表1 RIGHT JOIN 表2 ON 连接条件;
以表2为基准表,将表1中符合连接条件的记录与右表进行连接。如果右表中的记录在左表中找不到符合连接条件的记录,则会使用空记录和右表的记录进行连接。右连接保证了最终的结果集中肯定会包含右表中的所有记录。
全连接:
关键字是FULL JOIN,语法格式:
SELECT * FROM 表1 FULL JOIN 表2 ON 连接条件;
结果是左连接和右连接的并集。
例如:
SELECT * FROM STUDENT T1,SCORE T2 WHERE T1.STUDENT_ID = T2.STUDENT_ID;--笛卡尔积的写法。
SELECT * FROM STUDENT T1 JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID;--内连接的写法。
SELECT * FROM STUDENT T1 LEFT JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID;--左连接的写法。
SELECT * FROM STUDENT T1 RIGHT JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID;--右连接的写法。
这里需要特别强调一点,在外连接中,如果连接条件不满足,结果一样会有数据,其中,左连接是全部左表数据+右表空值,右连接是左表空值+全部右表数据,全连接则是两者的并集。
USING:
当使用ON作为条件关键字时(内连接、外连接),如果连接条件中的两个表的字段名称一样,则可以使用USING,即ON T1.COLUMN = T2.COLUMN可以写成USING(COLUMN)。例如:
SELECT * FROM STUDENT T1 LEFT JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID;--左连接的写法。
可写成
SELECT * FROM STUDENT T1 LEFT JOIN SCORE T2 USING(STUDENT_ID);--左连接的写法。
(+):
用在where条件中,表示外连接。
T1.字段(+)=T2.字段,则表示以T2为基础表,实现外连接。
T1.字段=T2.字段(+),则表示以T1为基础表,实现外连接。
例如:
SELECT * FROM STUDENT T1,SCORE T2 WHERE T1.STUDENT_ID = T2.STUDENT_ID;--笛卡尔积的写法。
SELECT * FROM STUDENT T1 LEFT JOIN SCORE T2 ON T1.STUDENT_ID = T2.STUDENT_ID;--左外连接,以T1为基准表。
SELECT * FROM STUDENT T1,SCORE T2 WHERE T1.STUDENT_ID = T2.STUDENT_ID(+);--外连接,以T1为基准表。