Mastering and Understanding the Join Function in SQL

Joins are one of the most frequently used functions in SQL, and that is for good reason. They play an important role in relational databases by enabling you to combine data from multiple tables, which is often necessary for data analysis and retrieval.

The JOIN function is highly dynamic, and it offers a range of varieties based on your specific join needs. Let’s take a closer look at the different type of join’s, and how they work.

Here are the different types of the JOINs in SQL:

• LEFT (OUTER) JOIN: The LEFT JOIN retrieves all records from the table on the left side and the matched records from the table on the right side. If there’s no match, it still includes the left table’s records, but the right table’s columns will be filled with NULL values for those rows.
• INNER JOIN: This type of join in SQL combines records from two tables based on a common attribute, providing only the records where there is a match in both tables.
• RIGHT (OUTER) JOIN: The RIGHT JOIN retrieves all records from the table on the right side and the matched records from the table on the left side. Similarly, if there’s no match, it includes the right table’s records with NULL values in the left table’s columns.
• FULL (OUTER) JOIN: The FULL JOIN retrieves all records when there is a match in either the left or the right table. It combines all rows from both tables, filling in missing values with NULL where there’s no match.

Understanding the function

The syntax for a SQL join is quite straight forward. We start by specifying the columns with the associated table we want to retrieve in the result set. We then perform a join operation on the specified table. Lastly, we use the ON clause to define the key between the tables.

-- Select the columns you want
SELECT 
    column1, 
    column2, 
    ...
    
-- From the first table
FROM 
    table1
    
-- Join it with the second table
JOIN 
    table2
    
-- On a specified condition
ON 
    table1.column_name = table2.column_name;

Practical examples

For the following examples, we will be using these two tables (Customer & Revenue) to showcase how the different join types operate. Both of these tables share the customerId as join key.

Left (OUTER) join

Let’s start with a left join. In a left outer join, we keep all the records from the left side of the join and include any matched records from the right side. This means that if a record on the left side doesn’t have a match on the right side, it will still be included in the result along with any pairs of records that do have matches between the left and right sides.

SELECT 
    Customers.customerId, 
    Customers.last_name,
    Customers.first_name,
    Revenue.totalRevenue
FROM 
    Customers
LEFT JOIN 
    Revenue 
ON 
    Customers.customerId = Revenue.customerId;

This is how the output looks like performing a left join on the specified tables. The highlighted rows show all the unmatched rows included from the left.

Inner join

Inner join only outputs the records where there are a match on both left and right side. All records from both left and right that does not match the other side will be left out.

SELECT 
    Customers.customerId, 
    Customers.last_name,
    Customers.first_name,
    Revenue.totalRevenue
FROM 
    Customers
INNER JOIN 
    Revenue 
ON 
    Customers.customerId = Revenue.customerId;

This is how the output looks like performing a inner join on the specified tables. Only the matched records are included in the output.

Right (OUTER) join

A right outer join, also known as a right join, keeps all the records from the right side of the join and including any matching records from the left side. In this type of join, if a record on the right side doesn’t find a match on the left side, it will still appear in the result.

SELECT 
    Customers.customerId, 
    Customers.last_name,
    Customers.first_name,
    Revenue.totalRevenue
FROM 
    Customers
RIGHT JOIN 
    Revenue 
ON 
    Customers.customerId = Revenue.customerId;

This is how the output looks like performing a left join on the specified tables. The highlighted rows shows all the unmatched records included from the right.

Full (OUTER) join

The full outer join retrieves all records when there is a match in either the left or the right table. It combines all rows from both tables, filling in missing values with NULL where there’s no match.

SELECT 
    Customers.customerId, 
    Customers.last_name,
    Customers.first_name,
    Revenue.totalRevenue
FROM 
    Customers
FULL JOIN 
    Revenue 
ON 
    Customers.customerId = Revenue.customerId;

This is how the output looks like performing a outer join on the specified tables. All records are output, both matched and unmatched records.

There are many ways to apply joins in different scenarios, but this will give you the basic understanding of how the JOIN function in SQL operates. Happy data wrangling!