This tutorial shows you how to write ANSI-Style inner joins with the INNER JOIN keywords. Included are a general description, some syntax examples and a comparison between inner and cross joins.

Note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN. That means: All example statements found in this article work fine when you replace INNER JOIN with JOIN or CROSS JOIN.


Syntax

Here are syntax examples for the impatient. Basically, ANSI-style join conditions can be specified with two different keywords: USING and ON. Take a look at the following examples:

-- inner join with USING clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
USING(<columnName>)


-- inner join with ON clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
ON a.<someColumn> = b.<anotherColumn>

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

Basics

Probably 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 <firstTable> CROSS JOIN <anotherTable>

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 difference between outer and inner joins is: an outer join is able to identify rows that were not matched by any row in the joined table. Therefor, if you’re searching for rows from tableA that have no related entry in tableB at all, you have to use an outer join. Please see the tutorial about outer joins for a more information.

More than one join

It’s also very common to add more than one join to a single statement. There is no special 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

Inner Join with comma operator

An inner join can also be written with the help of the so called comma operator. Please read the dedicated tutorial about writing inner joins with the comma operator for more information.

You can host your MySQL projects on regular web hosting packages or a dedicated server. Nowadays, all of them should support inner joins.

External Resources