多表连接

来源：互联网发布：怎样用淘宝客推广编辑：程序博客网时间：2024/06/09 22:28

第五章：多表连接

什么是连接

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

• 语法为：

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

–在WHERE子句中书写连接条件。

–如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。

–N个表相连时，至少需要N－1个连接条件。

笛卡尔积：

每一行分别和第二个表的每一行分别连接即m*n（为第一个表行数，n为第二个表行数）

产生原因：

连接条件无效或者省略（所以where中要写上正确的连接条件）

等值连接

SQL> SELECT emp.empno, emp.ename, emp.deptno,

2 dept.deptno, dept.loc

3 FROM emp, dept

4 WHERE emp.deptno=dept.deptno;

And连接运算符

：用and连接符增加其它查询条件（表之间的等值条件在前，其余限定条件在后）

限定歧义列名：即SELECT e.empno,e.deptno,e.ename,d.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno

改变表名后必须全部改变

练习1

• 1.写一个查询，显示所有员工姓名，部门编号，

部门名称。

SELECT emp.empno,emp.ename,emp.deptno,dept.loc

FROM emp,dept

WHERE emp.deptno=dept.deptno

• 2.写一个查询，显示所有工作在CHICAGO并且奖

金不为空的员工姓名，工作地点，奖金

SELECT e.ename,d.loc,e.comm
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.loc='CHICAGO'
AND e.comm IS NOT NULL

• 3.写一个查询，显示所有姓名中含有A字符的员

工姓名，工作地点。

SELECT e.ename,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.ename LIKE'%A%'

多表的连接和两个表一样，只不过要多考虑一个表之间的关联条件

非等值连接：• 查询每个员工的姓名，工资，工资等级

SQL> SELECT e.ename, e.sal, s.grade

2 FROM emp e, salgrade s

3 WHERE e.sal

4 BETWEEN s.losal AND s.hisal;

ENAME SAL GRADE

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

JAMES 950 1

SMITH 800 1

ADAMS 1100 1

...

14 rows selected.

多表连接的写法

• 1.分析要查询的列都来自于哪些表，构成FROM子句;

• 2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;

• 3.接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;

• 4.分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;

• 5.根据用户想要显示的信息，补充SELECT子句。

• 6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

练习2

• 1.查询每个员工的编号，姓名，工资，工资等级，所在工

作城市，按照工资等级进行升序排序。

SELECT e.empno,e.ename,e.sal,s.grade,d.loc
FROM emp e, dept d,salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
ORDER BY s.grade ASC

外部连接：

查看哪些行按照连接条件没有被匹配上

（+）号加在缺少信息匹配的那个表的一端

（+）在左边属于右外连接

--查询所有部门信息，以及部门的员工，即使部门没有员工也要显示出来
SELECT e.empno,e.ename,d.dname
FROM emp e, dept d
WHERE --e.deptno=d.deptno(+)
d.deptno(+)=e.deptno

SELECT d.*,e.empno,e.ename
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno---(有一个没有部门所以没显示，只显示了13个员工)

自身连接：

SQL> SELECT worker.ename||' leader is '||manager.ename

2 FROM emp worker, emp manager

3 WHERE worker.mgr = manager.empno; （自身连接的条件）

WORKER.ENAME||'leader is'||MANAG

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

BLAKE leader is KING

CLARK leader is KING

JONES leader is KING

MARTIN leader is BLAKE

...

13 rows selected.

练习3

• 1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

SELECT e.ename, e.empno,m.ename,m.empno
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND d.deptno=e.deptno
AND d.loc IN('NEW YORK','CHICAGO')

• 2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

SELECT e.ename, e.empno,m.ename,m.empno
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno(+) （没有经理，则对应的经理表中就为空，所以+添在奖励表这里）
AND d.deptno=e.deptno
AND d.loc IN('NEW YORK','CHICAGO')

• 3.查询所有员工编号，姓名，部门名称，包括没

有部门的员工也要显示出来。

--3

SELECT d.*,e.empno,e.ename

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno （d里边有所有的部门，而e里边并不是所有的都有，即缺的，所以添在e里边）

SQL：1999 语法的连接

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

