exists.md

# Exists

Joe 想从员工表

```sql
create table employee(
    id int primary key auto_increment,
    dept_id int,
    name varchar(256),
    post varchar(16)
)
```

中找出所有其所在部门没有助理（post 为 `assistant`）的员工信息。由于 Joe 没有其它表的查询权限，
他只能查询员工表，并且这一次他想用 exists 实现。这个查询应该是：

<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
from employee as o
where not exists(select * from employee as i where o.dept = i.dept and post='assistant'); 
```

## 选项

### A

```sql
select id, name, dept
from employee as o
where exists(select * from employee as i where o.dept = i.dept and post='assistant'); 
```

### B
```sql
select id, name, dept 
from employee as o
where 'assistant' != exists(select post from employee as i); 
```

### C

```sql
select id, name, dept 
from employee as o
where 'assistant' = not exists(select post from employee as i where o.dept = i.dept); 
```