SQL JOINS

3rd May 2017

When using SQL JOINS within a query, you are combining rows from multiple tables into one result set based on the tables having a related column name.

The most common example of using SQL JOIN queries can be demonstrated by using a customers table and an orders table stored in the same database.

Let’s assume that the customers table looks like the following.

The order table could look like the below.

Each table contains a primary key column: customer_id for the customers table, and order_id for the orders table. Both tables have a related customer_id column.

With this foreign key relation, you can use join queries to retrieve a set of information from both tables.

A basic JOIN query using the above tables might look like the below.

SELECT o.customer_id, c.first_name, c.last_name, o.order_total 
FROM orders o
JOIN customers c
ON c.customer_id = o.customer_id;

This retrieves the following data.

Different Types of JOIN

By default, INNER JOIN is the default type of JOIN if you don’t specify the type when you use JOIN in the query.

This means that all records are retrieved that are common between both tables.

There is also a RIGHT JOIN. This gets all records from the RIGHT linked table. If you have selected some columns from the LEFT table and there are no related records, these columns will contain NULL.

Using the example query above, as all of the data in both tables is linked (i.e. both customers added to the customers table have placed at least one order in the orders table), using RIGHT JOIN will yield the same results as JOIN and INNER JOIN.

If however another customer was added to the customers table and they did not place an order, using the RIGHT JOIN query will yield the below results.

SELECT o.customer_id, c.first_name, c.last_name, o.order_total 
FROM orders o
RIGHT JOIN customers c
ON c.customer_id = o.customer_id;

Here the orders table is the LEFT table (as it is selected first), and customers is the RIGHT. Therefore all the records have been selected from the customers table even if the customers had no order records.

LEFT JOIN performs the same functionality as LEFT except with information from the RIGHT linked table.

The FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.