CROSS JOIN 子句

使用CROSS JOIN 子句完成。

SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc

FROM emp

CROSS JOIN dept;

等价于：SELECT * FROM emp,dept

自然连接：是对两个表之间相同名字和数据类型的列进行等值连接如果两个表之间相同名称的列的数据类型不同，则会产生错误

NATUTAL JOIN子句

SELECT empno,ename,sal,deptno,loc

FROM emp

NATURAL JOIN dept;

等价于SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno

USING子句

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

Using子句在自然连接基础上做的

SELECT e.ename,e.ename,e.sal,deptno,d.loc

FROM emp e JOIN dept d USING (deptno)

WHERE deptno = 20 ;

等价于：SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno

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

–如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。

–如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。

–USING子句中的用到的列不能使用表名和别名作为前缀。不能用e.ename等写法

–NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用。

ON子句

• 自然连接条件基本上是具有相同列名的表之间的等值连接；

• 如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；

• 用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；

• ON子句可以提高代码的可读性。

SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc

FROM emp e

JOIN dept d

ON (e.deptno = d.deptno);

等价于
SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno

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);

等价于：SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno(+)

右外连接

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

来。

SELECT e.ename,e.deptno,d.loc

FROM emp e

RIGHT OUTER JOIN dept d

ON (e.deptno = d.deptno);

全外连接

• 全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录

SELECT e.ename,e.deptno,d.loc

FROM emp e

FULL OUTER JOIN dept d

ON (e.deptno = d.deptno);

ENAME DEPTNO LOC

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

…

MILLER 10 NEW YORK

KING 10 NEW YORK

CLARK 10 NEW YORK

……

WARD

BOSTON （失败记录）

已选择行。

练习4

• 使用SQL-99写法，完成如下练习

• 1.创建一个员工表和部门表的交叉连接。

SELECT *
FROM emp
CROSS JOIN dept

• 2.使用自然连接，显示入职日期在80年5月1日之后的员工

姓名，部门名称，入职日期

SELECT ename,dname,hiredate
FROM emp
NATURAL JOIN dept
WHERE hiredate >'01-5月-80'

• 3.使用USING子句，显示工作在CHICAGO的员工姓名，部门

名称，工作地点

SELECT ename,dname, loc
FROM emp JOIN dept USING (deptno)
WHERE loc='CHICAGO'

• 4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名

称，工作地点，薪资等级

SELECT e.ename, d.dname,d.loc,s.grade
FROM emp e JOIN dept d
ON (e.deptno=d.deptno)
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal

• 5.使用左连接，查询每个员工的姓名，经理姓名，没有经

理的King也要显示出来。

SELECT e.ename,m.ename
FROM emp e
LEFT OUTER JOIN emp m
ON e.mgr=m.empno

• 6.使用右连接，查询每个员工的姓名，经理姓名，没有经

理的King也要显示出来。

SELECT e.ename,m.ename
FROM emp m
RIGHT OUTER JOIN emp e
ON e.mgr=m.empno

课后作业

• 1.显示员工SMITH的姓名，部门名称，直接上级名称

SELECT e.ename 员工姓名,dname 部门名称,m.ename 直接上级名称
FROM emp e,emp m, dept d
WHERE e.deptno=d.deptno
AND e.mgr= m.empno
AND e.ename='SMITH'

• 2.显示员工姓名，部门名称，工资，工资级别，要求工资

级别大于4级。

SELECT e.ename 员工姓名,dname 部门名称,e.sal 工资,s.grade 工资级别
FROM emp e, dept d,salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND s.grade>4

• 3.显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT e.ename 员工姓名,m.ename 经理姓名
FROM emp e,emp m
WHERE e.mgr=m.empno
AND m.ename IN('KING','FORD')

• 4.显示员工姓名，参加工作时间，经理名，参加工作时间，

要求参加时间比经理早。

SELECT e.ename 员工姓名, e.hiredate 参加工作时间 ,m.ename 经理姓名, m.hiredate 经理参加工作时间
FROM emp e,emp m
WHERE e.mgr=m.empno
AND e.hiredate< m.hiredate..

0 0