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

## 选项

### A

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

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

### C

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