---
# System prepended metadata

title: inner join vs. outer join

---

# 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.
    ![](https://i.imgur.com/xBaJxxG.png)
* 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**.
    ```sql=
    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**.
    ```sql=
    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**.
    ```sql=
    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**.
    ```sql=
    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    |