What is the difference between an INNER JOIN and an OUTER JOIN in MySQL?

When using SQL to interact with a database, you may wish to retrieve data from one table, and then, based on what you find, pull data from another table.

This can be achieved by writing multiple queries, or, by using a JOIN to query both tables at the same time.

For example:

Your database contains two tables: musicians and tracks. The musicians table includes a selection of musicians and various bits of information about them. The tracks table contains information regarding albums and tracks. You want to pull the information about the musician, and also a list of their music.

You could make two queries to the database

SELECT * FROM musicians WHERE musician='The Rolling Stones';

SELECT * FROM tracks WHERE musician='The Rolling Stones';

Or, you could use a JOIN statement, to query both tables at the same time

SELECT * FROM musicians
JOIN tracks USING(musician)
WHERE musician='The Rolling Stones'

When writing a JOIN statement, there are 3 things that need to be included:

1. The type of JOIN
2. The table that you are joining.
3. The variable that is common between the tables.

INNER JOIN table2 USING(variable)

INNER JOIN table2 ON table1.variable=table2.variable

In the previous example, the common variable had the same name in both tables. Had the variable been called track_musician in the tracks table, I would not have been able to use the USING() syntax, and instead would have joined ON musician=track_musician.

Had I written the example using the ON syntax, but with both variables called the same thing, then my data set would include the 'musicians' column twice. This can lead to problems when you require the data to be used in an associative array, as you will have two variables with the same name.

It is also worth pointing out that the variable should be an INDEX in both tables. Using a JOIN without this will cause the database engine to look through every item of data. In large databases this will cause a massive performance hit, so make sure you set the INDEX before hand.

So what IS the difference between and INNER and OUTER JOIN?

An INNER JOIN should be used when you only want to pull data that exists in both of the tables. Any entry found in the first table, that does not also have an entry in the second table, will not be included in the result set.

An OUTER JOIN should be used when you want to pull all of the matching data in the first table, regardless of whether or not it exists in the second table. Your result set will contain NULL for the variables that would have been found in the second table

For Example.

If you wanted to retrieve a list of all musicians and their tracks, but only retrieve data for a musician if there are tracks stored for them in the tracks table.

SELECT * FROM musicians
INNER JOIN tracks USING(musician)

If you wanted to retrieve a list of all musicians and their tracks, regardless of whether or not they actually have some data in the tracks table.

SELECT * FROM musicians
OUTER JOIN tracks USING(musician)