mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | 0 | |
| NAME | varchar(16) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set

mysql> desc sc;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| SID | int(11) | YES | | NULL | |
| CID | int(11) | YES | MUL | NULL | |
| SCORE | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
4 rows in set

mysql> select * from student;
+-------+----------+-----+
| ID | NAME | AGE |
+-------+----------+-----+
| 10001 | Andy | 26 |
| 10002 | Bill | 27 |
| 10003 | Caroline | 34 |
| 10004 | David | 46 |
+-------+----------+-----+
4 rows in set

mysql> select * from sc;
+----+-------+-----+-------+
| ID | SID | CID | SCORE |
+----+-------+-----+-------+
| 1 | 10001 | 101 | 78 |
| 2 | 10001 | 102 | 67 |
| 3 | 10008 | 103 | 100 |
+----+-------+-----+-------+
3 rows in set
-----------------------------------------------------------------------------------------------------------------
内连接
mysql> select student.*, sc.* from student inner join sc on student.id = sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in set

内连接等价于我们平时的自然连接，也就是：
mysql> select student.*, sc.* from student,sc where student.id = sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in set

-----------------------------------------------------------------------------------------------------------------
左连接
考虑下面的需求，我想列出所有学生对应的成绩，一个学生可能多个成绩，也可能没有成绩。有多个成绩把多个成绩列出来，没有成绩的话，成绩这些字段的值使用NULL填充。怎么解决这个问题？
使用左连接，student表 left join sc表，如下：
mysql> select student.*, sc.* from student left join sc on student.id = sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in set

-----------------------------------------------------------------------------------------------------------------
右连接
考虑下面的需求，我想列出所有成绩对应的学生，一个成绩有对应的学生，也可能没有对应的学生，比如这个学生开除了。有学生就把学生列出来，没有学生的话，学生这些字段的值使用NULL填充。怎么解决这个问题？
使用右连接，student表 right join sc表，如下：
mysql> select student.*, sc.* from student right join sc on student.id = sc.sid;
+-------+------+------+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+------+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| NULL | NULL | NULL | 3 | 10008 | 103 | 100 |
+-------+------+------+----+-------+-----+-------+
3 rows in set

根据对称性，A left join B 等价于 B right join A
-----------------------------------------------------------------------------------------------------------------
外连接
左连接也叫左外连接（同理右连接），这里的外连接也叫全外连接。
考虑下面的需求，我想列出所有学生对应的所有成绩，这里存在学生可能没有成绩，成绩也可能没有对应的学生，没有的话，也要列出来，这些字段的值使用NULL填充。
目前，mysql不支持外连接，解决办法是使用union组合查询，把左连接和右连接的结果合并。如下：
mysql> select student.*, sc.* from student left join sc on student.id = sc.sid union select student.*, sc.* from student right join sc on student.id = sc.sid;
+-------+----------+------+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+------+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | 10008 | 103 | 100 |
+-------+----------+------+------+-------+------+-------+
6 rows in set

注意：这里的union自动去除了重复行，如果不想去除重复行，使用union all

-----------------------------------------------------------------------------------------------------------------
还有一点需要注意：就是on 之后的条件，如下：
mysql> select student.*, sc.* from student left join sc on student.id = sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in set

mysql> select student.*, sc.* from student left join sc on student.id = sc.sid and sc.cid=101;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
4 rows in set

mysql> select student.*, sc.* from student left join sc on student.id = sc.sid where sc.cid=101;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
+-------+------+-----+----+-------+-----+-------+
1 row in set

这里看出第二个查询和第三个查询的区别，换一种写法就很清楚了。
mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid and sc.cid=101);
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
4 rows in set

mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid) where sc.cid=101;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
+-------+------+-----+----+-------+-----+-------+
1 row in set

mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid where sc.cid=101);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where sc.cid=101)' at line 1

-----------------------------------------------------------------------------------------------------------------
select student.*, sc.* from student left join sc on (student.id = sc.sid and sc.cid=101); 相当于：
1、内部连接
mysql> select student.*, sc.* from student inner join sc on student.id = sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in set
2、选择出sc.cid=101，再进行左连接，没有成绩的使用NULL填充

-----------------------------------------------------------------------------------------------------------------
select student.*, sc.* from student left join sc on (student.id = sc.sid) where sc.cid=101; 相当于：
1、左连接
mysql> select student.*, sc.* from student left join sc on student.id = sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | AGE | ID | SID | CID | SCORE |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in set
2、再选出 sc.cid=101

最新文章

1. 面试题系列——OSI七层模型
2. 总结一下 input propertychange
3. sql 将字符串转换为表
4. 为HTML添加图片登录按钮
5. C# DataGridView控件 动态添加新行
6. 基于Flume的美团日志收集系统(一)架构和设计【转】
7. 内核参数优化之2-1 tcp/ip 标志位报文解析
8. c#、vb 自动属性
9. stm32之GPIO
10. linux上搭建zookeeper
11. BBS论坛（二十一）
12. 2189 ACM 母函数 素数
13. 这到底是什么bug？---已结贴
14. php 超时 解决办法 （Maximum execution time of 30 seconds exceeded）这个问题？
15. go语言 变量类型
16. <二叉树的基本操作>
17. ruby gem install rails 错误解决
18. unity3d中设计模式的学习<一>:泛型单例
19. T-SQL解析json字符串函数
20. Jmeter环境搭建详细介绍

热门文章

1. HDU 3652：B-number（数位DP）
2. boost库学习之regex
3. malloc与kmalloc
4. Winform自定义分页控件的实现
5. dot函数和*的区别
6. Linux下svn提交文件后自动同步更新到网站目录
7. Flume-NG + HDFS + HIVE日志收集分析
8. HTTP请求&响应、POST与GET
9. IOS之分析网易新闻存储数据(CoreData的使用，增删改查)
10. Update与FixedUpdate区别