MySQL內(nèi)外連接實(shí)戰(zhàn)詳解

更新時(shí)間：2025年08月07日 09:22:42 作者：茉莉玫瑰花茶

文章詳解MySQL內(nèi)連接與外連接,內(nèi)連接通過WHERE篩選匹配記錄,外連接包括左、右連接,保留一側(cè)全數(shù)據(jù),包含案例與練習(xí)及LeetCode實(shí)戰(zhàn)題目,對(duì)mysql內(nèi)外連接實(shí)戰(zhàn)案例相關(guān)知識(shí)感興趣的朋友一起看看吧

表的連接分類

表的連接主要分為內(nèi)連接和外連接兩種類型。

內(nèi)連接

內(nèi)連接實(shí)際上就是利用WHERE子句對(duì)兩種表形成的笛卡爾積進(jìn)行篩選，這是我們前面學(xué)習(xí)的主要查詢方式，也是開發(fā)過程中使用最多的連接查詢。

我們除了可以使用“ , ”來隔離表，我們還可以使用下面的語法：和我們之前笛卡爾積，后面使用where條件篩選是一樣的效果的！只不過下面的寫法是比較標(biāo)準(zhǔn)的！

SELECT 字段 FROM 表1 INNER JOIN 表2 ON 連接條件 AND 其他條件；

前面學(xué)習(xí)的大部分查詢都屬于內(nèi)連接。

案例：顯示SMITH的名字和部門名稱

傳統(tǒng)寫法：

SELECT ename, dname FROM EMP, DEPT WHERE EMP.deptno=DEPT.deptno AND ename='SMITH';

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno and emp.ename='SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

標(biāo)準(zhǔn)內(nèi)連接寫法：

SELECT ename, dname FROM EMP INNER JOIN DEPT ON EMP.deptno=DEPT.deptno AND ename='SMITH';

mysql> select emp.ename, dept.dname from emp inner join dept on emp.deptno=dept.deptno and emp.ename='SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

外連接

外連接分為左外連接和右外連接。

為主的在哪一邊，基本就是使用那一邊的連接方式！--- 后面的練習(xí)部分體現(xiàn)?。?！

左外連接

如果聯(lián)合查詢時(shí)，左側(cè)的表完全顯示（左側(cè)的信息不要做任何的過濾和篩選，完全保留）（即使右側(cè)沒有匹配記錄），我們就稱為左外連接。

僅僅將 inner join 改成了 left join

SELECT 字段名 FROM 表名1 LEFT JOIN 表名2 ON 連接條件

創(chuàng)建測試表：

CREATE TABLE stu (id INT, name VARCHAR(30)); -- 學(xué)生表
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
CREATE TABLE exam (id INT, grade INT); -- 成績表
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+
3 rows in set (0.00 sec)

查詢所有學(xué)生的成績，如果學(xué)生沒有成績也要顯示個(gè)人信息：

SELECT * FROM stu LEFT JOIN exam ON stu.id=exam.id;

mysql> select * from stu left join exam on stu.id=exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | NULL |  NULL |
|    4 | nono | NULL |  NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)

這個(gè)查詢會(huì)顯示左邊表的所有記錄，即使右邊表沒有匹配的記錄。

右外連接

如果聯(lián)合查詢時(shí)，右側(cè)的表完全顯示（即使左側(cè)沒有匹配記錄），我們就稱為右外連接。

SELECT 字段 FROM 表名1 RIGHT JOIN 表名2 ON 連接條件；

對(duì)stu表和exam表聯(lián)合查詢，顯示所有成績，即使沒有對(duì)應(yīng)學(xué)生：

SELECT * FROM stu RIGHT JOIN exam ON stu.id=exam.id;

mysql> select * from stu right join exam on stu.id=exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
| NULL | NULL |   11 |     8 |
+------+------+------+-------+
3 rows in set (0.00 sec)

練習(xí)：

列出部門名稱和這些部門的員工信息，同時(shí)列出沒有員工的部門

方法一（使用左連接）：

SELECT d.dname, e.* FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno;

mysql> select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| ACCOUNTING | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| ACCOUNTING | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| ACCOUNTING | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| RESEARCH   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| RESEARCH   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| RESEARCH   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| RESEARCH   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| RESEARCH   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| SALES      | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| SALES      | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| SALES      | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| SALES      | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| SALES      | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| SALES      | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| OPERATIONS |   NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)

方法二（使用右連接）：

SELECT d.dname, e.* FROM emp e RIGHT JOIN dept d ON d.deptno=e.deptno;

mysql> select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| ACCOUNTING | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| ACCOUNTING | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| ACCOUNTING | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| RESEARCH   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| RESEARCH   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| RESEARCH   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| RESEARCH   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| RESEARCH   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| SALES      | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| SALES      | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| SALES      | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| SALES      | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| SALES      | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| SALES      | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| OPERATIONS |   NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)