MySQL学习笔记：第四天（二）多表连接查询

多表连接

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。

• 在 WHERE子句中书写连接条件。
• 如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
• N个表相连时，至少需要N－1个连接条件。

连接类型

按连接条件分：

1. 等值连接
2. 非等值连接

按其他连接方法分

1. 外部连接
2. 内部连接

笛卡尔积

笛卡尔积，是第一个表中的所有行和第二个表中的所有行都发生连接。

笛卡尔积在下列情况产生

• 连接条件被省略
• 连接条件是无效的

限制歧义列名

• 在用到多个表时可以使用表名作前缀来限定列；
• 通过使用表前缀可以提高性能；
• 通过使用列的别名可以区分来自不同表但是名字相同的列；

自身连接

自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

SELECT worker.ename ‘WNAME’,manager.ename ‘LNAME’
  FROM 	emp worker, emp manager
 WHERE 	worker.mgr = manager.empno;

交叉连接

交叉连接会产生连个表的交叉乘积，和两个表之间的笛卡尔积是一样的；

使用CROSS JOIN 子句完成。

自然连接

自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；

如果两个表之间相同名称的列的数据类型不同，则会产生错误；

使用NATURAL JOIN子句来完成。

USING子句

自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。

使用USING子句创建连接时，应注意以下几点：

• 如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。
• 如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。
• USING子句中的用到的列不能使用表名和别名作为前缀。 NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用

ON子句

• 自然连接条件基本上是具有相同列名的表之间的等值连接；
• 如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；
• 用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；
• ON子句可以提高代码的可读性。

SELECT 	e.empno, e.ename, d.loc,m.ename 
FROM   		emp e 
JOIN   		dept d 
ON     		e.deptno = d.deptno 
JOIN   		emp m 
ON     		e.mgr = m.empno; 

外部连接

在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。

左外连接（主表在左边）

左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

SELECT 	e.ename,e.deptno,d.loc 
FROM   	emp e 
LEFT OUTER JOIN dept d 
ON     	(e.deptno = d.deptno);

右外连接（主表在右边）

右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来

SELECT 	e.ename,e.deptno,d.loc 
FROM   	emp e 
RIGHT  OUTER JOIN dept d 
ON     	(e.deptno = d.deptno);