# inner join vs. outer join
> First of all, it was a shame for me to mix up what is inner join and outer join at the moment, so I must make up for it!

* Here is the picture I mentioned in the job interview.
* To be more specific, I will give you some examples from the common situation in my experience.

## Example: parent and childen

**parent**
| parent_id | father | mother |
| --------- | ------ | ------ |
| 1 | Bob | Lisa |
| 2 | Tony | Wendy |
| 3 | Peter | Vicky |

**children**
| children_id | parent_id | children |
| ----------- | --------- | -------- |
| 1 | 1 | Alan |
| 2 | 1 | Henry |
| 3 | 2 | John |
| 4 | NULL | Jason |

### INNER JOIN
* If we want to query all the parents, who have children, with their children, we can use **inner join**.

    SELECT
      p.father,
      p.mother,
      c.children
    FROM parent p
    INNER JOIN children c
      ON p.parent_id = c.parent_id

| father | mother | children |
| ------ | ------ | -------- |
| Bob | Lisa | Alan |
| Bob | Lisa | Henry |
| Tony | Wendy | John |

### LEFT JOIN
* If we want to query all the parents(the parents may or may not have children) with their children, we can use **left join**.

    SELECT
      p.father,
      p.mother,
      c.children
    FROM parent p
    LEFT JOIN children c
      ON p.parent_id = c.parent_id

| father | mother | children |
| ------ | ------ | -------- |
| Bob | Lisa | Alan |
| Bob | Lisa | Henry |
| Tony | Wendy | John |
| Peter | Vicky | NULL |

### RIGHT JOIN
* If we want to query all the children (the children may or may not have parents) with their parents, we can use **right join**.

    SELECT
      p.father,
      p.mother,
      c.children
    FROM parent p
    RIGHT JOIN children c
      ON p.parent_id = c.parent_id

| father | mother | children |
| ------ | ------ | -------- |
| Bob | Lisa | Alan |
| Bob | Lisa | Henry |
| Tony | Wendy | John |
| NULL | NULL | Jason |

### FULL JOIN
* If we want to query all the parents(the parents may or may not have children) and all the children (the children may or may not have parents), we can use **full join**.

    SELECT
      p.father,
      p.mother,
      c.children
    FROM parent p
    FULL JOIN children c
      ON p.parent_id = c.parent_id

| father | mother | children |
| ------ | ------ | -------- |
| Bob | Lisa | Alan |
| Bob | Lisa | Henry |
| Tony | Wendy | John |
| Peter | Vicky | NULL |
| NULL | NULL | Jason |