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:

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:

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;

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;