联结
举例:
我们有多个工厂A,B,每个工厂会生产的商品种类为T1,T2,T3,,商品所对应的价格为P1,P2,P3,那么我们如何设计数据表结构去存储每隔商品的信息?
没有考虑联结时,设计的表结构如下:
+------------+-----------+----------+ ----------+
| factory | commodity| price | factoryid |
+-----------------------------------------------+
| A | T1 | P1 | 1001 |
+-----------------------------------------------+
| A | T2 | P2 | 1001 |
+-----------------------------------------------+
| A | T3 | P3 | 1001 |
+-----------------------------------------------+
| B | T1 | P1 | 1002 |
+-----------------------------------------------+
| B | T2 | P2 | 1002 |
+-----------------------------------------------+
| B | T3 | P3 | 1002 |
+------------+-----------+----------+-----------+
考虑联结以后,设计两个表,一个工厂信息表,一个商品信息表,表结构如下:
工厂信息表
+------------+-----------+
| factory | factoryid |
| A | 1001 |
| B | 1002 |
+------------+-----------+
商品数据表
+------------+-----------+-----------+
| commodity | price | factoryid |
+------------------------------------+
| T1 | P1 | 1001 |
+------------------------------------+
| T2 | P2 | 1001 |
+------------------------------------+
| T3 | P3 | 1001 |
+------------------------------------+
| T1 | P1 | 1002 |
+------------------------------------+
| T2 | P2 | 1002 |
+------------------------------------+
| T3 | P3 | 1002 |
+------------+-----------+-----------+
使用联结的优点:
1.工厂信息不会重复,从而节约时间和空间
2.如果工厂信息发生变动,对于第一种表结构,需要修改全部商品所对应的工厂信息,对于第二种表结构,只需要修改工厂信息表中所对应的工厂信息即可。
3.由于数据没有重复,即数据是一致性的,那么处理数据更简单
一、内部联结
表达式:
SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1;
举例说明:
TableA TableB
+------------+----------+ +--------------------+
|Column1 | Column2 | |Column1 | Column3 |
+-----------------------+ +--------------------+
| 1 | A | | 1 | X |
| 2 | B | | 2 | Y |
+------------+----------+ +---------+----------+
使用内部联结之后:
SELECT * FROM TableA INNER JOIN TableB USING (Column1);
SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1;
+------------+-----------+---------------------+
| Column1 | Column2 | Column1| Column3|
+------------------------+---------------------+
| 1 | A | 1 | X |
| 2 | B | 2 | Y |
+------------+-----------+----------+----------+
使用select where语句得到:
SELECT * FROM TableA , TableB WHERE TableA.Column1 = TableB.Column1;
+------------+-----------+----------+
| Column1 | Column2 | Column3|
+------------------------+----------+
| 1 | A | X |
| 2 | B | Y |
+------------+-----------+----------+
二、自然联结
自然联结其实和内部联结一样,只是不会出现相同的重复列。比如在上边的INNER JOIN中,出现了两个id列,他们一模一样。
在以前的版本中,有natural join 来实现自然联结,也就是去除重复的列。在最新版本的MySQL中,不在支持natural join。把这项工作交给了用户。
基本上所有的内部联结都是自然联结,我们几乎不会用到不是内部联结的自然联结。
三、外部联结
定义:联结中包含了那些在相关表中没有关联行的行,这就是外部联结。
举例:
继续用上面的TableA、TableB来举例,我们在TableA中再插入一行,这一行在TableB中没有对应的column1。
TableA TableB
+------------+----------+ +--------------------+
|Column1 | Column2 | |Column1 | Column3 |
+-----------------------+ +--------------------+
| 1 | A | | 1 | X |
| 2 | B | | 2 | Y |
| 3 | C | +---------+----------+
+------------+----------+
如果我们使用内部联结,其不会显示column1=3 这个相关的信息。
SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1;
+------------+-----------+---------------------+
| Column1 | Column2 | Column1| Column3|
+------------------------+---------------------+
| 1 | A | 1 | X |
| 2 | B | 2 | Y |
+------------+-----------+----------+----------+
所以,如果我们也想显示具体TableA中所有元素的信息,不管他在不在TableB中,我们可以使用外部联结。
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.Column1 = TableB.Column1;
+------------+-----------+---------------------+
| Column1 | Column2 | Column1| Column3|
+------------------------+---------------------+
| 1 | A | 1 | X |
| 2 | B | 2 | Y |
| 3 | C | NULL | NULL |
+------------+-----------+----------+----------+
上面的语句使用的是LEFT OUTER JOIN,其是使用JOIN左边的表为基准,会选择左边表所有的行,如果该行所对应的数据在TableB中不存在,则将其显示为null
同理,RIGHT OUTER JOIN 是以JOIN右边的表作为基准。