DoubleNotExists.md

# Double Not Exists Check

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

```mysql
-- 招标项目
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 想要找出参与了所有投标的企业（有围标嫌疑），那么这个查询应该怎么写？

## 答案

```mysql
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

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

### B

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

### C

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

### D

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