SQL Joins: 12 Practice Questions with Detailed Answers

In this article, we dig into our SQL JOINS course and give you 12 join exercises to solve. But don’t worry – all the exercises have solutions and explanations. If you get stuck, help is there! This is, after all, made for practicing and learning. 

SQL joins can be tricky. It’s not just the syntax, but also knowing what joins to use in what scenarios.

Joins are used when combining data from two or more tables in SQL. The tables can be joined in several ways, and, depending on the tables, each way of joining them can result in a completely different result.  There’s no other way to learn this than practice. Yes, you can read explanations and typical uses of SQL joins. That helps, for sure! But practice builds on that through problem-solving and repetition, which makes your knowledge stick. The more you practice, the greater the possibility that the real-life data problems you’ll have to solve will be similar or completely the same as what you’ve already done!

And practice is what we’ll do in this article! We’ll show you exercises for basic and more advanced SQL joins uses. If you like them, you’ll enjoy our SQL JOINs course even more, as all the exercises are taken from there. In total, the course offers you 93 SQL joins exercises. They cover topics ranging from the types of joins in SQL, to filtering data, joining more than two tables, self-joining a table, and using non-equi joins.

OK, so let’s introduce the datasets and start exercising, shall we? Feel free to help yourself with the SQL JOIN Cheat Sheet as you go.

List of Exercises

Here's a list of all exercises in the article:

• Exercise 1: List All Books and Their Authors
• Exercise 2: List Authors and Books Published After 2005
• Exercise 3: Show Books Adapted Within 4 Years and Rated Lower Than the Adaptation
• Exercise 4: Show All Books and Their Adaptations (If Any)
• Exercise 5: Show All Books and Their Movie Adaptations
• Exercise 6: Show All Books with Their Reviews (If Any)
• Exercise 7: List All the Books and All the Authors
• Exercise 8: Show Products Under 150 Calories and Their Department
• Exercise 9: List All Products with Their Producers, Departments, and Carbs
• Exercise 10: Show All the Products, Prices, Producers, and Departments
• Exercise 11: List All Workers and Their Direct Supervisors
• Exercise 12: Show Cars with Higher Mileage Than a Specific Car

INNER JOIN

INNER JOIN is a type of SQL join that returns only the matching rows from the joined tables.

To show you how this works, we’ll use Dataset 1 from the course.

Dataset 1

The dataset consists of four tables: author, book, adaptation, and book_review.

The first table shows the author data in the following columns:

• id – The author’s unique ID within the database.
• name – The author’s name.
• birth_year – The year when that author was born.
• death_year – The year when that author died (the field is empty if they are still alive).

Here are the table’s first few rows:

The second table, book,  shows details about books. The columns are:

• id – The ID of a given book.
• author_id – The ID of the author who wrote that book.
• title – The book’s title.
• publish_year – The year when the book was published.
• publishing_house – The name of the publishing house that printed the book.
• rating – The average rating for the book.

These are the first five rows:

The adaptation table has the following columns:

• book_id – The ID of the adapted book.
• type – The type of adaptation (e.g. movie, game, play, musical).
• title – The name of this adaptation.
• release_year – The year when the adaptation was created.
• rating – The average rating for the adaptation.

Here’s a snapshot of the data from this table:

The final table is book_review. It consists of the following columns:

• book_id - The ID of a reviewed book.
• review - The summary of the review.
• author - The name of the review's author.

Here’s the data:

Exercise 1: List All Books and Their Authors

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published.

Solution:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id;

Solution explanation: The query selects the name of the author, the book title, and its publishing year. This is data from the two tables: author and book. We are able to access both tables by using INNER JOIN. It returns only rows with matching values (values that satisfy the join condition) from both tables.

We first reference the table author in the FROM clause. Then we add the JOIN clause (which can also be written as INNER JOIN in SQL) and reference the table book.

The tables are joined on the common column. In this case, it's id from the table author and author_id from the table book. We want to join the rows where these columns share the same value. We do that using the ON clause and specifying the column names. We also put the table name before each column so the database knows where to look. That’s primarily because there’s an id column in both tables, but we want the id column only from the author table. By referencing the table name, the database will know from which table we need that column.

