【MySQL】Join相关查询

前言

　　有时为了得到完整的结果，我们需要从两个或者更多的表中获取数据，这时join就派上用场了。它可以基于这些表之间的共同字段把表结合起来。它有几种形式，分别为：join、inner join、left join、right join等等。其中join和inner join相同，这里以join的用法来代表两者。

数据源

 CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',
  `sno` int(11) DEFAULT NULL COMMENT '学号',
  `school_id` int(10) DEFAULT NULL COMMENT '学校id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `school` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `school_name` varchar(10) DEFAULT NULL COMMENT '学校名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


INSERT INTO `student` (`id`, `sname`, `sno`, `school_id`) VALUES ('1', '琚建飞', '130', '1');
INSERT INTO `student` (`id`, `sname`, `sno`, `school_id`) VALUES ('2', '张三', '131', '2');
INSERT INTO `student` (`id`, `sname`, `sno`, `school_id`) VALUES ('3', '李四', '132', '3');


INSERT INTO `school` (`id`, `school_name`) VALUES ('1', '廊坊师范');
INSERT INTO `school` (`id`, `school_name`) VALUES ('2', '清华大学');
INSERT INTO `school` (`id`, `school_name`) VALUES ('3', '北京大学');

Join

　　Join会返回匹配的所有行。

mysql> SELECT s.sname,sc.school_name FROM student s JOIN school sc ON s.school_id = sc.id;
+--------+-------------+
| sname  | school_name |
+--------+-------------+
| 琚建飞 | 廊坊师范    |
| 张三   | 清华大学    |
| 李四   | 北京大学    |
+--------+-------------+
3 rows in set

Left Join

　　Left Join会返回左表中的所有行，即使在右表中无匹配行。
　　现在，把student表中，张三的school_id改为4，李四的school_id改为5，示例代码：

//Join表的效果
mysql> SELECT s.sname,sc.school_name FROM student s JOIN school sc ON s.school_id = sc.id;
+--------+-------------+
| sname  | school_name |
+--------+-------------+
| 琚建飞 | 廊坊师范    |
+--------+-------------+
1 row in set

//Left Join的效果
mysql> SELECT s.sname,sc.school_name FROM student s Left JOIN school sc ON s.school_id = sc.id;
+--------+-------------+
| sname  | school_name |
+--------+-------------+
| 琚建飞 | 廊坊师范    |
| 张三   | NULL        |
| 李四   | NULL        |
+--------+-------------+
3 rows in set

Right Join

　　Right Join关键字会返回在右表所有的行，即使在左表中没有匹配的行。

mysql> SELECT s.sname,sc.school_name FROM student s Right JOIN school sc ON s.school_id = sc.id;
+--------+-------------+
| sname  | school_name |
+--------+-------------+
| 琚建飞 | 廊坊师范    |
| NULL   | 清华大学    |
| NULL   | 北京大学    |
+--------+-------------+
3 rows in set

UNION

　　列出所有连接表的全部数据。MySQL不支持FULL OUTER JOIN关键字。替代格式为：

mysql> SELECT * FROM student s LEFT JOIN school sc ON s.school_id = sc.id
UNION
SELECT * FROM student s RIGHT JOIN school sc ON s.school_id = sc.id;
+------+--------+------+-----------+------+-------------+
| id   | sname  | sno  | school_id | id   | school_name |
+------+--------+------+-----------+------+-------------+
|    1 | 琚建飞 |  130 |         1 |    1 | 廊坊师范    |
|    2 | 张三   |  131 |         4 | NULL | NULL        |
|    3 | 李四   |  132 |         5 | NULL | NULL        |
| NULL | NULL   | NULL | NULL      |    2 | 清华大学    |
| NULL | NULL   | NULL | NULL      |    3 | 北京大学    |
+------+--------+------+-----------+------+-------------+
5 rows in set

　　UNION 操作符用于合并两个或多个 SELECT 语句的结果集。语法格式为：

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

　　默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

//在student表添加一行数据
INSERT INTO student(sname,sno,school_id) VALUES('王五',130,1);

//使用UNION
mysql> SELECT school_id FROM student
UNION 
SELECT id FROM school;
+-----------+
| school_id |
+-----------+
|         4 |
|         5 |
|         1 |
|         2 |
|         3 |
+-----------+
5 rows in set

//使用UNION ALL
mysql> SELECT school_id FROM student
UNION ALL
SELECT id FROM school;
+-----------+
| school_id |
+-----------+
|         4 |
|         5 |
|         1 |
|         1 |
|         2 |
|         3 |
+-----------+
6 rows in set

参考资料：http://www.w3school.com.cn/sql/sql_join.asp