Up to now, we've been working with a single table, but entity data in the real world is often broken down into pieces and stored across multiple orthogonal tables using a process known as normalization 1 ("Database normalization").

Database normalization

Database normalization is useful because it minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other (ie. Types of car engines can grow independent of each type of car). As a trade-off, queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables.

In order to answer questions about an entity that has data spanning multiple tables in a normalized database, we need to learn how to write a query that can combine all that data and pull out exactly the information we need.

Multi-table queries with JOINs

Tables that share information about a single entity need to have a primary key that identifies that entity uniquely across the database. One common primary key type is an auto-incrementing integer (because they are space efficient), but it can also be a string, hashed value, so long as it is unique.

Using the JOIN clause in a query, we can combine row data across two separate tables using this unique key. The first of the joins that we will introduce is the INNER JOIN.

Select query with INNER JOIN on multiple tables

SELECT column, another_table_column, … FROM mytable **INNER JOIN another_table ON mytable.id = another_table.id** WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

The INNER JOIN is a process that matches rows from the first table and the second table which have the same key (as defined by the ON constraint) to create a result row with the combined columns from both tables. After the tables are joined, the other clauses we learned previously are then applied.

Did you know?

You might see queries where the INNER JOIN is written simply as a JOIN. These two are equivalent, but we will continue to refer to these joins as inner-joins because they make the query easier to read once you start using other types of joins, which will be introduced in the following lesson.

Exercise

We've added a new table to the Pixar database so that you can try practicing some joins. The Box Office table stores information about the ratings and sales of each particular Pixar movie, and the Movie_id column in that table corresponds with the Id column in the Movies table 1-to-1. Try and solve the tasks below using the INNER JOIN introduced above.

1. Find the domestic and international sales for each movie
	1. SELECT Title, Domestic_sales, International_sales FROM Movies M INNER JOIN Boxoffice B ON M.Id = B.Movie_Id;
2. Show the sales numbers for each movie that did better internationally rather than domestically
	1. SELECT Title, Domestic_sales, International_sales FROM Movies M INNER JOIN Boxoffice B ON M.Id = B.Movie_Id WHERE Domestic_sales < International_sales;
3. List all the movies by their ratings in descending order
	1. SELECT Title, Domestic_sales, International_sales FROM Movies M INNER JOIN Boxoffice B ON M.Id = B.Movie_Id ORDER BY Rating DESC;

Exercise 6 — Tasks

1. Find the domestic and international sales for each movie ✓
2. Show the sales numbers for each movie that did better internationally rather than domestically ✓
3. List all the movies by their ratings in descending order ✓

The current SQLBolt lesson covers how to perform multi-table queries using JOINs in SQL, focusing on data that is split across multiple normalized tables. Key concepts introduced include:

• Database normalization helps reduce duplicate data and organizes related data into separate tables, but requires more complex queries to retrieve combined information.
• Primary keys uniquely identify each row in a table and are essential for linking related data across tables.
• The INNER JOIN clause allows you to combine rows from different tables based on a shared key, returning only the rows where the key matches.

This lesson provides exercises using two tables, Movies and Box Office, which are related by a movie ID:

• The Movies table includes information like the movie title, director, year, and length.
• The Box Office table contains ratings and sales data for each movie.

Exercise tasks include:

• Finding domestic and international sales for each movie.
• Showing movies where international sales are higher than domestic sales.
• Listing all movies ordered by their rating in descending order.

The lesson features interactive SQL tasks that let you practice writing JOIN queries to answer questions that require information from both tables.[1]

1