SQL Joins: Working with Databases

SQL joins don’t have to be this challenging!

When first learning SQL, it’s common to work with data in a single table. In the real world, databases generally have data in more than one table. If we want to be able to work with that data, we’ll have to combine multiple tables within a query. In this SQL joins post, we’ll learn how to use joins to select data from multiple tables.

We’ll assume that you know the fundamentals of working in SQL including filtering, sorting, aggregation, and subqueries. If you don’t, our SQL Fundamentals course teaches all of these concepts, and you can sign up and start that course for free.

The Factbook Database

We're going to be using a version of the CIA World Factbook (Factbook) database that has two tables. The first table is called facts, and each row represents a country from the Factbook. Here are the first 5 rows of the facts table:

In addition to the facts table is a second table called cities which contains information on major urban areas from countries in the Factbook (for the rest of this post, we'll use the word 'cities' to mean the same as 'major urban areas'). Let's take a look at the first few rows of this table and a description of what each column represents:

• id - A unique ID for each city.
• name - The name of the city.
• population - The population of the city.
• capital - Whether the city is a capital city: 1 if it is, 0 if it isn't.
• facts_id - The ID of the country, from the facts table.

The last column is of particular interest to us, as it is a column of data that also exists in our original facts table. This link between tables is important as it's used to combine the data in our queries. Below is a schema diagram, which shows the two tables in our database, the columns within them and how the two are linked.

The line in the schema diagram clearly shows the link between the id column in the facts table and the facts_id column in the cities table.

If you'd like to download the database to follow along on your own computer you can download the dataset as a SQLite database.

Our First SQL Join

The most common way to join data using SQL is using an inner join. The syntax for an inner join is:

SELECT [column_names] FROM [table_name_one]
INNER JOIN [table_name_two] ON [join_constraint];

The inner join clause is made up of two parts:

• INNER JOIN, which tells the SQL engine the name of the table you wish to join in your query, and that you wish to use an inner join.
• ON, which tells the SQL engine what columns to use to join the two tables.

Joins are usually used in a query after the FROM clause. Let's look at a basic inner join where we combine the data from both of our tables.

SELECT * FROM facts
INNER JOIN cities ON cities.facts_id = facts.id
LIMIT 5;

Let's look at the line of the query with the join in it:

• INNER JOIN cities: This tells the SQL engine that we wish to join the cities table to our query using an inner join.
• ON cities.facts_id = facts.id: This tells the SQL engine which columns to use when joining the data, following the syntax table_name.column_name.

You might presume that SELECT * FROM facts will mean that the query returns only columns from the facts table, however the * wildcard when used with a join will give you all columns from both tables. Here is the result of this query:

This query gives us all columns from both tables and every row where there is a match between the id column from facts and the facts_id from cities, limited to the first 5 rows.

Understanding SQL Inner Joins

We've now joined the two tables to give us extra information about each row in cities. Let's take a closer look at how this inner join works.

An inner join works by including only rows from each table that have a match as specified using the ON clause. Let's look at a diagram of how our join from the previous screen works. We have included a selection of rows which best illustrate the join:

Our inner join will include:

• Rows from the cities table that have a cities.facts_id that matches a facts.id from facts.

Our inner join will not include:

• Rows from the cities table that have a cities.facts_id that don't match any facts.id from facts.
• Rows from the facts table that have a facts.id that don't match any cities.facts_id from cities.

You can see this represented as a Venn diagram:

We already know how to use aliases to specify custom names for columns, eg:

SELECT AVG(population) AS average_population

We can also create aliases for table names, which makes queries with joins easier to both read and write. Instead of:

SELECT * FROM facts
INNER JOIN cities ON cities.facts_id = facts.id

We can write:

SELECT * FROM facts AS f
INNER JOIN cities AS c ON c.facts_id = f.id

Just like with column names, using AS is optional. We can get the same result by writing:

SELECT * FROM facts f
INNER JOIN cities c ON c.facts_id = f.id

We can also combine aliases with wildcards - for instance, using the aliases created above, c.* would give us all columns from the table cities.

While our query from the previous screen included both columns from the ON clause, we don't need to use either column from our ON clause in our final list of columns. This is useful as it means we can show only the information we're interested in, rather than having to include the two join columns every time.

Let's use what we've learned to build on our original query. We'll:

• Join cities to facts using an INNER JOIN.
• Use aliases for table names.
• Include, in order:
  • All columns from cities.
  • The name column from facts aliased to country_name.
• Include only the first 5 rows.

SELECT
    c.*,
    f.name country_name
FROM facts f
INNER JOIN cities c ON c.facts_id = f.id
LIMIT 5;

Practicing Inner Joins in SQL

Let's practice writing a query to answer a question from our database using an inner join. Say we want to produce a table of countries and their capital cities from our database using what we've learned so far. Our first step is to think about what columns we'll need in our final query. We'll need:

• The name column from facts
• The name column from cities

Given that we've identified that we need data from two tables, we need to think about how to join them. The schema diagram from earlier indicated that there is only one column in each table that links them together, so we can use an inner join with those columns to join the data.

So far, thinking through our question we can already write most of our query (it's almost identical to the previous query we wrote):

SELECT
    f.name,
    c.name
FROM cities c
INNER JOIN facts f ON f.id = c.facts_id;

The last part of our process is to make sure we have the correct rows. From the previous two screens we know that a query like this will return all rows from cities that have a corresponding match from facts in the facts_id column. We're only interested in the capital cities from the cities table, so we'll need to use a WHERE clause on the capital column, which has a value of 1 if the city is a capital, and 0 if it isn't:

WHERE c.capital = 1

We can now put this all together to write a query that answers our question. We'll limit it to just the first 10 rows so the amount of output is managable.

SELECT
    f.name country,
    c.name capital_city
FROM cities c
INNER JOIN facts f ON f.id = c.facts_id
WHERE c.capital = 1
LIMIT 10;

Left Joins in SQL

As we mentioned earlier, an inner join will not include any rows where there is not a mutual match from both tables. This means there could be information we are not seeing in our query where rows don't match.

We can use SQL queries to explore this:

SELECT COUNT(DISTINCT(name)) FROM facts;

count

261

SELECT COUNT(DISTINCT(facts_id)) FROM cities;

count

210

By running these two queries, we can see that there are some countries in the facts table that don't have corresponding cities in the cities table, which indicates we may have some incomplete data.

Let's look at how we can create a query to explore the missing data using a new type of join— the left join.

A left join includes all the rows that an inner join will select, plus any rows from the first (or left) table that don't have a match in the second table. We can see this represented as a Venn diagram.

Let's look at an example by replacing INNER JOIN with LEFT JOIN from the first query we wrote, and looking at the same selection of rows from our earlier diagram

SELECT * FROM facts
LEFT JOIN cities ON cities.facts_id = facts.id

Here we can see that for the rows where facts.id doesn't match any values in cities.facts_id (237, 238, 240, and 244), the rows are still included in the results. When this happens, all of the columns from the cities table are populated with null values.

We can use these null values to filter our results to just the countries that don't exist in cities with a WHERE clause. When making a comparison to null in SQL, we use the IS keyword, rather than the = sign. If we want to select rows where a column is null we can write:

WHERE column_name IS NULL

If we want to select rows where a column name isn't null, we use:

WHERE column_name IS NOT NULL

Let's use a left join to explore the countries that don't exist in the cities table.

SELECT
    f.name country,
    f.population
FROM facts f
LEFT JOIN cities c ON c.facts_id = f.id
WHERE c.name IS NULL;

Looking through the results of the query we wrote in the previous screen, we can see a number of different reasons that countries don't have corresponding values in cities:

• Countries with small populations and/or no major urban areas (which are defined as having populations of over 750,000), eg San Marino, Kosovo, and Nauru.
• City-states, such as Monaco and Singapore.
• Territories that are not themselves countries, such as Hong Kong, Gibraltar, and the Cook Islands.
• Regions & Oceans that aren't countries, such as the European Union and the Pacific Ocean.
• Genuine cases of missing data, such as Taiwan.

It's important whenever you use inner joins to be mindful that you might be excluding important data, especially if you are joining based on columns that aren't linked in the database schema.

Right Joins and Outer Joins

There are two less-common join types SQLite does not support that you should be aware of. The first is a right join. A right join, as the name indicates, is exactly the opposite of a left join. While the left join includes all rows in the table before the JOIN clause, the right join includes all rows in the new table in the JOIN clause. We can see a right join in the Venn diagram below:

The following two queries, one using a left join and one using a right join, produce identical results.

SELECT f.name country, c.name city
FROM facts f
LEFT JOIN cities c ON c.facts_id = f.id
LIMIT 5;

SELECT f.name country, c.name city
FROM cities c
RIGHT JOIN facts f ON f.id = c.facts_id
LIMIT 5;

The main reason a right join would be used is when you are joining more than two tables. In these cases, using a right join is preferable because it can avoid restructuring your whole query to join one table. Outside of this, right joins are used reasonably rarely, so for simple joins it's better to use a left join than a right as it will be easier for your query to be read and understood by others.

The other join type not supported by SQLite is a full outer join. A full outer join will include all rows from the tables on both sides of the join. We can see a full outer join in the Venn diagram below:

Like right joins, full outer joins are reasonably uncommon. The standard SQL syntax for a full outer join is:

SELECT f.name country, c.name city
FROM cities c
FULL OUTER JOIN facts f ON f.id = c.facts_id
LIMIT 5;

When joining cities and facts with a full outer join, the result will be be the same as our left and right joins above, because there are no values in cities.facts_id that don't exist in facts.id.

Let's look at the Venn diagrams of each join type side by side, which should help you compare the differences of each of the four joins we've discussed so far.

Next, let's practice using joins to answer some questions about our data.

Finding the Most Populous Capital Cities

Previously, we've used column names when specifying order for our query results, like so:

SELECT
    name,
    migration_rate
FROM facts
ORDER BY migration_rate desc;

There is a handy shortcut we can use in our queries which lets us skip the column names, and instead use the order in which the columns appear in the SELECT clause. In this instance, migration_rate is the second column in our SELECT clause so we can just use 2 instead of the column name:

SELECT
    name,
    migration_rate
FROM facts
ORDER BY 2 desc;

You can use this shortcut in either the ORDER BY or GROUP BY clauses. Be mindful that you want to ensure your queries are still readable, so typing the full column name may be better for more complex queries.

Let's use what we've learned to produce a list of the top 10 capital cities by population. Because we are not interested in countries from facts that don't have corresponding cities in cities, we should use an INNER JOIN.

SELECT
    c.name capital_city,
    f.name country,
    c.population
FROM facts f
INNER JOIN cities c ON c.facts_id = f.id
WHERE c.capital = 1
ORDER BY 3 DESC
LIMIT 10;

Combining SQL Joins with Subqueries

Subqueries can be used to substitute parts of queries, allowing us to find the answers to more complex questions. We can also join to the result of a subquery, just like we could a table.

Here's an example of a using a join and a subquery to produce a table of countries and their capital cities, like we did earlier in the lesson.

Reading subqueries can be overwhelming at first, so we'll break down what happens in this example in several steps. The important thing to remember is that the result of any subquery is always calculated first, so we read from the inside out.

• The subquery, in the red box, is calculated first. This simple query selects all columns from cities, filtering rows that are marked as capital cities by having a value for capital of 1.
• The INNER JOIN joins the subquery result, aliased as c, to the facts table based on the ON clause.
• Two columns are selected from the results of the join:
  • f.name, aliased as country.
  • c.name, aliased as capital_city.
• The results are limited to the first 10 rows.

Below is the output of this query:

Using this example as a model, we'll write a similar query to find non-capital cities with populations of over 10 million.

SELECT
    c.name city,
    f.name country,
    c.population population
FROM facts f
INNER JOIN (
            SELECT * FROM cities
            WHERE capital = 0
            AND population > 10000000
           ) c ON c.facts_id = f.id
ORDER BY 3 DESC;

SQL Challenge: Complex Query with Joins and Subqueries

Let's take everything we've learned before and use it to write a more complex query. It's not uncommon to find that 'thinking in SQL' takes a bit of getting used to, so don't be discouraged if this query seems difficult to understand at first. It will get easier with practice!

When you're writing complex queries with joins and subqueries, it helps to follow this process:

• Think about what data you need in your final output
• Work out which tables you'll need to join, and whether you will need to join to a subquery.
  • If you need to join to a subquery, write the subquery first.
• Then start writing your SELECT clause, followed by the join and any other clauses you will need.
• Don't be afraid to write your query in steps, running it as you go— for instance you can run your subquery as a 'stand alone' query first to make sure it looks like you want before writing the outer query.

We will be writing a query to find the countries where the urban center (city) population is more than half of the country's total population. There are multiple ways to write this query but we'll step through one approach.

We can start by writing a query to sum all the urban populations for cities in each country. We can do this without a join by grouping on the facts_id (we'll use a limit in our example below to make the output managable):

SELECT
    facts_id,
    SUM(population) urban_pop
FROM cities
GROUP BY 1
LIMIT 5;

Next, we'll join the facts table to that subquery, selecting the country name, urban population and total population (again, we've used a limit to keep things tidy):

SELECT
    f.name country,
    c.urban_pop,
    f.population total_pop
FROM facts f
INNER JOIN (
            SELECT
                facts_id,
                SUM(population) urban_pop
            FROM cities
            GROUP BY 1
           ) c ON c.facts_id = f.id
LIMIT 5;

Lastly, we'll create a new column that divides the urban population by the total population, and use a WHERE and ORDER BY to filter/rank the results:

SELECT
    f.name country,
    c.urban_pop,
    f.population total_pop
FROM facts f
INNER JOIN (
            SELECT
                facts_id,
                SUM(population) urban_pop
            FROM cities
            GROUP BY 1
           ) c ON c.facts_id = f.id
LIMIT 5;

You can see that while our final query is complex, it's much easier to understand if you build it step-by-step.

SQL Joins: Next Steps

In this post dedicated to SQL joins, we learned:

• The difference between inner and left joins.
• The role of right and outer joins
• How to choose which join is appropriate for your task.
• Using joins with subqueries, aggregate functions, and other SQL techniques.

Other resources that might interest you include our Complete Guide to SQL, SQL Cheat Sheet, our article on SQL certification, our rundown of SQL interview questions for job interviews, and of course our interactive SQL courses. Click below to sign up and get started for free!