join_self.md

# 自连接

现有 node 表如下：

```mysql
create table node(
    id int primary key auto_increment,
    pid int,
    content varchar(256)
)
```

现在Joe 想要给出 content 以 `fork-` 开头的所有节点，和它们的子节点，输出 `parent_id, parent_content, child_id, child_content` 。
他应该怎么做？

## 答案

```mysql
select l.id as parent_id, 
       l.content as parent_content,
       r.id as child_id,
       r.content as child_content
from node as l
    join node as r on l.id = r.pid
where l.content like 'fork-%';
```

## 选项

### A

```mysql
select l.id as parent_id, 
       l.content as parent_content,
       r.id as child_id,
       r.content as child_content
from node as l
    right join node as r on l.id = r.pid
where l.content like 'fork-%';
```

### B

```mysql
select l.id as parent_id, 
       l.content as parent_content,
       r.id as child_id,
       r.content as child_content
from node as l,  node as r 
where l.id =(+) r.pid l.content like 'fork-%';
```

### C

```mysql
select l.id as parent_id, 
       l.content as parent_content,
       r.id as child_id,
       r.content as child_content
from node as l
    cross join node as r on l.id = r.pid
where l.content like 'fork-%';
```

### D

```mysql
select l.id as parent_id, 
       l.content as parent_content,
       r.id as child_id,
       r.content as child_content
from node as l
    join node as r on l.pid = r.id
where l.content like 'fork-%';
```