mysql子查询在select后面连接子查询

最新推荐文章于 2024-08-19 16:45:00 发布

蔚蓝天空857

最新推荐文章于 2024-08-19 16:45:00 发布

阅读量1.2k

点赞数

文章标签： mysql sql plsql

本文链接：https://blog.csdn.net/qq_45148387/article/details/116354715

版权

在select后面嵌套子查询。

案例：找出每个成员所在的部门名称，要求显示员工名和部门名。

mysql>  select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

不过不常用。

常用的是：

mysql> select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+