Select Distinct Logo Clear Background

Business Analytics Blog

Joins in SQL

Joins in SQL: a simple guide

Joins in SQL: A simplified guide

Introduction to joins in SQL

This guide is aimed at explaining in simple terms the different types of joins between tables in SQL

We use simplified real life examples combining data from Products, Stores, Sales and Inventory

What we will not do unlike other guides we have seen is attempt to explain joins using Venn diagrams. In my experience trying to explain joins using venn diagrams is not helpful and tends to confuse people even more than before

Joins are a vital way to combine data from multiple tables into one coherent data set for analysis

Good ‘normalised’ database design would tend to store data as efficiently as possible, for example a Sales table would likely contain separate IDs for each part of a transaction such as a ProductID, StoreID and a Customer ID. This saves a huge amount of duplication of data as the IDs can be efficiently stored as integer values and then joins can be used to look up the relevant details for each store, product or customer

Here is a simplified representation of a sales table, we can see a record for each store and product combination what the quantity sold has been. But from a reporting point of view we need to know what the products and the stores are

A simplified example of a sales fact table

A very simplified store dimension table would like this, with one row representing each store

A simple store dimension table

Products would also have a similar structure (although there would typically be lots more details in separate columns!

A simple product dimension table

To create a report that brings these three data tables together we need to use a join

You can follow along these examples by running the SQL
SQL Joins Examples

There are four main types of join between tables, here we will start with the simplest one

The first type of join is call an inner join

instead of using a convoluted Venn diagram to confuse everyone, we will start with a typical question

What were the sales in the Aberdeen store?

To answer this we run the following query

select Store_Name, Product_Name, qty_sold
from sales
join stores on sales.store_ID = stores.store_ID
join products on sales.product_ID = products.product_ID
where sales.store_ID = 1

We now get a much more useful dataset that can be easily understood

A list of sales by product in Aberdeen

This is an example of a standard join, also called an INNER JOIN

an inner join returns only records that have a match in both tables

To explain this clearly, we know that there are 10 products in the products table, but only 9 had sales. The Aberdeen store sold no fish, so there is no matching entry in the sales table, so the row is excluded

The next type of join is a LEFT JOIN

The next type of join we will examine is called a left join, these can also be called a left outer join

If you were asked the question “Can I see all products and what they sales they have (if any)”

Then straight away we know that some of the products had no sales in Aberdeen

In this instance we will start with all products

select * from products

This gives us a list of all of our products (including the fish!)

If we add the left join (but limited to just sales for Aberdeen)

select * from products
left join sales on sales.product_ID = products.product_ID and sales.store_ID = 1

We can see all 10 products, but with a null row for the fish

An example of a left join in SQL


A right join is similar, but in reverse

A typical question here would be something like, “can I see all Aberdeen sales and any products they haven’t sold too”

select a.*, products.*
select * from Sales where sales.store_ID = 1
) a

right join products on a.product_ID = products.product_ID

Here we start with the Aberdeen sales inside the sub query we have called ‘a’, then a right join adds everything from the products table to this matching where it can and adding rows with null values for the missing sales data

An example of a Right Join in SQL


A full join is the final type of join, a full join returns all records whether there is a match in either table. These are sometimes called a full outer join

“Can I see all items in Aberdeen that have either sales or stock”

We know that not all items have sales, and some may have no stock despite having had some sales

A full join provides us with this

select * from
select * from inventory where inventory.store_id = 1
) i
full join
select * from Sales where sales.store_ID = 1
) s on s.product_ID = i.product_ID

An example of a full join in SQL


Understanding the four main types of join and when you might use them is a key SQL skill to learn

Seeing the examples and being able to follow along should reinforce that knowledge, download the sample SQL script to create your own tables and follow along

Much easier to understand than a Venn diagram

Subscribe to our channel to see more SQL tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work and are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert