mysql系列（十三）SQL语句之联结表

1.联结

sql最强大的功能之一就是可以在数据检索查询的执行中联结（join）表。联结是利用SELECT能执行的嘴重要的操作。

1.1 关系表

关系表的设计就是要保证把信息分成多个表，一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。

• 外键：外键为某个表中的一列，它包含另一个表的主键值，它定义了两个表之间的关系

关系表的好处：

• 信息不重复，从而不浪费时间和空间
• 如果其中一个表的数据变动，可以只更新该表中的相关记录，关联表的信息可以不变动
• 由于数据无重复，显然数据是一致的，这使得数据处理变得更简单

总之，关系数据可以有效得储存和方便地处理。因此关系数据库的可伸缩性远比非关系数据库要好。

可伸缩性(scale):能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

1.2 为什么使用联结

• 联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。
• 使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
• 联结不是物理实体，它在实际的数据库中不存在，联结由MYSQL根据需要建立，它存在于查询的执行当中。
• 维护引用完整性：通过在表的定义中指定主键和外键来实现的。

2.创建联结

2.1 使用联结

联结的创建需要规定要联结的表以及它们如何关联。

SELECT a.column1_name,b.column2_name FROM table1 a,table2 b WHERE a.column1 = b.column2;

• 完全限定名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔表名和列名），如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误

• 笛卡尔积(cartesian product):由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

• 应该保证所有的联结都有对应的WHERE子句，否则MySQL将返回比想要的数据多得多得数据。同理应该保证WHERE子句的正确性，不正确的过滤条件将导致MySQL返回不正确的数据。

2.2.内部联结

以上举的联结例子称为等值联结(equijoin),它基于两个表之间的相等测试，这种联结也称为**内部联结，对于这种联结还可以使用另一种语法表示：

SELECT a.column1_name,b.column2_name FROM table1 a INNER JOIN table2 b ON a.column1 = b.column2;

2.3 联结多个表

SELECT a.column1_name,b.column2_name,c.column3_name FROM table1 a,table2 b,table3 c WHERE a.column1_name = b.column2_name AND a.column2_name = c.column3_name; 

• 性能考虑
  MySQL在运行时关联指定的每个表以处理联结，这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表，联结的表越多，性能下降越厉害。

3. 创建高级联结

3.1 使用别名

别名不但可以用在列以及计算字段，还可以给表起别名

SELECT a.column1_name,b.column2_name FROM table1 AS a INNER JOIN table2 AS b ON a.column1 = b.column2;

其中AS可以省略

• 应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

3.2 自联结

SELECT a.column1_name,a.column2_name FROM table a, table b WHERE a.column1 = b.column1 AND b.column2_name = "xxx";

此查询中需要的两个表实际上是相同的表，因此表table在FROM子句中出现了两次。为了避免二义性，使用了表别名。

• 用自联结而不用子查询
  自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

3.3 自然联结

无论何时对表进行联结，应该至少有一个列出现不止在一个表中(被联结的列)。标准的联结返回的数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。一般通过对表使用通配符（SELECT *），对所有其他表使用明确的子集来完成。如下：

SELECT a.*,b.column2_name FROM table1 AS a JOIN table2 AS b ON a.column1 = b.column2;

3.4 外部联结

许多联结将一个表中的行与另一个表中的行想关联，有时候需要包含没有关联行的那些行。
如下所示：

SELECT a.*,b.column2_name FROM table1 AS a LEFT JOIN table2 AS b ON a.column1 = b.column2;

• 外部联结的类型：左外联结(LEFT JOIN)和右外联结(RIGHT JOIN)
• 外部联结：联结包含了在相关表中没有关联行的行。
• 左外部联结表示从左边表中选出所有行，右外部联结表示右边表中显示所有行

SELECT a.*,b.column2_name FROM table1 AS a LEFT OUTER JOIN table2 AS b ON a.column1 = b.column2;

• OUTER可以省略

3.5 使用带聚集函数的联结

SELECT a.*,b.column2_name,COUNT(a.column_name) AS num_column FROM table1 AS a LEFT OUTER JOIN table2 AS b ON a.column1 = b.column2;