Solution output:

Here’s the output snapshot. We got all this data by joining two tables:

Exercise 2: List Authors and Books Published After 2005

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published. Show only books published after 2005.

Solution:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id
WHERE publish_year > 2005;

Solution explanation: This exercise and its solution are almost the same as the previous one. This is reflected by the query selecting the same columns and joining the tables in the same way as earlier.

The difference is that the exercise now asks us to show only books published after 2005. This requires filtering the output; we do that using the WHERE clause.

WHERE is a clause that accepts conditions used to filter out the data. It is written after joining the tables. In our example, we filter by referencing the column publish_year after WHERE and using the comparison operator ‘greater than’ (>) to find the years after 2005.

Solution output:

The output shows only one book published after 2005.

Exercise 3: Show Books Adapted Within 4 Years and Rated Lower Than the Adaptation

Exercise: For each book, show its title, adaptation title, adaptation year, and publication year.

Include only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released within four years of the book’s publication.

Rename the title column from the book table to book_title and the title column from the adaptation table to adaptation_title.

Solution:

SELECT
  book.title AS book_title,
  adaptation.title AS adaptation_title,
  book.publish_year,
  adaptation.release_year
FROM book
JOIN adaptation
  ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
  AND book.rating < adaptation.rating;

Solution explanation: Let’s start explaining the solution from the FROM and JOIN clauses. The columns we need to show are from the tables book and adaptation. We reference the first table in FROM and the second in JOIN.

In the ON clause, we equal the two book ID columns and specify the table of each column. This is the same as earlier, only with different table and column names.

Now, we need to select the required columns. The thing here is there’s a title column in both tables. To avoid ambiguity, a best practice is to reference the table name before each column in the SELECT.

Note: The above is mandatory only for ambiguous columns. However, it’s a good idea to do that with all columns; it improves code readability and the approach remains consistent.

After selecting the columns, we need to rename some of them. We do that using the keyword AS and writing a new column name afterward. That way, one title column becomes book_title, the other becomes adaptation_title. Giving aliases to the column names also helps get rid of ambiguity.

Now we need to filter the output. The first condition is that the adaptation had to be released four years or less after the book. We again use WHERE and simply deduct the book publish year from the adaptation release year. Then we say that the difference has to be less than or equal to (<=) 4.

We also need to add the second condition, where the book has a lower rating than the adaptation. It’s simple! The question implies that both the first and the second conditions have to be satisfied. The clue is in AND, a logical operator we use for adding the second condition. Here, it uses the ‘less than’ (<) operator to compare the two ratings.

Solution output:

The output shows three book–adaptation pairs that satisfy the conditions.

LEFT JOIN

Now that you get the gist of INNER JOIN, let’s move on to LEFT JOIN. It’s a type of outer join that returns all the columns from the left (the first) table and only the matching rows from the right (the second) table. If there is non-matching data, it’s shown as NULL.

You can learn more in our article about LEFT JOIN.

Exercise 4: Show All Books and Their Adaptations (If Any)

Exercise: Show the title of each book together with the title of its adaptation and the date of the release. Show all books, regardless of whether they had adaptations.

Solution:

SELECT
  book.title,
  adaptation.title,
  adaptation.release_year
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id;

Solution explanation: We first select the required columns from the two tables. Then we join book (the left table) with adaptation (the right table) using LEFT JOIN. You see that the SQL join syntax is the same for INNER JOIN. The only thing that changes is the join keyword.

Note: SQL accepts both LEFT JOIN and LEFT OUTER JOIN. They are the same command.

Solution output:

The output snapshot shows the required data, with some of the data shown as NULL. These are the books without the adaptation.

Exercise 5: Show All Books and Their Movie Adaptations

Exercise: Show all books with their movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation. Keep the books with no adaptations in the result.

Solution:

SELECT
  book.title,
  publishing_house,
  adaptation.title,
  adaptation.type
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id
WHERE type = 'movie'
  OR type IS NULL;

Solution explanation:

The question asks to show all the rows, even those without any adaptations. It’s possible that there are books without adaptations, so we use LEFT JOIN.

We first select the book title, its publishing house, its adaptation title, and its type.

Then we join book (the left table) with adaptation (the right table) using LEFT JOIN. We join the tables on the book ID. All the books that don’t satisfy the conditions will have NULLs as an adaptation title and type.

We filter data using WHERE. The first condition is that the adaptation type has to be a movie, so we equal the type column with a movie using the equal sign (=).  Note: When using text data in the WHERE condition, it must be enclosed in single quotes ('').

The second filtering condition is added using the logical operator OR. It says that the type can also be NULL if it’s not a movie. The exercise asks us to keep books with no adaptations in the results.

Solution output:

Here’s the output snapshot. You can see that it shows only books adapted as movies or not adapted at all.

RIGHT JOIN

Where there’s LEFT JOIN, there’s also RIGHT JOIN, right? Despite being the LEFT JOIN's mirror image, it’s still a part of the SQL joins practice.

It’s a type of join that returns all the columns from the right (the second) table and only the matching rows from the left (the first) table. If there is non-matching data, it’s shown as NULL.

Exercise 6: Show All Books with Their Reviews (If Any)

Exercise: Join the book_review and book tables using a RIGHT JOIN. Show the title of the book, the corresponding review, and the name of the review's author. Consider all books, even those that weren't reviewed.

Solution:

SELECT
  book.title,
  book_review.review,
  book_review.author
FROM book_review
RIGHT JOIN book
  ON book.id = book_review.book_id;

Solution explanation:

We first select the required columns. Then we do as we’re told: join the tables using RIGHT JOIN. We join the tables on the book ID. The table book is the right table; we want all the data from it, regardless of the reviews.

As you can see, the syntax stays the same as in INNER JOIN and LEFT JOIN.

Note: SQL accepts both RIGHT JOIN and RIGHT OUTER JOIN.

Solution output:

The query returns all the book titles, their reviews, and authors. Where there’s no review or author information, a NULL is shown.

FULL JOIN

Here’s another join type that’s useful in some scenarios: the FULL JOIN . This is a LEFT JOIN and RIGHT JOIN put together. It shows matching rows from both tables, rows that have no match from the left table, and rows that have no match from the right table. In short, it shows all data from both tables.

You can read more about how and when to use FULL JOIN.

Exercise 7: List All the Books and All the Authors

Exercise: Display the title of each book along with the name of its author. Show all books, even those without an author. Show all authors, even those who haven't published a book yet. Use a FULL JOIN.

Solution:

SELECT
  title,
  name
FROM book
FULL JOIN author
  ON book.author_id = author.id;

Solution explanation: The question requires showing all books, but also all authors – FULL JOIN is perfect for doing this elegantly.

We select the book title and the author's name. Next, we FULL JOIN the table book with the table author. The joining condition is that the author ID has to be the same in both tables. Again, the syntax is the same as in all the previous join types.

Note: SQL accepts both FULL JOIN and FULL OUTER JOIN.

Solution output:

The output shows all the books and all the authors, whether the authors or books exist in both tables or not.

Joining 3 or More Tables

Yes, SQL joins allow for joining more than two tables. We’ll see how to do that in this part of the SQL joins practice. You can find a more detailed explanation of multiple joins here.

We also need a new dataset, so let’s introduce it.

Dataset 2

The first table in the dataset is department. Its columns are:

• id – The unique ID of the department.
• name – The department name, i.e. where a particular type of product is sold.

Here’s the data from the table.

The second table is product, and it consists of the following columns:

• id – The ID of a given product.
• name – The product’s name.
• department_id – The ID of the department where the product is located.
• shelf_id – The ID of the shelf of that department where the product is located.
• producer_id – The ID of the company that manufactures this product.
• price – The product’s price.

Here’s the data snapshot:

The next table is nutrition_data. Its columns and data are given below:

• product_id – The ID of a product.
• calories – The calorific value of that product.
• fat – The amount of fat in that product.
• carbohydrate – The amount of carbohydrates in that product.
• protein – The amount of protein in that product.

The fourth table is named producer. It has the following columns:

• id – The ID of a given food producer.
• name – The name of the producer.

Below is the data from this table:

The last table in the dataset is sales_history. It has the following columns:

