SQL基础总结(六)：联结

本系列blog源自前年写的SQL学习笔记，汇总一下发上来。(1月份发了前三篇笔记，原以为后面的笔记误操作删了，今天在硬盘里又找到了，一起发上来)

--------------------------------

限定名称是一个表名后跟一个点号和表中的列名。限定名称可以在整个数据库中唯一的标识出某个列。如：table.column。

要提高性能，应该在联结查询中对所有列使用限定名称。

 

创建表的别名，在子查询的上下文中也称为相关名称：table [AS] alias

AS关键字是可选的。

 

SELECT子句可以在后面的语句定义别名之前先使用别名。

别名隐藏了表名，如果为表命名了别名，则必须在所有的限定引用中使用它的别名。

SELECT authors.au_id FROM authors au; //这样是不合法的。

也可以使用AS为视图分配别名。

 

使用联结

交叉联结：返回第一个表的每一行和第二个表的所有行组合得到的表的所有行。

自然联结：对第一个表所有列和第二个表具有名字相同的列进行等同比较的联结。

内联结：最普通的联结。使用比较操作符基于每一个表中共同列的值。

左外联结：返回左表中的所有行，不管与右表是否有匹配的联结列。如果左表中的行在右表中没有匹配的行，关联的结果对于右表所有SELECT子句列包含空值。

右外联结：返回右表中的所有行。

全外联结：返回左表和右表中的所有行。

自联结：表和它自身的联结。

如果关键字是复合的，通常应该联结所有关键字列。

如果联结列包含空值，空值无法联结。

不能联结二进制对象。

 

为了提高性能，应该对联结列建立索引。

 

可以将视图联结表或其它视图。

 

处理联结时，按照下面顺序执行整个查询：

1． 在JOIN子句中应用联结条件。

2． 在WHERE子句中应用联合条件和查询条件。

3． 按照GROUP BY分组。

4． 在HAVING子句中对分组进行过滤。

5． 按照ORDER BY排序。

当联结两个表时，对左表每一行和右表每一行进行组对，形成交叉联结，然后使用联结条件从交叉联结中筛选行。

 

被联结的列不必有相同的数据类型。如果数据类型不可以被隐式地转换，联结条件需要使用CAST()函数显示的转换数据类型。大多数使用联结的查询可以改写为嵌套在另一个查询中的查询（子查询），反之，大多数子查询可以改写为联结。

 

对于使用JOIN语法，如果被联结列有相同的名字且被用于比较是否相等，SQL标准定义了替代ON子句的USING子句。FROM table1 JOINTYPE table2 USING (columns).

 

使用CROSS JOIN 创建交叉联结

SELECT * FROM authors CROSS JOIN publishers;

等价于：

SELECT * FROM authors , publishers;

 

使用NATURAL JOIN创建自然联结

比较一个表中所有列和另一个表中具有名称相同的相应列的等同性。

使用自然联结，需要确保两个联结表中所有相关列有相同的列名，且非相关列有唯一的列名。

 

使用INNER JOIN创建内联结

使用比较操作符(=,,<>,>,<,>=,<=)匹配两个表的行。

只返回满足联结条件的联结行的结果。

SELECT columns FROM table1 INNER JOIN table2 ON join_conditions;

SELECT columns FROM table1 , table2 WHERE join_conditions;

 

默认情况下，JOIN等价于INNER JOIN。

使用>,<,>=,<=的联结很常见，<>很少用到，不等联结仅当用于自联结时才有意义。

 

使用OUTER JOIN创建外联结

内联结：两表中至少有一行满足联结条件才返回行，内联结删除在另一表中没有匹配的行。

外联结：外联结至少返回其中一个表的行。

 

左外联结：返回左表中的所有行，不管与右表是否有匹配的联结列。如果左表中的行在右表中没有匹配的行，关联的结果对于右表所有SELECT子句列显示空值。

SELECT columns FROM left_table LEFT [OUTER] JOIN right_table ON join_conditions;

 

右外联结：

SELECT columns FROM left_table RIGHT [OUTER] JOIN right_table ON join_conditions;

 

全外联结：

SELECT columns FROM left_table FULL [OUTER] JOIN right_table ON join_conditions;

 

关键字OUTER可选。

 

SQL有联合联结(union join)：返回删除匹配行的全外联结。在联合联结中每行是一个表的列与另一个表为空值的列。

联合联结很少使用，很多DBMS不支持。t1 UNION JOIN t2等价于t1 FULL OUTER JOIN t2 ON 1=2.1=2是永远为假的条件。

 

创建自联结

自联结是一个表和自身的联结，并通过比较同一个表的一列或多列值，从表中检索行的常规SQL联结。

自联结常被使用在有反身联系的表。

反身联系是指主键/外键同为一个表中的列或列的组合。

 

像任何联结一样，自联结需要两个表，但只是添加同一个表的另一个实例。DBMS组合并返回满足联结条件的行。将它们想象成两个表就很容易理解。

自联结经常被改写为子查询。