subquery.md

# 子查询

现有员工表

```sql
create table employee
(
    id     serial primary key,
    name   varchar(256),
    dept   varchar(256),
    salary decimal(12, 4)
);
```

Joe 希望找出比销售部（dept 为 sale）工资最高的员工工资更高的那部分人，查询出他们的完整信息，下面哪一项可以满足要求？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
select id, name, dept, salary
from employee
where salary > (select max(salary)
                from employee
                where dept = 'sale')
```

## 选项

### A

```sql
select id, name, dept, salary
from employee
where dept = 'sale'
group by dept
having salary > max(salary)
```

### B

```sql
select l.id, l.name, l.dept, l.salary
from employee as l
         join employee as r 
             on l.salary > max(r.salary)
where r.dept = 'sale'
group by r.dept 
```

### C

```sql
select id, name, dept, salary
from employee
having salary > (select max(salary) 
                 from employee 
                 where dept = 'sale')
```