• date – The date of sale.
• product_id – The ID of the product sold.
• amount – The amount of that product sold on a particular day.

Here’s the data, too:

Exercise 8: Show Products Under 150 Calories and Their Department

Exercise: List all products that have fewer than 150 calories. For each product, show its name (rename the column product) and the name of the department where it can be found (name the column department).

Solution:

SELECT
  p.name AS product,
  d.name AS department
FROM department d
JOIN product p
  ON d.id = p.department_id
JOIN nutrition_data nd
  ON nd.product_id = p.id
WHERE nd.calories < 150;

Solution explanation: The general principle of how you join the third (fourth, fifth…) table is that you simply add another JOIN. You can see how it’s done in this article explaining multiple joins. We’ll do it the same way here.

We first join the department table with the product table on the department ID using JOIN. But we also need the third table. To get the data from it, we just add another JOIN, which will join the product table with the nutrition_data table. The syntax is the same as with the first join. In this case, the query joins the tables on the product ID.

Then we use WHERE to find products with fewer than 150 calories. We finally select the product and department names and rename the columns as per the exercise instructions.

Note: You probably noticed both selected columns have the same original name. And you also noticed we solved this ambiguity by putting some strange short table names in front of all the columns in the query. These shortened names are table aliases, which you give by simply writing them after the table name in FROM or JOIN. By giving aliases to the tables, you can shorten the tables’ names. Therefore, you don’t have to write their full names (sometimes they can be really long!), but the short aliases instead. This saves time and space.

Solution output:

The output shows a list of the products and the department they belong to. It includes only those products with fewer than 150 calories.

Exercise 9: List All Products with Their Producers, Departments, and Carbs

Exercise: For each product, display the:

• Name of the company that produced it (name the column producer_name).
• Name of the department where the product is located (name it department_name).
• Product name (name it product_name).
• Total number of carbohydrates in the product.

Your query should still consider products with no information about producer_id or department_id.

Solution:

SELECT
  prod.name AS producer_name,
  d.name AS department_name,
  p.name AS product_name,
  nd.carbohydrate
FROM product p
LEFT JOIN producer prod
  ON prod.id = p.producer_id
LEFT JOIN department d
  ON d.id = p.department_id
LEFT JOIN nutrition_data nd
  ON nd.product_id = p.id;

Solution explanation: The query selects the required columns. Then it joins the table product with the table producer on the producer ID using LEFT JOIN. We choose this type of join because we have to include products without producer data.

Then we add another LEFT JOIN. This one adds the department table and joins it with the product table. Again, we choose LEFT JOIN because we need to show products that don’t have a department.

There’s also a third join! We simply add it to the chain of the previous joins. It’s again LEFT JOIN, as we add the nutrition_data table and join it with the product table.

This is an interesting topic to explore, so here’s an article that explains multiple LEFT JOINs to help you with it.

Solution output:

The output shows all the products with their producer and department names and carbohydrate amounts:

If you need more details, please read how to LEFT JOIN multiple tables in SQL.

Exercise 10: Show All the Products, Prices, Producers, and Departments

Exercise: For each product, show its name, price, producer name, and department name.

Alias the columns as product_name, product_price, producer_name, and department_name, respectively. Include all the products, even those without a producer or department. Also, include the producers and departments without a product.

Solution:

SELECT
  p.name AS product_name,
  p.price AS product_price,
  prod.name AS producer_name,
  d.name AS department_name
FROM product p
FULL JOIN producer prod
  ON p.producer_id = prod.id
FULL JOIN department d
  ON d.id = p.department_id;

Solution explanation: This exercise requires using FULL JOIN, as we need all the data from the tables we’ll use: product, producer, and department.

The syntax is the same as in the previous examples. We just join the different tables (product and producer) on the producer ID and use a different type of join:  FULL JOIN.

The second FULL JOIN joins the product table with the department table.

After selecting the required columns and renaming them, we get the following output.

Solution output:

The solution shows all the data from the selected tables and columns:

Self-Join

A self-join is not a distinct type of SQL JOIN – any join can be used for self-joining a table. It’s simply a join used to join the table with itself. By giving different aliases to the same table, it’s treated as two different tables when self-joined.

