# Introduction

For beginners when learning `JOIN`

it's a common error to ignore the differences between
`LEFT JOIN`

and `RIGHT JOIN`

.

- An inner join of A and B gives the result of A intersect B.
- An outer join of A and B gives the results of A union.

Note: if the table has duplicates value the statement above is not true.

# Examples queries

For all example we are going to use two table A and B. each one with a single column.

The tables are populated with the following data:

A.a | B.b |
---|---|

1 | 3 |

2 | 4 |

3 | 5 |

4 | 6 |

Tables are called A and B, where columna are a and b. To refer to column a of table A we use A.a.

Observations: * On table A we have values 1 and 2 which are not in table B. * On table B we have values 5 and 6 which are not in table A. * Values 3 and 4 are in both Tables.

## Inner join

As we said avobe, inner join will return row in common (3 and 4), since inner join gives the intersection.

```
select * from A INNER JOIN B on A.a = B.b;
select A.*,B.* from A,B where A.a = A.b;
```

The result of the query is:

a | b |
---|---|

3 | 3 |

4 | 4 |

## Left outer join

Left outer join will return all rows in A and any common rows in B.

```
select * from A LEFT OUTER JOIN B on A.a = B.b;
select A.*,B.* from A,B where A.a = B.b(+);
```

a | b | --- | --- | 1 | null 2 | null 3 | 3 4 | 4

Note that values not in common return null.

## Right outer join

Right outer join will return all rows in B and any common rows of table A.

```
select * from A RIGHT OUTER JOIN B on A.a = B.b;
select A.*,B.* from A,B where A.a(+) = B.b;
```

a | b |
---|---|

3 | 3 |

4 | 4 |

null | 5 |

null | 6 |

Note that you can do the same query with LEFT but changing the order of the tables in the query.

## Full outer join

Our article is about right and left joins, but to clear any doubt we also explain full outer join. A full outer join will return the union of A and B. For any values that don't have a corresponding value in the other table, a null will be returned. If both values are in the two tables the will be shown togheter.

```
select * from a FULL OUTER JOIN b on a.a = b.b;
```

a | b |
---|---|

1 | null |

2 | null |

3 | 3 |

4 | 4 |

null | 6 |

null | 5 |