连接查询(JOIN)
实际应用中所需要的数据,经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询。连接查询通常建立在存在相互关系的父子表之间。
相关概念
笛卡尔乘积运算定义了一个关系,两个集合X和Y的笛卡尓积,又称直积,表示为X × Y。如果X关系有I个元组、N个属性,而Y关系有J个元组、M个属性,则他们的笛卡尔乘积将会有“I×J”个元组、“N+M”个属性。两个关系中可能有同名属性。
例如,areas表和employments表做笛卡尔乘积运算。
--areas表中有4条记录 SELECT COUNT(*) FROM areas;
COUNT(*) -------------------- 4 1 rows fetched.
--employments表中有19条记录 SELECT COUNT(*) FROM employments;
COUNT(*) -------------------- 19 1 rows fetched.
--对areas和employments两个表做不指定条件的连接查询时,会得到76(4×19)条记录 SELECT areas.area_name, employments.employment_id FROM areas, employments;
语法格式
SELECT [ , ... ] FROM table_reference [LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | INNER] JOIN table_reference [ON { predicate } [ { AND | OR } condition ] [ , ... n ]]
- table_reference 子句:
{ table_name [ [AS] alias ] | view_name [ [AS] alias] | ( select query ) [ [AS] alias ] }
- 外连接操作符(+)
外连接不仅可以使用LEFT/RIGHT关键字来描述,也可以使用外连接操作符(+)来表示。WHERE子句中含有(+)的条件属于外连接的关联条件,其中含有(+)一侧的表属于左连接的右节点,不含(+)一侧的表属于左连接的左节点。
- 外连接操作符的支持范围或约束条件如下:
- 存在join关键字时,不可以再出现(+);
- (+)前面仅可以修饰表字段;
- (+)仅支持出现在where子句中,且含(+)的条件不隶属OR字句;
- (+)的compare条件仅支持6种运算符:=、<>、>、<、>=、<=;
- (+)仅可以出现在compare条件的一侧;
- 若该compare条件的一侧出现(+),则该compare条件的每一侧最多允许存在一张表的字段;
- compare条件的一侧只要出现一个(+),外连接操作符就生效。如果出现多个(+),与出现一个(+)的效果相同;
- 当多个条件同时带(+)时,可能会生成不了关联关系而报错处理(例如,t1.f1=t2.f1(+) and t1.f1(+)=t2.f1);
GaussDB 100的连接操作如果没有显式指定,则默认为INNER JOIN。
使用方法
当查询的FROM子句中出现多个表时,数据库就会执行连接。查询的SELECT项则可以是这些表中任意一些列。例如:
SELECT table1.column, table2.column FROM table1, table2;
如果这些表中的任何两个具有共同的列名,则必须使用表名来限定整个查询中对这些列的所有引用避免歧义。
大多数连接查询包含至少一个连接条件,连接条件可以在FROM子句中也可以在WHERE子句中。例如:
SELECT table1.column, table2.column FROM table1 JOIN table2 ON(table1.column1 = table2.column2);
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
内连接
内连接的关键字为inner join,其中inner可以省略。使用内连接,连接执行顺序必然遵循语句中所写的表的顺序。
示例:使用内连接联合查询表。
--删除表training。 DROP TABLE IF EXISTS training;
--创建表training。 CREATE TABLE training(staff_id INT NOT NULL,course_name CHAR(50),course_start_date DATETIME, course_end_date DATETIME,exam_date DATETIME,score INT);
--向表training中插入记录1。 INSERT INTO training(staff_id,course_name,course_start_date,course_end_date,exam_date,score) VALUES(10,'SQL majorization','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90); --向表training中插入记录2。 INSERT INTO training(staff_id,course_name,course_start_date,course_end_date,exam_date) VALUES(11,'BIG DATA','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00'); --向表training中插入记录3。 INSERT INTO training(staff_id,course_name,course_start_date,course_end_date,exam_date,score) VALUES(12,'Performance Turning','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
--删除表education。 DROP TABLE IF EXISTS education;
--创建表education。 CREATE TABLE education(staff_id INT, higest_degree CHAR(8), graduate_school VARCHAR(64), graduate_date DATETIME, education_note VARCHAR(70));
--向表education中插入记录1。 INSERT INTO education(staff_id,higest_degree,graduate_school,graduate_date,education_note) VALUES(10,'doctor','Xidian University','2017-07-06 12:00:00','211'); --向表education中插入记录2。 INSERT INTO education(staff_id,higest_degree,graduate_school,graduate_date,education_note) VALUES(11,'master','Northwestern Polytechnical University','2017-07-06 12:00:00','211&985'); --向表education中插入记录3。 INSERT INTO education(staff_id,higest_degree,graduate_school,graduate_date,education_note) VALUES(12,'','Peking University','2017-07-06 12:00:00','211&985'); --向表education中插入记录4。 INSERT INTO education(staff_id,higest_degree,graduate_school,graduate_date,education_note) VALUES(13,'scholar','Peking University','2017-07-06 12:00:00','211&985');
--查询员工ID、最高学历和考试分数。使用training和education两个相关的列(staff_id)做查询操作。 SELECT e.staff_id, e.higest_degree, t.score FROM education e JOIN training t ON (e.staff_id = t.staff_id); STAFF_ID HIGEST_DEGREE SCORE ------------ ------------- ------------ 10 doctor 90 11 master 12 95 3 rows fetched.
外连接
内连接所指定的两个数据源处于平等的地位。而外连接不同,外连接以一个数据源为基础,将另外一个数据源与之进行条件匹配。内连接返回两个表中所有满足连接条件的数据记录。当需要返回那些不满足连接条件的记录,则使用外连接。外连接不仅返回满足连接条件的记录,还将返回不满足连接条件的记录。外连接又分为左外连接、右外连接和全外连接,又简称左连接、右连接和全连接。
- 左外连接:又称左连接,如图1所示,是指以左边的表为基础表进行查询,根据指定连接条件关联右,获取基础表以及和条件匹配的右表数据。
例如,把内连接中的查询连接改成左外连接。
SELECT e.staff_id, e.higest_degree, t.score FROM education e LEFT JOIN training t ON (e.staff_id = t.staff_id);
STAFF_ID HIGEST_DEGREE SCORE ------------ ------------- ------------ 10 doctor 90 11 master 12 95 13 scholar 4 rows fetched.
- 右外连接:又称右连接,如图2所示,是指以右边的表为基础表,在内连接的基础上也查询右边表中有记录,而左边的表中没有记录的数据。
例如,把内连接中的查询改为右外连接。
SELECT e.staff_id, e.higest_degree, t.score FROM education e RIGHT JOIN training t ON (e.staff_id = t.staff_id);
STAFF_ID HIGEST_DEGREE SCORE ------------ ------------- ------------ 10 doctor 90 11 master 12 95 3 rows fetched.
- 全外连接:又称全连接,如图3所示,是指除了返回两个表中满足连接条件的记录,还会返回不满足连接条件的所有其它行。即是左外连接和右外连接查询结果的总和。如果两个表使用了完全连接,那么将先进行一次左连接,再进行一次右连接,最后将两个临时结果集进行union操作。
例如,通过全连接获取表education和表training的完全连接。
SELECT e.staff_id, e.higest_degree, t.score FROM education e FULL JOIN training t ON (e.staff_id = t.staff_id);
STAFF_ID HIGEST_DEGREE SCORE ------------ ------------- ------------ 10 doctor 90 11 master 12 95 13 scholar 4 rows fetched.
图1 左外连接图2 右外连接图3 全外连接半连接
半连接(Semi Join)是一种特殊的连接类型,在SQL中没有指定的关键字,通过在WHERE后面使用IN或EXISTS子查询实现。当IN/EXISTS右侧的多行满足子查询的条件时,主查询也只返回一行与IN/EXISTS子查询匹配的行,而不是复制左侧的行。
如下面示例中,要查看参加培训的员工的教育信息。即使training表中的许多行可能与子查询匹配(即同一个staff_id下有多个职员),也只需要从表training返回一行。表education和表training的定义请参见内连接。
SELECT staff_id, higest_degree, education_note FROM education WHERE EXISTS (SELECT * FROM training WHERE education.staff_id = training.staff_id);
STAFF_ID HIGEST_DEGREE EDUCATION_NOTE ------------ ------------- ---------------------------------------------------------------- 10 doctor 211 11 master 211&985 12 211&985 3 rows fetched.
反连接
反连接(Anti Join)是一种特殊的连接类型,在SQL中没有指定的关键字,通过在WHERE后面使用 NOT IN或NOT EXISTS子查询实现。返回所有不满足条件的行。这个关系的概念跟半连接相反。
例如,下面的示例查询没有参加培训的员工的教育信息。表education和表training的定义请参见内连接。
SELECT staff_id, higest_degree, education_note FROM education WHERE staff_id NOT IN (SELECT staff_id FROM training); STAFF_ID HIGEST_DEGREE EDUCATION_NOTE ------------ ------------- ---------------------------------------------------------------- 13 scholar 211&985 1 rows fetched.
说明:如果子查询位于WHERE子句的OR分支上,则无法进行半连接和反连接转换。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」关注作者【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。评论