This tutorial shows you how to write ANSI-style MySQL inner joins with the help of the INNER JOIN keywords. Included are a short introduction and some example statements.
Syntax
First of all, some examples for the impatient. In MySQL, a join condition can be specified in two different ways. Take a look at the following statements:
– 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
Basics
A very common operation in SQL statements is the inner join. It identifies and combines only rows from related database tables when a match can be found in both tables. A condition is used to specify in which way data that is stored in the addressed tables is related. To specify the join condition, the keywords ON or USING can be used:
- 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 following examples:
– ON clause
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.someColumn = b.otherColumn
– USING clause
SELECT *
FROM tableA a
INNER JOIN tableB b
USING (columnName)
Inner Join, Cross Join and Join
In MySQL, the keywords CROSS JOIN AND JOIN are synonymous with the keywords INNER JOIN. The ANSI SQL standard describes a CROSS JOIN as a join without a condition. It’s the Cartesian product of two tables and called a cross join. In MySQL it’s simply an inner join without a condition. The result set is the Cartesian product of these tables.
When you want MySQL to build the Cartesian product of two tables, use the CROSS JOIN keywords to indicate that intension. It makes it more easy to read your statement and keeps your code portable between different relational database management systems (RDBMS) such as Oracle or Postgres.
More than one join
It’s also not uncommon to combine more than one table with a single statement. And there is nothing special about it. You only have to add joins and (most probably) conditions:
– more than one
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.someColumn = b.otherColumn
INNER JOIN tableC c
ON b.anotherColumn = c.nextColumn
MySQL then creates a large result set in two first steps. The first step is to combine rows from tableA and tableB. In the second step rows from tableB and tableC are combined.