MySQL Inner Join Tutorial (Including Examples)

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.

The author, Jan Brinkmann, is a freelance web developer and Linux administrator. He has more than 10 years of experience with open-source web solutions and web servers and worked 5 years as a web- and database server administrator. More MySQL join tutorials can be found here:

- MySQL Join Tutorials

Article Source: http://EzineArticles.com/?expert=Jan_Brinkmann