内外连接

# 内连接

SELECT ... FROM t1 INNER JOIN t2 ON 连接条件 [INNER JOIN t3 ON 连接条件] ... AND 其他条件;

显示SMITH的名字和部门名称

方式1

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno and emp.ename='SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+

1
2
3
4
5
6

方式2

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
2
3
4
5
6

# 外连接

# 左外连接

左外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明:

SELECT ... FROM t1 LEFT JOIN t2 ON 连接条件 [LEFT JOIN t3 ON 连接条件] ... AND 其他条件;

# 右外连接

右外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明：

SELECT ... FROM t1 RIGHT JOIN t2 ON 连接条件 [RIGHT JOIN t3 ON 连接条件] ... AND 其他条件;

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

mysql> select dept.deptno, emp.ename, emp.empno, dept.dname from dept left join emp on dept.deptno=emp.deptno order by dept.deptno as
sc;
+--------+--------+--------+------------+
| deptno | ename  | empno  | dname      |
+--------+--------+--------+------------+
|     10 | MILLER | 007934 | ACCOUNTING |
|     10 | KING   | 007839 | ACCOUNTING |
|     10 | CLARK  | 007782 | ACCOUNTING |
|     20 | FORD   | 007902 | RESEARCH   |
|     20 | ADAMS  | 007876 | RESEARCH   |
|     20 | SCOTT  | 007788 | RESEARCH   |
|     20 | JONES  | 007566 | RESEARCH   |
|     20 | SMITH  | 007369 | RESEARCH   |
|     30 | JAMES  | 007900 | SALES      |
|     30 | TURNER | 007844 | SALES      |
|     30 | BLAKE  | 007698 | SALES      |
|     30 | MARTIN | 007654 | SALES      |
|     30 | WARD   | 007521 | SALES      |
|     30 | ALLEN  | 007499 | SALES      |
|     40 | NULL   |   NULL | OPERATIONS |
+--------+--------+--------+------------+
15 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

上次更新: 2025/03/14, 11:20:46

← 复合查询索引特性→