The Concept of Joining Tables

Why Split Data?

Organizing Information

In a relational database, we often split data into different tables to maintain efficiency and avoid redundancy. Instead of repeating a rating's full description for every movie, we store it once in a Rating table and link to it from the Movie table.

Welcome to Module 3. To understand how databases work, we first need to look at how they stay organized. Imagine if we wrote the full text 'Parental Guidance Suggested' next to every single PG movie in our database. That would be a lot of repeated work! Instead, we split the data into two tables: <code>Movie</code> and <code>Rating</code>. A <span class='highlight'>JOIN</span> is the magic operation that brings these pieces back together when we need to see the full picture, like matching 'Toy Story' with its 'General Audiences' description.

The Problem of Redundancy

In a relational database, we use normalization to keep data organized. Instead of repeating long descriptions like 'Parents Strongly Cautioned' for every PG-13 movie, we store it once in a separate table.

Welcome to Module 3. Imagine a giant spreadsheet where every single PG-13 movie repeats the same long rating description. This is inefficient! In a relational database, we split this into two tables: Movie and Rating. This process is called normalization. By splitting the data, we only store the description once. But now, we need a way to bring that information back together when we want to see it.

The Key Connection

Primary and Foreign Keys

To combine tables, the database looks for a common link. The RatingCode in the Movie table acts as a Foreign Key, pointing to the Primary Key (Code) in the Rating table.

To join these tables, the database needs a common link. Look at the <code>Movie</code> table's <code>RatingCode</code> column. This is our Foreign Key. It points directly to the <code>Code</code> column in the <code>Rating</code> table, which is the Primary Key. When the database sees a 'G' in the Movie table, it looks for the matching 'G' in the Rating table. This match is the 'glue' that allows the data to merge.

Visualizing the Connection

Tables connect like puzzle pieces using a Foreign Key. In our database, the RatingCode in the Movie table points to the Code in the Rating table.

To link these tables, we use shared values. Look at the RatingCode column in the Movie table. This is our Foreign Key. It points directly to the Code column in the Rating table, which acts as the Primary Key. When the database sees 'PG-13' in the Movie table, it knows exactly where to look in the Rating table to find the full description.

The Horizontal Merge

When we perform a JOIN, the database finds the matching values and merges the rows horizontally into a single, wider row.

Think of a JOIN as a horizontal expansion. Here is one row from the Movie table and one from the Rating table. When we JOIN them on the rating code, the database slides them together. Now we have one wide row containing the title and the description.

The Horizontal Merge

Gluing Rows Together

When a match is found, the database performs a horizontal merge. It 'glues' the matching rows together to create a single, wider row containing columns from both tables.

Watch what happens when we perform the JOIN. The database takes a row from the <code>Movie</code> table and finds its partner in the <code>Rating</code> table. They slide together horizontally, creating a new, wider row that contains the ID, Title, and the full Description.

Introduction to JOIN Syntax

In SQL, we use the JOIN keyword to name the second table and the ON keyword to define the matching logic.

To write this in SQL, we start with our standard SELECT and FROM. Then, we add the JOIN keyword followed by the table we want to attach. Finally, we use ON to tell the database which columns must match.

The Database Logic

How the Database Thinks

Follow the steps to see how the database engine processes a JOIN. Click each step to simulate the logic.

Let's step into the mind of the database. Click 'Step 1' to begin the process for our first movie. First, the engine looks at the first row of the <code>Movie</code> table. It sees 'Toy Story' with a <code>RatingCode</code> of 'G'. Next, it jumps over to the <code>Rating</code> table. It searches the <code>Code</code> column for a matching 'G'. A match is found! The engine now 'glues' all columns from both tables into one wide result row.

Practice: Joining Movie Data

Complete the query to combine the Movie table with the Rating table to see movies rated 'R'.

Now it's your turn. Complete the SQL query by filling in the missing keywords to join our tables and find the 'R' rated movies. Type your answer and click submit.

Identify the Connection

Find the 'Glue'

Look at the tables below. Click on the Foreign Key value in the Movie table that connects 'Jaws' to its rating description.

Time for a quick check. Look at the movie 'Jaws'. Click the specific cell in the <code>Movie</code> table that acts as the pointer to the <code>Rating</code> table. Not quite. Remember, we are looking for the 'pointer'—the code that appears in both tables to link them together. Correct! The 'PG' value in the <code>RatingCode</code> column is the Foreign Key that links 'Jaws' to the 'Parental Guidance' description.