An INNER JOIN is usually used to query the same table twice and join the results as one result set.

This example will use the 'world.sql' sample database provided by http:dev.myql.com/doc

The 'country' table contains the year of each countries independence, if any. 

mysql> SELECT name, IndepYear FROM country LIMIT 10;
+----------------------+-----------+
| name                 | IndepYear |
+----------------------+-----------+
| Aruba                |      NULL |
| Afghanistan          |      1919 |
| Angola               |      1975 |
| Anguilla             |      NULL |
| Albania              |      1912 |
| Andorra              |      1278 |
| Netherlands Antilles |      NULL |
| United Arab Emirates |      1971 |
| Argentina            |      1816 |
| Armenia              |      1991 |
+----------------------+-----------+
10 rows in set (0.00 sec)

Let's say that we want a list that contains the name of each country and the date that country gained independence. In the same row we also want to see the name of one country that shares the same independence date. We can accomplish this with an inner join on the country table: 

mysql> SELECT c1.name, c2.name AS same, c1.IndepYear
    -> FROM country AS c1 INNER JOIN country AS c2
    -> ON c1.IndepYear = c2.IndepYear LIMIT 10;
+-----------------------+-------------+-----------+
| name                  | same        | IndepYear |
+-----------------------+-------------+-----------+
| Afghanistan           | Afghanistan |      1919 |
| Angola                | Angola      |      1975 |
| Comoros               | Angola      |      1975 |
| Cape Verde            | Angola      |      1975 |
| Mozambique            | Angola      |      1975 |
| Papua New Guinea      | Angola      |      1975 |
| Sao Tome and Principe | Angola      |      1975 |
| Suriname              | Angola      |      1975 |
| Albania               | Albania     |      1912 |
| Andorra               | Andorra     |      1278 |
+-----------------------+-------------+-----------+
10 rows in set (0.00 sec)

This gives us the results we were looking for but is slightly redundant as each record will compare itself, to itself, creating an obvious match. This can be fixed by adding an extra line to the WHERE clause that makes sure the two countries being compared are not the same record. 

mysql> SELECT c1.name, c2.name AS same, c1.IndepYear
    -> FROM country AS c1 INNER JOIN country AS c2
    -> ON c1.IndepYear = c2.IndepYear AND c1.name != c2.name
    -> LIMIT 10;
+-----------------------+----------------------+-----------+
| name                  | same                 | IndepYear |
+-----------------------+----------------------+-----------+
| Comoros               | Angola               |      1975 |
| Cape Verde            | Angola               |      1975 |
| Mozambique            | Angola               |      1975 |
| Papua New Guinea      | Angola               |      1975 |
| Sao Tome and Principe | Angola               |      1975 |
| Suriname              | Angola               |      1975 |
| Bangladesh            | United Arab Emirates |      1971 |
| Bahrain               | United Arab Emirates |      1971 |
| Qatar                 | United Arab Emirates |      1971 |
| Azerbaijan            | Armenia              |      1991 |
+-----------------------+----------------------+-----------+
10 rows in set (0.00 sec)

You might also be interested in