（一）連接配接查詢？
從一張表中單獨查詢，稱為單表查詢。
emp表和dept表聯合起來進行查詢資料，從emp表中取員工名字，從dept表中取部門名字，這種跨表查詢，多張表聯合起來進行查詢資料的方式稱為連接配接查詢。
（二）分類
标準一：文法年代
SQL92：1992年出現的文法
SQL99：1999年出現的文法
标準二：連接配接方式
内連接配接：
① 等值連接配接
② 非等值連接配接
③ 自連接配接
外連接配接：
① 左外連接配接（左連接配接）
② 右外連接配接（右連接配接）
全連接配接：
笛卡爾積現象
兩張表進行連接配接查詢，沒有任何條件限制的時候，追蹤查詢的條數，是兩張表條數的乘積，此現象被稱為“笛卡爾積現象”
執行個體如下：
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)
mysql> select dname,deptno from dept;
+------------+--------+
| dname | deptno |
+------------+--------+
| ACCOUNTING | 10 |
| RESEARCH | 20 |
| SALES | 30 |
| OPERATIONS | 40 |
+------------+--------+
4 rows in set (0.00 sec)
#從從兩個表中各取一個字段
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
| JONES | RESEARCH |
| JONES | ACCOUNTING |
| MARTIN | OPERATIONS |
| MARTIN | SALES |
| MARTIN | RESEARCH |
| MARTIN | ACCOUNTING |
| BLAKE | OPERATIONS |
| BLAKE | SALES |
| BLAKE | RESEARCH |
| BLAKE | ACCOUNTING |
| CLARK | OPERATIONS |
| CLARK | SALES |
| CLARK | RESEARCH |
| CLARK | ACCOUNTING |
| SCOTT | OPERATIONS |
| SCOTT | SALES |
| SCOTT | RESEARCH |
| SCOTT | ACCOUNTING |
| KING | OPERATIONS |
| KING | SALES |
| KING | RESEARCH |
| KING | ACCOUNTING |
| TURNER | OPERATIONS |
| TURNER | SALES |
| TURNER | RESEARCH |
| TURNER | ACCOUNTING |
| ADAMS | OPERATIONS |
| ADAMS | SALES |
| ADAMS | RESEARCH |
| ADAMS | ACCOUNTING |
| JAMES | OPERATIONS |
| JAMES | SALES |
| JAMES | RESEARCH |
| JAMES | ACCOUNTING |
| FORD | OPERATIONS |
| FORD | SALES |
| FORD | RESEARCH |
| FORD | ACCOUNTING |
| MILLER | OPERATIONS |
| MILLER | SALES |
| MILLER | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
56 rows in set (0.00 sec) 補充：比對過程是将一個中一個所有記錄和另一個表中的資料進行一一比對
解決方案：
連接配接時添加條件，滿足條件可以将所需要的紀錄(有效資料)被篩選出來
條件：通過将兩張表中相同的記錄進行比較
mysql> select ename,dname from emp,dept where emp.deptno = dept.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
#給表起别名，提升效率（SQL92文法）
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec) 說明：查詢的結果變成了14條，但是比對的次數還是56次，并沒有減少。
結論：通過笛卡爾積現象可以得出，表的連接配接次數越多效率越低，盡量避免表的連接配接次數。
（三）查詢方式
I.内連接配接
特點：将比對中滿足條件的資料查詢出來(兩個表之間沒有主次關系)
（1）等值連接配接
執行個體：查詢每個員工所在部門名稱，顯示員工名和部門名？
#SQL92文法
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
#SQL99（說明：inner是可以省略的）
mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec) 總結：
① SQL92的缺點：
結構不清晰，表的連接配接條件和後期進一步篩選的條件，均放到了where的後面。
② SQL99（常用）的優點：
表連接配接條件獨立，連接配接之後，對于進一步的篩選，可以将條件添加到where後。
SQL99的文法：
select ...
from a
join b
on a和b的連接配接條件；
（2）非等值連接配接
說明：指的是條件不是一個等量關系，稱為非等值連接配接
案例：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級。
#查詢員工基本資訊表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
#查詢薪資等級表
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec) （3）自連接配接
說明：一張表看作兩張表
案例： 查詢員工的上級上司，要求顯示員工名和對應的上司名？
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec)
#技巧：将一張表看成是兩張表 一張是員工表 一張是上司表
#員工表 emp a
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
#上司表 emp b
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
#将第一表中員工的上司編号和第二個表中的編号進行比對找到對應的上司名字
mysql> select a.ename'員工名',b.ename '上司名' from emp a join emp b on a.mgr=b.empno;
+--------+--------+
| 員工名 | 上司名 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
13 rows in set (0.00 sec) II.外連接配接
特點：兩個表之間存在主次關系
案例：查詢員工的上級上司，要求顯示員工名和對應的上司名？
說明：要将能比對上的和不能比對上的資料都列印出來
#right作用：将join關鍵字右邊的這張表看作是主表，主要是為了将這張表的資料全部查詢出來，
# 捎帶着關聯查詢左邊的表，在外連接配接中，兩張表連接配接，産生了主次關系
# outer是可以省略的，帶着的話可以增加可讀性
mysql> select e.ename ,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS | #未比對成功的
+--------+------------+
15 rows in set (0.00 sec)
#right：表示的是其左邊的這個表是主表，右邊的表是捎帶表
mysql> select e.ename ,d.dname from dept d left outer join emp e on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS | #未比對成功的
+--------+------------+
15 rows in set (0.00 sec) 說明：
①帶有right是右外連接配接，又叫做右連接配接
②帶有left是左外連接配接，又叫做左連接配接
③任何一左連接配接都有右連接配接的寫法，任何一個右連接配接都有左連接配接的寫法
内連接配接 VS 外連接配接
區分： 帶有關鍵字‘ left ’或‘ right ’的連接配接一定是外連接配接。
查詢條數：
外連接配接的查詢結果一定是大于内連接配接的查詢結果條數。
執行個體：查詢每個員工的上級上司，要求顯示所有員工的名字和上司名？？
mysql> select empno ,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec）
#内連接配接
mysql> select a.ename'員工名',b.ename '上司名' from emp a join emp b on a.mgr=b.empno;
+--------+--------+
| 員工名 | 上司名 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
13 rows in set (0.00 sec)
#外連接配接
mysql> select a.ename'員工名',b.ename '上司名' from emp a left join emp b on a.mgr=b.empno;
+--------+--------+
| 員工名 | 上司名 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.00 sec) 拓展：多表連接配接
文法：
select
...
from
a
join
b
on
a 與 b連接配接的條件
join
c
on
a 與 c連接配接的條件
right join
d
on
a 與 d連接配接的條件
一條SQL語句 中内連接配接和外連接配接可以混合
執行個體：
找出每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、 薪資、薪資等級？
#員工表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
#部門表
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
#薪資等級表
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> select e.ename,e.sal,d.dname,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;
+--------+---------+------------+-------+
| ename | sal | dname | grade |
+--------+---------+------------+-------+
| SMITH | 800.00 | RESEARCH | 1 |
| ALLEN | 1600.00 | SALES | 3 |
| WARD | 1250.00 | SALES | 2 |
| JONES | 2975.00 | RESEARCH | 4 |
| MARTIN | 1250.00 | SALES | 2 |
| BLAKE | 2850.00 | SALES | 4 |
| CLARK | 2450.00 | ACCOUNTING | 4 |
| SCOTT | 3000.00 | RESEARCH | 4 |
| KING | 5000.00 | ACCOUNTING | 5 |
| TURNER | 1500.00 | SALES | 3 |
| ADAMS | 1100.00 | RESEARCH | 1 |
| JAMES | 950.00 | SALES | 1 |
| FORD | 3000.00 | RESEARCH | 4 |
| MILLER | 1300.00 | ACCOUNTING | 2 |
+--------+---------+------------+-------+
14 rows in set (0.00 sec) 改進：添加輸出上司名
#内連接配接
mysql> select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal join emp l on e.mgr=l.empno;
+--------+---------+------------+-------+-------+
| ename | sal | dname | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH | 800.00 | RESEARCH | 1 | FORD |
| ALLEN | 1600.00 | SALES | 3 | BLAKE |
| WARD | 1250.00 | SALES | 2 | BLAKE |
| JONES | 2975.00 | RESEARCH | 4 | KING |
| MARTIN | 1250.00 | SALES | 2 | BLAKE |
| BLAKE | 2850.00 | SALES | 4 | KING |
| CLARK | 2450.00 | ACCOUNTING | 4 | KING |
| SCOTT | 3000.00 | RESEARCH | 4 | JONES |
| TURNER | 1500.00 | SALES | 3 | BLAKE |
| ADAMS | 1100.00 | RESEARCH | 1 | SCOTT |
| JAMES | 950.00 | SALES | 1 | BLAKE |
| FORD | 3000.00 | RESEARCH | 4 | JONES |
| MILLER | 1300.00 | ACCOUNTING | 2 | CLARK |
+--------+---------+------------+-------+-------+
13 rows in set (0.00 sec)
#使用左（外）連接配接
mysql> select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr=l.empno;
+--------+---------+------------+-------+-------+
| ename | sal | dname | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH | 800.00 | RESEARCH | 1 | FORD |
| ALLEN | 1600.00 | SALES | 3 | BLAKE |
| WARD | 1250.00 | SALES | 2 | BLAKE |
| JONES | 2975.00 | RESEARCH | 4 | KING |
| MARTIN | 1250.00 | SALES | 2 | BLAKE |
| BLAKE | 2850.00 | SALES | 4 | KING |
| CLARK | 2450.00 | ACCOUNTING | 4 | KING |
| SCOTT | 3000.00 | RESEARCH | 4 | JONES |
| KING | 5000.00 | ACCOUNTING | 5 | NULL |
| TURNER | 1500.00 | SALES | 3 | BLAKE |
| ADAMS | 1100.00 | RESEARCH | 1 | SCOTT |
| JAMES | 950.00 | SALES | 1 | BLAKE |
| FORD | 3000.00 | RESEARCH | 4 | JONES |
| MILLER | 1300.00 | ACCOUNTING | 2 | CLARK |
+--------+---------+------------+-------+-------+
14 rows in set (0.00 sec)
#起别名後的薪資表
mysql> select e.ename'員工',e.sal'薪水',d.dname'部門名',s.grade'薪水等級',l.ename'上司' from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr=l.empno;
+--------+---------+------------+----------+-------+
| 員工 | 薪水 | 部門名 | 薪水等級 | 上司 |
+--------+---------+------------+----------+-------+
| SMITH | 800.00 | RESEARCH | 1 | FORD |
| ALLEN | 1600.00 | SALES | 3 | BLAKE |
| WARD | 1250.00 | SALES | 2 | BLAKE |
| JONES | 2975.00 | RESEARCH | 4 | KING |
| MARTIN | 1250.00 | SALES | 2 | BLAKE |
| BLAKE | 2850.00 | SALES | 4 | KING |
| CLARK | 2450.00 | ACCOUNTING | 4 | KING |
| SCOTT | 3000.00 | RESEARCH | 4 | JONES |
| KING | 5000.00 | ACCOUNTING | 5 | NULL |
| TURNER | 1500.00 | SALES | 3 | BLAKE |
| ADAMS | 1100.00 | RESEARCH | 1 | SCOTT |
| JAMES | 950.00 | SALES | 1 | BLAKE |
| FORD | 3000.00 | RESEARCH | 4 | JONES |
| MILLER | 1300.00 | ACCOUNTING | 2 | CLARK |
+--------+---------+------------+----------+-------+
14 rows in set (0.00 sec) ![一文讓你徹底了解SQL連接配接查詢[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)