天天看點
傳回

MySQL連接配接查詢詳解：外連接配接，内連接配接，全連接配接連接配接查詢

連接配接查詢

1.6.1 什麼是連接配接查詢?

在實際開發中，大部分的情況下都不是從單表中查詢資料，一般都是多張表聯合查詢取出最終的結果。

在實際開發中，一般一個業務都會對應多張表，比如:學生和班級，起碼兩張表。

stuno stu_name class_no classname
1 zs 1 北京大興區亦莊經濟技術開發區第二中學高三1班
1 wer 1 北京大興區亦莊經濟技術開發區第二中學高三1班

學生和班級資訊存儲到一張表中，結果就像上面一樣，資料會存在大量的重複，導緻資料的備援。

1.6.2 連接配接查詢的分類

  • 根據文法出現的年代劃分
    • SQL92（一些老的DBA可能還在使用這種）
    • SQL99（比較新的文法）
  • 根據表的連接配接方式分類
    • 内連接配接
      • 等值連接配接
      • 非等值連接配接
      • 自連接配接
    • 外連接配接
      • 左連接配接
      • 右連接配接
    • 全連接配接（很少使用）

1.6.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 * 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)

可以發現要想實作這個功能，我們需要從兩個表中分别取出兩個資料。

假如我們寫一條這樣的SQL語句：

select ename, dname from emp , dept;

，最後的結果會是兩個字段的笛卡爾乘積，這就是笛卡爾積現象。

即當兩個表的篩選未加任何篩選條件時，就會出現上述現象。

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

此外，補充一點關于表的别名的問題：

select e.ename, d.dname from emp e, dept d;

這樣給表起了别名。

表的别名有什麼好處：

第一：執行效率高。

第二：可讀性好。

  • 若是要完成上面的案例，怎麼避免笛卡爾積現象呢？加條件！！！

思考：避免了笛卡爾積現象會減少記錄的比對次數嗎？

​ 不會！次數還是56次，隻不過顯示的是有效記錄。

還是考慮上面的案例：

select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

這是一種SQL92的寫法：

mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.01 sec)

1.6.3 内連接配接

1.等值連接配接

等值連接配接是内連接配接的一種，條件是等量關系。

案例：查詢每個員工的部門名稱，要求顯示員工名和部門名。

  • SQL92寫法（不常用） 
mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)
  • SQL99（常用） 
mysql> select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)

文法：

select ... from ... join ... on... where ...

SQL99文法結構将表連接配接調節與

where...

資料過濾的條件分離了，這種文法更加清晰。

其中

join

前面省略了一個

inner

，即完整的寫法是：

select ... from ... inner join ... on... where ...
2.非等值連接配接

案例：找出每個員工的工資等級，要求顯示員工名，工資，工資等級。

mysql> select ename, sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

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)

文法為：（

inner

可以省略）

mysql> select e.ename, e.sal, s.grade from emp e inner 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 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
    -> inner join
    -> emp b
    -> on
    -> a.mgr = b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| 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.01 sec)

1.6.4 外連接配接

什麼是外連接配接？和内連接配接有什麼差別？

内連接配接：假設A表和B表進行内連接配接，凡是A表和B表能夠比對上的記錄都查詢出來，這就是内連接配接。

外連接配接：假設A表和B表進行連結，那麼AB兩張表，一張是主表，一張是附表，主要查詢主表中的資料，捎帶着查詢附表，當附表中的資料沒有和副表中給的資料比對上，副表中自動比對出NULL。

外連接配接分類：

  • 左（外）連接配接：表示左邊的表是主表
  • 右（外）連接配接：表示右邊的表是主表

左連接配接有對應的右連接配接的寫法，右連接配接也有對應的左的寫法。

仍是借用1.6.3中的員工上級上司的例子，我們還要求所有員工必須查詢出來，也就是king必須查詢出來（在内連接配接中King不會被查詢出來）

  • 原内連接配接的寫法：

    select a.ename, d.ename from emp a inner join emp d on a.mgr = d.empno;
  • 外連接配接的左外連接配接的寫法：

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

     
mysql> select a.ename, d.ename from emp a inner join emp d on a.mgr = d.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| 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, d.ename from emp a left join emp d on a.mgr = d.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| 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)

可以看出左連接配接的多了一個King。

對應的右連接配接的寫法為：

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

不論是左連接配接還是右連接配接，left和right後面都省略了outer關鍵字。

三種寫法對比：

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

     ：内連接配接

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

     ：左外連接配接

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

     ：右外連接配接

外連接配接最主要的特點：主表資料無條件查詢出來。

案例：找出哪個部門沒有員工。

員工表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

部門表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

可知，部門表為主表，員工表應該為副表。我們先進行下面的外連接配接查詢：

select d.deptno, d.dname, a.* from dept d left outer join emp a on d.deptno = a.deptno;

 
mysql> select d.deptno, d.dname, a.* from dept d left outer join emp a on d.deptno = a.deptno;
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| deptno | dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
|     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|     20 | RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|     30 | SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|     30 | SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|     30 | SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|     30 | SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|     30 | SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|     40 | OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+

在上面查詢基礎上，我們還要進一步篩選才能得到無員工的部門，是以我們加上一個

where

條件即可：

select d.deptno, d.dname, a.* from dept d left outer join emp a on d.deptno = a.deptno where empno is null;

 
mysql> select d.deptno, d.dname, a.* from dept d left outer join emp a on d.deptno = a.deptno where empno is null;
+--------+------------+-------+-------+------+------+----------+------+------+--------+
| deptno | dname      | EMPNO | ENAME | JOB  | MGR  | HIREDATE | SAL  | COMM | DEPTNO |
+--------+------------+-------+-------+------+------+----------+------+------+--------+
|     40 | OPERATIONS |  NULL | NULL  | NULL | NULL | NULL     | NULL | NULL |   NULL |
+--------+------------+-------+-------+------+------+----------+------+------+--------+
1 row in set (0.00 sec)

或者

select d.* from dept d left outer join emp a on d.deptno = a.deptno where empno is null;

 
mysql> select d.* from dept d left outer join emp a on d.deptno = a.deptno where empno is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

1.6.5 全連接配接

兩張表全是主表，一般用的很少，是以不予說明。

1.6.6 多表連接配接查詢

案例：找出每一個員工的部門名稱以及工資等級。

三個資料分布在三個表中：

emp,dept,salgrade

；

  • 多表連接配接查詢的文法：

select ... from A join B on ... join C on ...

表示：A表和B表先進行表連接配接，連接配接之後A表繼續和C表連接配接。

那麼案例的表述可以是：

select e.ename, 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;

 
mysql> select e.ename, 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  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

案例：找出每一個員工的部門名稱以及工資等級和上級上司。

額外要用到自連接配接。最後一個一定要是外連接配接不然King的資料會丢失。

select e.ename, d.dname, s.grade, e2.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

outer join emp e2 on e.mgr = e2.empno;

 
mysql>  select e.ename, d.dname, s.grade, e2.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 outer join emp e2 on e.mgr = e2.empno;
+--------+------------+-------+-------+
| ename  | dname      | grade | ename |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+
14 rows in set (0.00 sec)
資料庫 資料庫系統 mysql sql
上一篇: 按等級查詢和連接配接查詢——mysql學習筆記
下一篇: MySQL連接配接查詢常見坑1

繼續閱讀