DoubleNotExists.md

# Double Not Exists Check

风控部门的数据库中有一组关于招投标的数据，关键信息如下：

```sql
-- 招标项目
create table invitation
(
    id         int primary key auto_increment,
    name       varchar(256),
    attachment text
    -- ignore more detail ...
);

-- 竞标企业
create table company
(
    id   int primary key auto_increment,
    name varchar(256)
    -- ignore more detail ...
);

-- 投标记录
create table bids
(
    id            int primary key auto_increment,
    invitation_id int references invitation (id),
    company_id    int references company (id)
    -- ignore more detail ...
);

```

Joe 想要找出参与了所有投标的企业（有围标嫌疑），那么这个查询应该怎么写？

<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 *
from company
where not exists(
        select *
        from invitation
        where not exists(
                select *
                from bids
                where invitation.id = bids.invitation_id
                  and bids.company_id = company.id
            )
    )
```

## 选项

### A

```sql
select *
from company
where id = all (select company_id
                from bids)
```

### B

```sql
select *
from company
where id = any (select company_id
                from bids)
```

### C

```sql
select *
from company
where company.id in (select distinct company_id from bids) 
```

### D

```sql
select *
from company
where exists(
              select *
              from bids
              where bids.company_id = company.id
          ) 
```