Writing INNER JOIN Queries

Why We JOIN Tables

The Need for Connection

In professional databases, information is organized into multiple tables to avoid redundancy. To see a movie's Title alongside its full Description, we need to bridge the gap between the Movie and Rating tables.

Welcome! In professional databases, information is often spread across multiple tables to stay organized. Instead of repeating long descriptions, the Movie table uses a short RatingCode. To see the title 'Frozen' next to 'Parental Guidance Suggested', we must JOIN these tables together.

• Data is often spread across multiple tables for organization
• JOINs allow us to combine related rows
• The Movie table contains codes, while the Rating table contains descriptions

Why We Use INNER JOIN

Connecting the Dots

In a relational database, data is often split to prevent repetition. Our Movie table stores a RatingCode, but the full human-readable Description lives in the Rating table.

An INNER JOIN acts as a bridge, combining rows only when a match is found in both tables.

Welcome to the world of relational data! To keep things organized, we store movie titles in one table and rating descriptions in another. Notice how both tables share a common link: the rating code. An INNER JOIN lets us pull these pieces together into one clear report.

• Relational databases split data across tables for organization.
• INNER JOIN retrieves the 'overlap' between two tables.
• Matches are based on specific shared columns.

The Logic of INNER JOIN

The 'Bridge' Concept

An INNER JOIN acts like a bridge. It only returns rows where there is a matching value in both tables.

• If a match exists, the row is included.
• If no match exists, the row is excluded from the results.

Think of the relationship between our tables as a bridge. The RatingCode in the Movie table matches the Code in the Rating table. An INNER JOIN only keeps the rows where this connection is successful. If a movie has a code that doesn't exist in the Rating table, it won't appear in your results.

• INNER JOIN requires matching values
• It acts as a logical bridge between tables
• Unmatched rows are discarded in the final result

The INNER JOIN Syntax

Building the Query

To join tables, we use the INNER JOIN keyword followed by an ON clause.

SELECT Movie.Title, Rating.Description
FROM Movie
INNER JOIN Rating
ON Movie.RatingCode = Rating.Code;

Let's break down the syntax. We start with our standard SELECT and FROM. Then, we add the INNER JOIN keyword followed by the second table name. Finally, the ON clause tells SQL exactly how these tables are connected.

• The FROM clause specifies the first table.
• INNER JOIN specifies the second table.
• The ON clause defines the relationship.

Syntax Breakdown: Dot Notation

The Language of JOINs

To write a join, we use dot notation (Table.Column). This tells SQL exactly which table a column belongs to, preventing any confusion.

To write a join, you follow a specific order. First, we SELECT the columns we want using dot notation, like Movie.Title. Next, we name our primary table in the FROM clause. Then, we use INNER JOIN to name the table we want to attach. Finally, the ON clause defines the match: Movie.RatingCode equals Rating.Code.

• SELECT: Choose columns from both tables
• FROM: Identify the first table
• INNER JOIN: Identify the second table
• ON: Define the matching logic

Mastering Dot Notation

The 'Dot' Notation

When working with multiple tables, SQL needs to know exactly which table a column belongs to. We use Table.Column notation to be precise.

• Movie.RatingCode: The RatingCode column inside the Movie table.
• Rating.Code: The Code column inside the Rating table.

As requested by our lead architect, let's look closely at the dot notation. Movie-dot-RatingCode tells SQL to look inside the Movie table for the code. Rating-dot-Code points to the specific key in the Rating table. The equals sign between them creates the bridge.

• Dot notation prevents ambiguity.
• Format: [TableName].[ColumnName]
• Essential for the ON clause.

Practice: Identifying the Bridge

Identify the correct columns to link the Movie and Rating tables based on our schema.

Look at these two table schemas. To join them effectively, which two columns should we set equal to each other in our ON clause? Drag the matching columns into the bridge. Correct! By matching Movie.RatingCode with Rating.Code, SQL can find the right description for every film.

• Matching primary and foreign keys.
• Applying dot notation logic.

Practice: Building the Bridge

Write Your First JOIN

Help us find the Title and Description for all movies released in 2023. Complete the query below.

It's your turn. Look at the query and fill in the missing pieces to connect our tables and filter for the year 2023.

• Correct clause ordering
• Applying dot notation
• Filtering with JOINs

Common Pitfalls to Avoid

Stay Error-Free

Even pros make mistakes! Watch out for these three common errors when joining tables.

• Forgetting the ON clause
• Typing errors in table names
• Missing the dot in dot notation

Let's look at what can go wrong. Without an ON clause, the database doesn't know how to pair the rows. Typing 'Movies' instead of 'Movie' will cause a 'Table Not Found' error. And remember, always use the dot to clearly state: 'Look in the Movie table for this column'.

• The ON clause is mandatory for a JOIN
• Table names must be exact (e.g., Movie vs Movies)
• Dot notation prevents column ambiguity

Common Pitfalls

Watch Out for Errors

Two common mistakes can break your JOIN queries:

• Missing the ON clause: SQL won't know how to pair the rows.
• Ambiguous Columns: If both tables have an 'ID' column, you must use dot notation (e.g., Movie.ID) or SQL will get confused.

Even pros make mistakes. If you forget the ON clause, the database gets lost. And if two tables share a column name like 'ID', you must be specific. Without the table prefix, SQL won't know which ID you're asking for.

• Always include an ON clause.
• Use dot notation for columns that exist in both tables.

Diagnostic: Fix the Query

Fix the Outage

A developer wrote the query below, but it's returning an error. Type the corrected line for the ON clause to fix the connection.

This query is broken. Look at the ON clause and type the correct version using dot notation to link RatingCode and Code.

• Identifying dot notation errors
• Matching primary and foreign keys

Final Exercise: The Movie Report

Final Challenge

Write a query that retrieves the Title from the Movie table and the Description from the Rating table.

It's time to put it all together. Write the full SQL query to generate a report showing every movie title alongside its content description. Remember your dot notation!

• Full query construction.
• Applying INNER JOIN and ON logic.