MySQL - How to choose inner or outer joins ?

December 2016



Intro


This article is for help beginners to make the best use of SQL INNER and OUTER joins.
A join is used to retrieve information from multiple tables. These tables must be linked by at least one (common) column (name, ID, phone number). This is called a join criterion.

INNER JOIN: intersection of tables


The INNER JOIN is used when you have two tables, and you want to retrieve the information from the rows present in both.
Example: you have the "people" tables that contains a name of persons with general data (people_id, name, address ...). In addition, you have a "engineers" table that contains information specific to engineers (id_engineer, people_id, business name, school ...). We will assume that your base is clean and that all engineers are present in the "people" table.

Now you want to retrieve all the information about engineers, including address, name ... so you need information stored in both tables!
The criterion will be "people_id" as it is unique and present in both tables.

Therefore your quety will be like this:
SELECT *  
FROM people p 
     INNER JOIN engineers i ON i.people_id = p.people_id;

It then retrieves all the information present in both tables!

OUTER JOIN: union of tables


The OUTER JOIN is useful if you want to retreive all the data from one of the two tables. In the example above, we recovered a portion of the table "people" (those having a corresponding line in the engineers table).
In this case we will specify what we want to keep the in the "people" table: we will have the "engineers" data for engineers, and empty values ??when there are no related data. For this, we use shall use an OUTER JOIN.

LEFT OUTER JOIN


The LEFT OUTER JOIN specifies that it is the first table that must remain full.
In our example, this becomes:

SELECT * 
FROM people p 
       LEFT OUTER JOIN engineers i ON i.people_id = p.people_id;

So, with this query you have all the "people" engineers completed with data from the "engineers" table.

RIGHT OUTER JOIN


This is the same as the LEFT OUTER JOIN, but this is the 2nd table that will remain full. Y
SELECT *  
FROM engineers i  
     RIGHT OUTER JOIN people p ON p.people_id = i.people_id;

FULL OUTER JOIN


The FULL OUTER JOIN retrieves all the data from both tables.
Here we have:
SELECT *  
FROM people p 
     FULL OUTER JOIN engineer i ON i.people_id = p.people_id;

Related :

This document entitled « MySQL - How to choose inner or outer joins ? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.