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.

MySQL LEFT JOIN

MySql Left Join

Here you find information about writing LEFT JOINs (also referred to as LEFT OUTER JOINs). This introduction into left joins includes a description, syntax information and example statements that use left outer joins. The Venn diagram on the left represents a result set that a statement with a left join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Left Join syntax

First of all, some syntax examples for the impatient:

— left join with USING-clause
SELECT *
FROM LEFT JOIN
USING(id)

— left join with ON-clause
SELECT *
FROM a LEFT 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 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?

Left Outer Joins vs Inner joins

A left 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 left outer join this means that when there is a row in the left table which can’t be combined with any row in the right table (according to the join condition), MySQL…

• takes all selected values from the left table
• combines them with the column names from the right table.
• sets the value of every column from the right 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.

MySQL INNER JOIN With comma operator

Here you find information about writing inner joins with the comma operator. It’s the most basic way to combine (join) two tables. There is an alternative syntax that can be used, because in MySQL you can write inner joins in two different ways. Another popular way is it to use the INNER JOIN command or synonymous keywords like CROSS JOIN and JOIN.

Syntax

The following examples are equivalent to the INNER JOIN examples, to make it easy to compare them. The first example builds the Cartesian product of two tables: Every row in the left table is combined with every row in the right table. In ANSI SQL, this is called a cross join. MySQL however doesn’t distinguish between inner joins and cross joins.

— inner join without a condition: a cross join
SELECT *
FROM a, b

– inner join with WHERE-clause
SELECT *
FROM a, b
WHERE a. = b.

When you write inner joins using the comma operator, there is only one way to specify the join condition: with a WHERE-clause.

Basics

An inner join combines all matching rows from two related tables. Two rows match if they are related, for example because they share a common column. When you write an inner join with the comma operator, the join condition which reflects a relationship between tables is added as a WHERE clause. You can use inner joins to SELECT, UPDATE or DELETE data that is stored within MySQL tables. Here are two more examples which are equivalent to the INNER JOIN examples:

SELECT *
FROM tableA a, tableB b
WHERE a.someColumn = b.otherColumn

Further information
You have seen how to write inner joins with the comma operator. From My point of view, it’s “OK” to use this feature. However, using the alternative syntax makes it easier to read your joins. It’s considered as a good behavior to write statements where you can directly see if it’s an INNER JOIN or a CROSS JOIN (the Cartesian product of two tables).

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