left_join.md

# 左连接

现有部门表

```mysql
create table department(
    id int primary key auto_increment,
    name varchar(256)
)
```

和员工表

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

Joe 想要列出所有的部门，如果这个部门有部门助理（post 为 `assistant`），则将 stuff 的名字也列出来，那么这个查询应该是：

## 答案

```mysql
select d.id, d.name, e.name as assistant
from department as d
    left join employee as e on e.dept = d.id
where e.post = 'assistant'
```

## 选项

### A

```mysql
select d.id, d.name, e.name as assistant
from department as d
         cross join employee as e on e.dept = d.id
where e.post = 'assistant'
```

### B

```mysql
select d.id, d.name, e.name as assistant
from department as d
         join employee as e on e.dept = d.id
where e.post = 'assistant'
```

### C

```mysql
select d.id, d.name, e.name as assistant
from department as d
         cross join employee as e on e.dept = d.id
where e.post = 'assistant'
```
 
### D

```mysql
select d.id, d.name, e.name as assistant
from employee as e
    left join department as d on e.dept = d.id
where e.post = 'assistant'
```