left_join.md

# 左连接

现有部门表

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

和员工表

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

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

<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 d.id, d.name, e.name as assistant
from department as d
         left join (select name, dept_id
                    from employee
                    where post='assistant') as e on e.dept_id = d.id


```

## 选项

### A

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

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

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

```sql
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'
```