}

Created:

# 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