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 RIGHT JOIN

MySql Right Join

Here you find information about writing RIGHT JOINs (also referred to as RIGHT OUTER JOINs). This introduction into right joins includes a detailed description, syntax information and right outer join example statements. The Venn diagram on the left represents a result set that a statement with a right join produces. refer to the syntax examples below for an example.

Right Join syntax
First of all, some syntax examples for the impatient:

— right join with USING-clause
SELECT *
FROM RIGHT JOIN
USING(id)

— right join with ON-clause
SELECT *
FROM a RIGHT JOIN b
ON a.name = b.authorName

As you can see, a join condition can be written with the keyword ON or the keyword USING. The difference is that the ON keyword is used when each relationship column has a different name and USING when a column with the same name exists in both tables.
Reference table, left and right table?

When we join two tables, there is always a left and a right table (take a look at our syntax examples):

• The left table is listed on the left side of the OUTER JOIN keywords
• The right table is listed on the right side of the OUTER JOIN keywords

The outer join which is used decides which table is treated as the reference table. A left join treats the left- and a right join the right table as the reference table. Do you recognize the reference table in the syntax examples? Alright, fasten your seat belts. We’re ready to take off.
Right Outer Joins vs Inner joins

A right outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a right outer join this means that when there is a row in the right table which can’t be combined with any row in the left table (according to the join condition), MySQL…

• takes all selected values from the right table
• combines them with the column names from the left table.
• sets the value of every column from the left table to NULL

This is the important difference, because an inner join is not able to select records from a reference table that have no related data in another.