JOIN Clause 连接查询

笛卡尔积现象

查询前先从笛卡尔积现象开始考虑

内连接

(1) 等值连接：表连接条件是等量关系

SQL99 Syntax: ... A (inner) join B on 表连接条件 where 数据过滤条件...

SQL99语法优于SQL92语法，因为SQL99将表连接条件（join）与数据过滤条件（where）分离，SQL99语法结构更清晰

Problem：查询每个员工的部门名称，显示员工名和部门名。

mysql> select ename,deptno from emp;       mysql> select deptno,dname from dept;  
+--------+--------+                        +--------+------------+
| ename  | deptno |                        | deptno | dname      |
+--------+--------+                        +--------+------------+
| SMITH  |     20 |                        |     10 | ACCOUNTING |
| ALLEN  |     30 |                        |     20 | RESEARCH   |
| WARD   |     30 |                        |     30 | SALES      |
| JONES  |     20 |                        |     40 | OPERATIONS |
| MARTIN |     30 |                        +--------+------------+
| BLAKE  |     30 |                        4 rows in set (0.00 sec)
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

SQL92：select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
SQL99：select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; (常用)

(2) 非等值连接：表连接条件是非等量关系

 Problem：查询每个员工的工资等级，显示员工名、工资和工资等级。

mysql> select ename,sal from emp;       mysql> select * from salgrade;
+--------+---------+                    +-------+-------+-------+
| ename  | sal     |                    | GRADE | LOSAL | HISAL |
+--------+---------+                    +-------+-------+-------+
| SMITH  |  800.00 |                    |     1 |   700 |  1200 |
| ALLEN  | 1600.00 |                    |     2 |  1201 |  1400 |
| WARD   | 1250.00 |                    |     3 |  1401 |  2000 |
| JONES  | 2975.00 |                    |     4 |  2001 |  3000 |
| MARTIN | 1250.00 |                    |     5 |  3001 |  9999 |
| BLAKE  | 2850.00 |                    +-------+-------+-------+
| CLARK  | 2450.00 |                    5 rows in set (0.01 sec)
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

 (3) 自连接：一张表看作两张表，自己连自己

 Problem：查询每个员工的上级领导，显示员工名和领导名。

mysql> select empno,ename,mgr from emp;

员工表                            领导表
+-------+--------+------+        +-------+--------+------+
| empno | ename  | mgr  |        | empno | ename  | mgr  |
+-------+--------+------+        +-------+--------+------+
|  7369 | SMITH  | 7902 |        |  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |        |  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |        |  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |        |  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |        |  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |        |  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |        |  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |        |  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |        |  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |        |  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |        |  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |        |  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |        |  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |        |  7934 | MILLER | 7782 |
+-------+--------+------+        +-------+--------+------+
14 rows in set (0.00 sec)

select a.ename,b.ename from emp a join emp b on a.mgr=b.empno;

Subquery 子查询

mysql> select distinct mgr from emp; //领导编号
+---