For more details, check out our illustrated guide to the SQL self-join.

Dataset 3

The dataset for this example consists of only one table: workshop_workers. It has the following columns.

• id – The worker’s ID.
• name – The worker’s first and last name.
• specialization – The worker's specialization.
• master_id – The ID of the worker's supervisor.
• experience – The worker's years of experience.
• project_id – The ID of the project to which the worker is currently assigned.

Here’s the data:

Exercise 11: List All Workers and Their Direct Supervisors

Exercise: Show all workers' names together with the names of their direct supervisors. Rename the columns  apprentice_name and master_name, respectively. Consider only workers who have a supervisor (i.e. a master).

Solution:

SELECT
  apprentice.name AS apprentice_name,
  master.name AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
  ON apprentice.master_id = master.id;

Solution explanation: Let’s start with explaining the self-join. The general principle is the same as with regular joins. We reference the table in FROM and give it an alias, apprentice. Then we use JOIN and reference the same table in it. This time, we give the table the alias master. We’re basically pretending that one table has the apprentice data and the other has the master data.

The tables are joined on the master ID from the apprentice table and the ID from the master table.

This example is a typical use of a self-join: the table has a column (master_id) that references another column from the same table (id). Both columns show the worker’s ID. When there’s NULL in master_id, it means that the worker doesn’t have a master. In other words, they are the master.

After self-joining, we simply select the required columns and rename them.

Solution output:

The output shows all the apprentices and their direct supervisors.

Non-Equi Joins

The final topic we’ll tackle in this SQL joins practice are non-equi joins. The joins we used so far are called equi-joins because they use the equality sign (=) in the joining condition. Non-equi are all other joins that use any other operators – comparison operators (<, >, <=, >=, !=, <>), the BETWEEN operator, or any other logical condition – to join tables.

Dataset 4

We’ll use the dataset consisting of two tables. The first table is car. Here are its columns:

• id – The car’s ID in the database.
• model – The car’s model.
• brand – The car’s brand.
• original_price – The original price of that car when new.
• mileage – The car’s total mileage.
• prod_year – The car’s production year.

The data looks like this:

The second table is charity_auction with these columns:

• car_id – The car’s ID.
• initial_price – The car’s initial (i.e. starting) price.
• final_price – The actual price when the car was sold.
• buyer_id – The ID of the person who bought the car.

Here’s the data:

Exercise 12: Show Cars with Higher Mileage Than a Specific Car

Exercise: Show the model, brand, and final price of each car sold at the auction. Consider only those sold cars that have more mileage than the car with the id = 4.

Solution:

SELECT
  car.model,
  car.brand,
  car.final_price
FROM car
JOIN charity_auction ca
  ON car.id = ca.car_id
JOIN car car2
  ON car.mileage > car2.mileage
WHERE car2.id = 4
  AND final_price IS NOT NULL;

Solution explanation: We select the car model, brand, and final price.

In the first JOIN, we join the car table with the charity_auction table. The tables are joined where the car IDs are the same. This is our regular equi JOIN.

We add the second JOIN, which is a self-join. It adds the table car again, so we can filter the data using the non-equi join condition. The condition will return all the cars from the car table and all the cars from the car2 table with the lower mileage. This is a non-equi condition as it uses the ‘greater than’ ( > ) operator. The syntax is the same, but there’s > instead of = this time.

Finally, we need to filter data using WHERE. We’re not interested in comparing the mileage of all cars. We want to show the cars that have a mileage higher than the car with id = 4. This is what the first filtering condition does.

We add another filtering condition that says the final price shouldn’t be NULL, i.e., the car has to have been sold in the auction.

Solution output:

The result shows two cars:

SQL JOINs Practice Makes Perfect. More Practice? Perfect-er!

Twelve SQL join exercises is a solid amount of practice. Through these exercises, you could learn and practice all the most common join topics that trouble beginner and intermediate users.

Now, you just need to keep going! When you practice even more, you become even perfect-er. So if you liked our exercises, you can get more of the same in our SQL JOINS course or the article about the SQL JOIN interview questions.

Hope you ace all the exercises that await you there!