该系列文章系个人读书笔记及总结性内容,任何组织和个人不得转载进行商业活动!
外联接、自联接与联合:新策略
关于联接我们只认识了一半:
我们已经知道 返回每个可能行的交叉联接,返回两张表中相符记录的内联接;
但还没见过外联接,他可以在表中没有匹配记录的情况下返回记录;
还有自联接,他可以联接表本身;
还有联合,可以合并查询结果;
学习了这些之后,就可以用自己的方式来取得所有数据;本章还会探讨下子查询;
测试数据库我们使用上一章的mark_list,不熟悉的可以移步看下;
找出my_contacts表中任何记录连接的职业?
任何记录是指每一条记录,不管这条记录是否有对应的职业;
这种信息可以通过外联接(outer join)取得;
我们知道,使用内连接的时候,虽然要比对来自两张表的行,但表的顺序并无影响;
我们新插入一条联系人信息,用以测试:(该表之前删除过一条数据)
我们使用内联接,找到的my_contacts表中有职业的记录,SQL如下:
这实际上是一个相等联接,比较的是联系人的contact_id;
mysql> SELECT mc.last_name,mc.first_name,jc.title
-> FROM my_contacts mc INNER JOIN job_current jc
-> ON mc.contact_id = jc.contact_id;
+-----------+------------+-----------+
| last_name | first_name | title |
+-----------+------------+-----------+
| Joy | HQ | EngineerM |
| Mary | DM | EngineerS |
+-----------+------------+-----------+
外联接:
外联接返回某张表的所有行,并且有来自另一张表的条件相符的数据;
一切都跟左右有关:
外联接更注重两张表之间的关系;
左外联接(LEFT OUTER JOIN):
接收左边的所有行,并用这些行与右边匹配;
当左表与右表具有一对多关系时,左外联接特别有用;
在LEFT OUTER JOIN中,出现在FROM后、联接前的表称为左表,出现在联接后的表为右表;
尝试使用左外联接回答一开始的问题:
mysql> SELECT mc.last_name,mc.first_name,jc.title
-> FROM my_contacts mc LEFT OUTER JOIN job_current jc
-> ON mc.contact_id = jc.contact_id;
+-----------+------------+-----------+
| last_name | first_name | title |
+-----------+------------+-----------+
| Joy | HQ | EngineerM |
| Mary | DM | EngineerS |
| July | FM | NULL |
+-----------+------------+-----------+
外联接和内联接的差别:
外联接一定会提供数据行,无论该行能否在另一个表中找到相匹配的行;
出现NULL表示没有匹配的行,上述查询的NULL表示July没有工作;
左外联接的结果集中的NULL表示右表中没有找到与左表相符的记录;
左表的所有行都会存在,符合条件的数据结果表中会有相应数据,不符合条件的则填入NULL;
列的显示顺序可以用SELECT语句控制,更重要的是联接表的顺序;
外联接与多个相符结果:
我们使用测试表boys和toys;
执行两个方向的左外联接:
下面两张图展示了左外联接的实际行动;
以第二张图为例,说明如下:
wan ju 1->条件不符->NULL;
wan ju 2->条件相符->boy1;
wan ju 2->条件相符->boy2;
wan ju 2->条件相符->boy3;
wan ju 3->条件相符->boy5;
wan ju 4->条件相符->boy4;
wan ju 5->条件不符->NULL;
右外联接(RIGHT OUTER JOIN):
与左外联接基本一致,区别在于它是使用右表与左表比对;
在RIGHT OUTER JOIN中,出现在FROM后、联接前的表称为右表,出现在联接后的表为左表;
因此同样的是左表联接右表,对boys和toys使用左、右外联接的SQL如下:
mysql> SELECT b.boy,t.toy
-> FROM boys b LEFT OUTER JOIN toys t
-> ON b.toy_id = t.toy_id;
mysql> SELECT b.boy,t.toy
-> FROM toys t RIGHT OUTER JOIN boys b
-> ON b.toy_id = t.toy_id;
查询的结果集,二者是一致的:
+-------+----------+
| boy | toy |
+-------+----------+
| boy 1 | wan ju 2 |
| boy 2 | wan ju 2 |
| boy 3 | wan ju 2 |
| boy 4 | wan ju 4 |
| boy 5 | wan ju 3 |
+-------+----------+
全连接(FULL OUTER JOIN):
少数的几个RDBMS能够做到,MySQL不行;
同一个表可以同时作为外联接的左右表
我们新建一张测试表clown_boss,记录员工及其boss:
相关SQL如下:
1)新建表:
mysql> CREATE TABLE clown_boss
-> (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(30),
-> boss_id INT NOT NULL,
-> PRIMARY KEY(id)
-> );
2)添加数据:
mysql> INSERT INTO clown_boss
-> (name,boss_id)
-> VALUES
-> ('B1',5),
-> ('B2',1),
-> ('B3',1),
-> ('B4',3),
-> ('B5',5);
3)更新表结构,添加外键:
mysql> ALTER TABLE clown_boss
-> ADD CONSTRAINT clown_boss_clown_boss_id_fk FOREIGN KEY(boss_id) REFERENCES clown_boss(id);
4)查看表结构:
mysql> DESC clown_boss;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| boss_id | int(11) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
5)再插入两条数据:
mysql> INSERT INTO clown_boss
-> (name,boss_id)
-> VALUES
-> ('B6',2),
-> ('B7',2);
测试表如下:
mysql> SELECT * FROM clown_boss;
+----+------+---------+
| id | name | boss_id |
+----+------+---------+
| 1 | B1 | 5 |
| 2 | B2 | 1 |
| 3 | B3 | 1 |
| 4 | B4 | 3 |
| 5 | B5 | 5 |
| 6 | B6 | 2 |
| 7 | B7 | 2 |
+----+------+---------+
自引用外键:
我们注意到上张表的主键和外键都来自clown_boss表;
boss_id是引用了同一张表主键id的外键——我们叫自引用外键(self-referencing foreign key);
自引用外键是出于其他目的而用于同一张表的主键;
联接表与他自己:
获取小丑的信息及其头领信息;
可以想成是两张相同的表,各自设置别名;
mysql> SELECT cb1.name clown ,cb2.name boss
-> FROM clown_boss cb1
-> INNER JOIN clown_boss cb2
-> ON cb1.boss_id = cb2.id;
+-------+------+
| clown | boss |
+-------+------+
| B1 | B5 |
| B2 | B1 |
| B3 | B1 |
| B4 | B3 |
| B5 | B5 |
| B6 | B2 |
| B7 | B2 |
+-------+------+
我们需要自联接:
使用表clown_boss两次,上边的SQL对应的就是自联接;
自联接能把单一表当成两张具有完全相同的信息的表来进行查询;
联合-取得多张表内容:
联合UNION,也以用来取得多张表的查询结果;
UNION可以根据在SELECT中指定的列,把两张或更多张表的查询结果合并至一个表中;
比如,我们要获取数据库mark_list中,三张工作表中的所有工作;
1)分开的SELECT查询:
2)使用联合:
mysql> SELECT title FROM job_current
-> UNION
-> SELECT title FROM job_desired
-> UNION
-> SELECT title FROM job_list;
+-----------+
| title |
+-----------+
| EngineerM |
| EngineerS |
| EngineerH |
| EngineerW |
| EngineerY |
| EngineerA |
+-----------+
如果需要排序,只需要在最后加上ORDER BY;
mysql> SELECT title FROM job_current
-> UNION
-> SELECT title FROM job_desired
-> UNION
-> SELECT title FROM job_list
-> ORDER BY title;
+-----------+
| title |
+-----------+
| EngineerA |
| EngineerH |
| EngineerM |
| EngineerS |
| EngineerW |
| EngineerY |
+-----------+
同时我们也注意到,查询结果并没有重复值,这是因为SQL默认会清除联合中的重复值;
如果需要看重复数据,可以使用UNION ALL运算符:
mysql> SELECT title FROM job_current
-> UNION ALL
-> SELECT title FROM job_desired
-> UNION ALL
-> SELECT title FROM job_list
-> ORDER BY title;
+-----------+
| title |
+-----------+
| EngineerA |
| EngineerH |
| EngineerM |
| EngineerM |
| EngineerM |
| EngineerM |
| EngineerS |
| EngineerS |
| EngineerS |
| EngineerS |
| EngineerW |
| EngineerY |
+-----------+
UNION的使用规则:
1)UNION只接受一个ORDER BY且位于语句末端,这是因为UNION已经把多个SELECT语句的查询结果串起来并进行了分组;(默认进行了GROUP BY);
SQL默认会清除联合结果中的重复值;
2)每个SELECT语句中的列数须一致;
3)每个SELECT语句包含的表达式与统计函数也必须相同;
4)SELECT语句的顺序并不重要,不会改变结果;
5)列的数据类型必须相同或者可以相互转换;(INT可以转成VARCHAR,但是反过来不行)
6)看全部的记录,可以使用UNION ALL运算符,它会返回每个相符的记录,可以重复;
从联合创建表:
使用CREATE TABLE AS可以接收来自SELECT查询的结果并把结果制作成一张表;
任何SELECT查询的结果都可以用于创建表;
mysql> CREATE TABLE job_titles AS
-> SELECT title FROM job_current
-> UNION
-> SELECT title FROM job_desired
-> UNION
-> SELECT title FROM job_list
-> ORDER BY title;
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM job_titles;
+-----------+
| title |
+-----------+
| EngineerA |
| EngineerH |
| EngineerM |
| EngineerS |
| EngineerW |
| EngineerY |
+-----------+
6 rows in set (0.00 sec)
INTERSECT与EXCEPT:
对应数学集合运算的并集是UNION的话,那么对应的交集就是INTERSECT,差集是EXCEPT;
使用方式大致和UNION相同;
INTERSECT 会返回同时在第一个和第二个中的列;
EXCEPT 会返回只在第一个查询 但不在第二个查询中的列;
现在,我们解决了 联接 和 联合;
子查询与联接的比较:
几乎所有的子查询都可以用联接来实现;
结果集需要取自多张表的时候,联接是更好的选择;
子查询更擅长聚合值/统计值,使用统计函数等;
我们尝试把子查询转换为连接;
这是子查询一章中,我们使用过的一个示例:
mysql> SELECT mc.last_name,mc.phone,jc.title
-> FROM my_contacts mc NATURAL JOIN job_current jc
-> WHERE jc.title IN (
-> SELECT title
-> FROM job_list
-> );
+-----------+-------------+-----------+
| last_name | phone | title |
+-----------+-------------+-----------+
| Joy | 15612121212 | EngineerM |
| Mary | 13212121212 | EngineerS |
+-----------+-------------+-----------+
尝试使用联接改写:
由于内联接比对的列值不唯一,查出的记录也会有多个;
虽然是改写,但这个更适合使用子查询;
mysql> SELECT mc.last_name,mc.phone, jc.title
-> FROM (my_contacts mc NATURAL JOIN job_current jc )
-> INNER JOIN job_list jl
-> ON jc.title = jl.title;
+-----------+-------------+-----------+
| last_name | phone | title |
+-----------+-------------+-----------+
| Joy | 15612121212 | EngineerM |
| Joy | 15612121212 | EngineerM |
| Mary | 13212121212 | EngineerS |
| Mary | 13212121212 | EngineerS |
+-----------+-------------+-----------+
实践:列出薪资高于平均薪资者的姓名;
mysql> SELECT mc.last_name
-> FROM my_contacts mc NATURAL JOIN job_current jc
-> WHERE jc.salary > (
-> SELECT AVG(salary) FROM job_current
-> );
把自联接变成子查询:
我们看一下之前自联接的SQL;
mysql> SELECT cb1.name clown ,cb2.name boss
-> FROM clown_boss cb1
-> INNER JOIN clown_boss cb2
-> ON cb1.boss_id = cb2.id;
变成子查询的话,子查询需要依赖外层查询的结果,才能取到正确的boss_id,所以子查询是关联子查询:
mysql> SELECT cb1.name,(
-> SELECT name FROM clown_boss
-> WHERE cb1.boss_id = id
-> ) boss
-> FROM clown_boss cb1;
+------+------+
| name | boss |
+------+------+
| B1 | B5 |
| B2 | B1 |
| B3 | B1 |
| B4 | B3 |
| B5 | B5 |
| B6 | B2 |
| B7 | B2 |
+------+------+
总结:
我们学习了外联接、自联接与联合;
联接转换为子查询,反向转换也OK;
1.自引用外键:SELF-REFERENCING FOREIGN KEY
外键是同一张表的主键;
2.左外联接:LEFT OUTER JOIN
接受左表中的所有记录,并从右表比对出相符记录;
3.右外联接:RIGHT OUTER JOIN
接受右表中的所有记录,并从左表比对出相符记录;
4.自联接:SELF-JOIN
能用一张表做出联接两张完全相同表的效果;
5.联合:UNION与UNION ALL
根据SELECT指定的列合并两个或多个查询的结果为一张表;
UNION ALL则可以包含重复的值;
6.CREATE TABLE AS:
使用本命令从任何SELECT语句的结果创建表;
7.INTERSECT:使用这个关键字返回同时存在于第一个和第二个查询中的值;
EXCEPT:使用这个关键字返回在第一个查询中但不在第二个查询中的值;