[mysql基础文档]-30-左右内联查询
引言
左右连接查询和内联查询的工作方式很类似，所不同的是，左右连接查询会将左值或右值作为基准值进行对比。
文章目录
0×1.如何使用left join和right join查询
使用上一篇文章inner join查询实例中所创建的表A和B来演示左右联合查询，建表语法请参考上一篇文章，这里仅给出表结构：
mysql> select * from A;
+-----+-------+
| aid | aname |
+-----+-------+
| 1 | susam |
| 2 | jenny |
| 3 | mary |
+-----+-------+
mysql> select * from B;
+-----+--------+
| bid | bname |
+-----+--------+
| 2 | tom |
| 3 | john |
| 4 | george |
+-----+--------+
--左联接查询，在这个实例中A表是"A left join B"这个表达式的左值，作为参照表使用A表aid列连接B表bid列，在这个过程中，将列出所有A表下面每个aid字段所对应的记录，当B表对应的bid中没有字段匹配的时候，用NULL填充，有人可能会问，为什么没有4，因为用左值作为参考，aid中没有4
mysql> select * from A left join B on A.aid=B.bid;
+-----+-------+------+-------+
| aid | aname | bid | bname |
+-----+-------+------+-------+
| 1 | susam | NULL | NULL |
| 2 | jenny | 2 | tom |
| 3 | mary | 3 | john |
+-----+-------+------+-------+
--右连接查询同理，顾名思义，B是"A right join B"这个表达式的右值，所以用B作为参考表，A中不存在的对应记录用NULL填充
mysql> select * from A right join B on A.aid=B.bid;
+------+-------+-----+--------+
| aid | aname | bid | bname |
+------+-------+-----+--------+
| 2 | jenny | 2 | tom |
| 3 | mary | 3 | john |
| NULL | NULL | 4 | george |
+------+-------+-----+--------+
0×2.联合查询综合经典实例
实例1，创建一个联合查询，将"mysql基础文档-24-select查询基础"第一部分所创建的cellphone表，请参考：[cellphone数据表]，以及"mysql基础文档-28-子查询"第二部分中创建的category表，请参考：[category数据表]，cellphone表中的goods_name，category表中的cat_id，cat_name，这三列查询并显示成一个结果集
--使用内联查询，将category的cat_id列同cellphone的cat_id列连接起来，
mysql> select category.cat_id,category.cat_name,cellphone.goods_name
-> from
-> cellphone inner join category
-> on
-> category.cat_id=cellphone.cat_id;
+--------+----------+--------------+
| cat_id | cat_name | goods_name |
+--------+----------+--------------+
| 1 | XiaoMi | hongMI2a |
| 1 | XiaoMi | MInote |
| 1 | XiaoMi | hongMI2 |
| 1 | XiaoMi | MI4 |
| 2 | Samsung | GalaxyN9200 |
| 3 | Apple | iPhone6A1586 |
| 4 | Meizu | MeizuNote2 |
| 5 | Huawei | Huawei4X |
| 2 | Samsung | GalaxyG9250 |
| 5 | Huawei | Huawei4A |
| 4 | Meizu | MX5 |
| 3 | Apple | iPhone5s |
| 2 | Samsung | GalaxyN9109W |
| 4 | Meizu | MX4 |
| 3 | Apple | iPhone4s |
| 5 | Huawei | Huawei6 |
| 3 | Apple | iPhone6s |
| 4 | Meizu | MX4Pro |
| 2 | Samsung | GalaxyS6 |
| 5 | Huawei | Huawei7 |
+--------+----------+--------------+
--有兴趣的朋友可以试着显示这张完整的内联表
mysql> select * from cellphone inner join category on category.cat_id=cellphone.cat_id;
--如果仅需要筛选出cat_id为3的完整记录，可以如下，这里要注意，当内联后的表中包含同名列时，使用时需要指定表前缀（category.cat_id）
mysql> select *
-> from
-> cellphone inner join category
-> on
-> category.cat_id=cellphone.cat_id
-> where
-> category.cat_id=3;
实例2，一道真实的内联查询面试题
现有两张表如下：
mysql> create table team(tid tinyint,tname char(3));
mysql> insert into team values
-> (1,'T1'),
-> (2,'T2'),
-> (3,'T3');
mysql> create table mat(cid tinyint unsigned,h tinyint,g tinyint,scoring char(10),sdate date);
mysql> insert into mat values
-> (1,1,2,'2:0','2015-05-12'),
-> (2,2,3,'1:2','2015-06-01'),
-> (3,3,1,'2:5','2015-06-15'),
-> (4,2,1,'3:2','2015-07-15');
--球队id和球队名称表team
mysql> select * from team;
+------+-------+
| tid | tname |
+------+-------+
| 1 | T1 |
| 2 | T2 |
| 3 | T3 |
+------+-------+
--球队比赛日期表，cid总比赛场数列，h表示主场，g表示客场，主客场列的值分别对应了team表的tid值，scoring是单场比赛得分，sdate是比赛日期
mysql> select * from mat;
+------+------+------+---------+------------+
| cid | h | g | scoring | sdate |
+------+------+------+---------+------------+
| 1 | 1 | 2 | 2:0 | 2015-05-12 |
| 2 | 2 | 3 | 1:2 | 2015-06-01 |
| 3 | 3 | 1 | 2:5 | 2015-06-15 |
| 4 | 2 | 1 | 3:2 | 2015-07-15 |
+------+------+------+---------+------------+
要求，查询出"2015-06-01"至"2015-07-01"之间所有比赛记录，返回结果集格式要求"T1 2:0 T2 2015-06-01"。
--还记得以前的分步思想吗？先用mat表的h列对应team表的tid列进行一次内联查询，使用as将第一个连接的team表命名为t1，t1中的tname顺序对应了主场的h值
mysql> select *
-> from
-> mat inner join team as t1
-> on
-> mat.h=t1.tid;
+------+------+------+---------+------------+------+-------+
| cid | h | g | scoring | sdate | tid | tname |
+------+------+------+---------+------------+------+-------+
| 1 | 1 | 2 | 2:0 | 2015-05-12 | 1 | T1 |
| 2 | 2 | 3 | 1:2 | 2015-06-01 | 2 | T2 |
| 3 | 3 | 1 | 2:5 | 2015-06-15 | 3 | T3 |
| 4 | 2 | 1 | 3:2 | 2015-07-15 | 2 | T2 |
+------+------+------+---------+------------+------+-------+
--对上面的步骤再加工，将上面这一步的结果集作为一张完整的表，再次和team表进行内联查询，使用team的tid对应mat表的g列，从而可以得到客场作战的tname顺序，将第二个连接的team表命名为t2，虽然结果中有四列相同的tid和tname，但是大家要明白前面两列是t1表的，后面两列是t2表的，select筛选的时候会用到
mysql> select *
-> from
-> mat inner join team as t1
-> on
-> mat.h=t1.tid
-> inner join team as t2
-> on
-> mat.g=t2.tid;
+-----+---+---+---------+------------+------+-------+------+-------+
| cid | h | g | scoring | sdate | tid | tname | tid | tname |
+-----+---+---+---------+------------+------+-------+------+-------+
| 1 | 1 | 2 | 2:0 | 2015-05-12 | 1 | T1 | 2 | T2 |
| 2 | 2 | 3 | 1:2 | 2015-06-01 | 2 | T2 | 3 | T3 |
| 3 | 3 | 1 | 2:5 | 2015-06-15 | 3 | T3 | 1 | T1 |
| 4 | 2 | 1 | 3:2 | 2015-07-15 | 2 | T2 | 1 | T1 |
+-----+-------+---------+------------+------+-------+------+-------+
--总表出来后，最后一次加工，按照题目要求的格式，写出select后面的部分代替星号，筛选出对应列和时间范围即可，cid列可写可不写
mysql> select cid,t1.tname as hname,scoring,t2.tname as gname,sdate
-> from
-> mat inner join team as t1
-> on
-> mat.h=t1.tid
-> inner join team as t2
-> on
-> mat.g=t2.tid
-> where between '2015-06-01' and '2015-07-01';
+------+-------+---------+-------+------------+
| cid | tname | scoring | gname | sdate |
+------+-------+---------+-------+------------+
| 2 | T2 | 1:2 | T3 | 2015-06-01 |
| 3 | T3 | 2:5 | T1 | 2015-06-15 |
+------+-------+---------+-------+------------+