What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

The difference is in the way tables are joined if there are no common records.
JOIN is same as INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in join clause. For example:

FROM t1
JOIN t2 on t1.ID = t2.ID

means show only records where the same ID value exists in both tables.
LEFT JOIN is same as LEFT OUTER JOIN and means to show all records from left table (i.e. the one that precedes in SQL statement) regardless of the existance of matching records in the right table.
RIGHT JOIN is same as RIGHT OUTER JOIN and means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.

MySQL INNER JOIN

My SQL Inner Join Example

This Article shows you how to write inner joins with the INNER JOIN keywords.

Please note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN which means: All example statements found in this article work fine when you use JOIN or CROSS JOIN instead of INNER JOIN.

Syntax
Here are syntax examples for the impatient. A join condition can be specified in two different ways. Take a look at the following join examples:

— inner join with USING clause

SELECT *
FROM a INNER JOIN b
USING()

— inner join with ON clause

SELECT *
FROM a INNER JOIN b
ON a. = b.

Now we should take a closer look at inner joins and what they really do.

Basics

The most common join operation MySQL supports is an inner join. It identifies and combines only matching rows which are stored in two related tables. A join condition which indicates how the tables are related, is added with the keywords ON or USING :

ON is used when the relationship column has a different name
USING is used when the relationship column has the same name in both tables
Take a look at the examples:

— INNER JOIN with ON clause
SELECT *
FROM tableA a
INNER JOIN tableB b
ON  a.someColumn = b.otherColumn

— INNER JOIN with USING clause
SELECT *
FROM tableA a
INNER JOIN tableB b
USING(columnName)

Inner Join vs Cross Join

In MySQL, the keywords CROSS JOIN and INNER JOIN are synonymous. ANSI SQL defines a CROSS JOIN as a join without a condition which builds the Cartesian product of two tables. In that case, MySQL combines every row in the left table with every row in the right table and returns the result set.

— inner join without a condition: cross join
SELECT *
FROM CROSS JOIN

When you have to build the Cartesian product of two tables, use the CROSS JOIN keywords to indicate your intensions. It makes it easy to read your statement and of course, keeps your code more portable.

Inner Join vs Outer Join
The major dhifference between outer joins and inner joins is: an outer join is able to identify rows that were not matched by any row in the joined table.
More than one join
It’s also not uncommon to add more than one join to a single statement. There is no additional syntax required. You only have to write a second (a third, and so on) join:

— more than one inner join
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.someColumn = b.otherColumn
INNER JOIN tableC c
ON b.anotherColumn = c.nextColumn