MattCASmith

My next post on SQL takes a look at JOINs, which allow you to compare and merge two tables in various ways and determine the relationship between two sets of data. Here’s what INNER, LEFT, RIGHT, and FULL OUTER JOINs are and how to use them.

Having gotten to grips with the construction of basic SQL queries, the next challenge that arose in my work was to merge two SQL tables based on records and values that match. In SQL, this is done with the very useful JOIN command, which has a few variants – INNER, LEFT, RIGHT, and OUTER – that return different combinations of records from the first, second, and both tables, and merge them differently.

To illustrate this, I’ve set up two tables. The first, Positions, contains a list of team IDs and their 2017/18 Premier League finishing positions. The second, Teams, contains a list of team IDs and the teams’ names. To mix things up, there are some positions in Positions that do not have corresponding teams in Teams – seventh, eighth, and ninth – and some non-Premier League teams in Teams that do not have positions in Positions – Portsmouth, Millwall, and Norwich.

INNER JOIN

A SQL INNER JOIN returns all the records in the two tables where the values in a column match. In this case, I’m matching on the team_id column, which is the only column the two tables have in common.

SELECT Teams.team_id, team_name, position FROM Teams
INNER JOIN Positions ON Teams.team_id = Positions.team_id
ORDER BY position ASC

In our example, this returns a list of the team IDs that match across the two tables with their corresponding names and positions. The positions that don’t have matching teams (seventh, eighth, ninth) and teams that do not have matching positions (Portsmouth, Millwall, Norwich) are excluded.

LEFT JOIN

A SQL LEFT JOIN returns all the records from your first table, and all the records that match from the second. In this example, this means we get the full list of teams, but only the positions that match.

SELECT Teams.team_id, team_name, position FROM Teams
LEFT JOIN Positions ON Teams.team_id = Positions.team_id
ORDER BY position ASC

This returns the full list of team_ids from Teams with corresponding data from Positions added where appropriate. This means Portsmouth, Millwall, and Norwich are included without positions, but the additional positions (seventh, eighth, and ninth) are left out.

RIGHT JOIN

A SQL RIGHT JOIN does just the opposite, returning all the records from your second table, and all the records that match from the first. This means the full list of positions will be returned.

SELECT Teams.team_id, team_name, position FROM Teams
RIGHT JOIN Positions ON Teams.team_id = Positions.team_id
ORDER BY position ASC</pre>

This gives us the full list of positions from Positions, including seventh, eighth, and ninth, which have no teams. Meanwhile, Portsmouth, Millwall, and Norwich – which have no positions – are excluded.

FULL OUTER JOIN

A SQL FULL OUTER JOIN will return all records in both of the tables, merging them were appropriate.

SELECT Teams.team_id, team_name, position FROM Teams
FULL OUTER JOIN Positions ON Teams.team_id = Positions.team_id
ORDER BY position ASC

Here we get the full list of teams – even those without positions – and the full list of positions – even those without corresponding teams. Every record in both tables is represented in the output.

SQL JOINs can be very useful when establishing the relationships between two or more large data sets – particularly when merging two tables of related data from two different sources. Learning what each JOIN does and how to use it effectively can be a valuable tool to have in your SQL arsenal.

Photo from rawpixel.com on Pexels