【数据库笔记】多表连接

等值连接

笛卡尔积（交叉连接）：

SELECT * FROM emp,dept 

实际使用时一般要避免笛卡尔积情况 ，解决办法为加where条件：

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno;

 使用AND运算符增加其它查询条件

SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno AND t2.loc='NEW YORK';

限制歧义列名：

在用到多个表时可以使用表名作前缀来限定列；

通过使用表前缀可以提高性能；

通过使用列的别名可以区分来自不同表但是名字相同的列；

SELECT ename,t1.deptno FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno AND t2.loc='NEW YORK';

非等值连接

SELECT e.ename,e.sal, s.grade FROM emp e,salgrade s WHERE e.sal>s.losal and e.sal<s.hisal;

SELECT e.ename,e.sal, s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

自身连接

-- 找SMITH的经理
-- emp t1员工表   emp t2经理表
SELECT t2.ename FROM emp t1,emp t2 WHERE t1.mgr=t2.empno AND t1.ename='SMITH';

sql 99标准

交叉连接（同笛卡尔积）：

SELECT * FROM emp CROSS JOIN dept;

自然连接（同等值连接）：

• 自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；
• 如果两个表之间相同名称的列的数据类型不同，则会产生错误；
• 使用NATURAL JOIN子句来完成。

SELECT * FROM emp NATURAL JOIN dept;

using子句

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

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

SELECT * FROM emp JOIN dept USING(deptno);

on子句（将连接条件从where中放入on中）

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

SELECT * FROM emp JOIN dept ON(emp.deptno=dept.deptno);

外部连接

• 在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。
• 左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。
• 右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来

左外连接（左表为基表全显示）

SELECT * FROM emp LEFT OUTER JOIN dept ON (emp.deptno=dept.deptno);

右外连接（右表为基表全显示）

SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno);