Selecting Columns from Joined Tables

The Wide Virtual Table

Picking Your Columns

When you INNER JOIN two tables, SQL temporarily creates a single, wide 'virtual table' containing every column from both sources. In a real database, this could mean dozens of columns!

To keep your results clean and efficient, you must learn how to SELECT only the specific data points you need from this combined pool.

Welcome back! Now that you know how to link tables, let's learn how to choose exactly what data to display. Think of a JOIN as creating a temporary, giant table. To manage this, we use SELECT to pick only the columns we actually want to see.

• JOIN results in a combined virtual table.
• Selecting specific columns improves performance.
• Tables are linked via keys but viewed as one unit during the query.

Why Select Specific Columns?

Efficiency Over Clutter

In previous lessons, we used SELECT * to grab everything. While easy, this is rarely used in professional environments. In this lesson, we learn to pick exactly what we need using dot notation.

Welcome! While using SELECT ALL is convenient, professional SQL developers almost always specify exactly which columns they need. Compare these two result sets: on the left, a cluttered mess from a SELECT ALL query, and on the right, the clean, targeted data we actually want. By selecting only Movie.Title and Rating.Description, we reduce the load on the database and make our output much easier to read.

• SELECT * is inefficient for large databases
• Selecting specific columns improves query speed
• Results are more readable when limited to relevant data

Dot Notation and Ambiguity

Resolving Ambiguity

When multiple tables are involved, SQL needs to know exactly where a column comes from. We use dot notation: TableName.ColumnName.

• Clarity: Makes code readable.
• Error Prevention: Avoids the Ambiguous Column Name error when two tables have columns with the same name.

Imagine both our tables had a column named 'Code'. If you just typed 'SELECT Code', SQL wouldn't know which one you meant! To fix this, we use dot notation. By writing 'Movie.RatingCode' or 'Rating.Code', we provide an explicit address for the data. Notice how the period sits directly between the table and the column. No spaces allowed!

• Use TableName.ColumnName syntax.
• Dot notation is required when column names overlap.
• It is a best practice even for unique column names.

Mastering Dot Notation

The Anatomy of Table.Column

When joining tables, you must tell SQL exactly where a column lives. We use dot notation: TableName.ColumnName.

• Movie.Title
• Rating.Description

To pick columns from different tables, we use dot notation. Think of it like a mailing address: the table is the city, and the column is the street. Then, add the dot to separate the location from the data. First, you write the name of the table, like Movie. Finally, specify the column name, like Title. This tells SQL exactly where to look.

• The table name comes before the dot
• The column name comes after the dot
• This is standard practice even if names are unique

Practical Syntax Walkthrough

Combining Titles and Descriptions

Let's look at a real-world example. We want to see the Movie.Title alongside the Rating.Description (e.g., 'PG' becomes 'Parental Guidance Suggested').

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

Here is the full syntax. First, we list our columns in the SELECT clause, each prefixed by its table name. Next, we define our source table. Then, we bring in the Rating table and set the matching condition. This ensures every title is paired with its correct description.

• SELECT list uses dot notation for every column.
• FROM identifies the primary table.
• INNER JOIN identifies the secondary table.
• ON defines the relationship.

Resolving Ambiguity

The Ambiguous Column Error

What happens if both the Movie table and the Rating table have a column named ID? Without dot notation, SQL won't know which one you want, resulting in an ambiguous column error.

Imagine both our tables have an ID column. If we just ask for SELECT ID, SQL gets confused. By specifying Movie.ID or Rating.Code, we clear up the confusion immediately. This is the 'Ambiguous Column' error. It's like asking for 'John' in a room full of people named John.

• Ambiguity occurs when column names overlap
• Dot notation resolves the conflict
• It's a best practice to use prefixes even when not strictly required

The Full Query Structure

Putting It All Together

Let's look at the full syntax for a targeted join. We want Title from Movie and Description from Rating.

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

In the SELECT clause, we list our specific columns, separated by a comma. The FROM and INNER JOIN clauses establish which tables we are connecting. Finally, the ON clause uses dot notation to match the primary and foreign keys. Here is our complete query. Notice how we use dot notation in two places: the SELECT clause and the ON clause.

• Separate multiple columns with commas
• The SELECT clause uses dot notation
• The ON clause also uses dot notation to link keys

Exercise: Building the Query

Your Task

Complete the query to retrieve the Title and Year from the Movie table, and the Description from the Rating table.

It's your turn to practice. Fill in the blanks with the correct table names to complete this query. Remember to use the exact table names: Movie or Rating. Great job! You correctly identified that Title and Year belong to the Movie table, while Description comes from Rating.

• Identify which table owns each column.
• Apply correct dot notation syntax.
• Maintain proper clause order.

Build the Targeted Query

Exercise

Complete the query to retrieve the Title and Year from the Movie table, along with the Description from the Rating table.

Excellent! You've correctly used dot notation to specify columns from both tables. Now it's your turn. Fill in the missing table prefixes to complete this query. Remember to use the exact table names: Movie and Rating.

• Prefix every column with its table name
• Use commas correctly

Final Challenge: Full Query Construction

Final Assessment

Write a complete SQL query to join Movie and Rating. Select the Title from Movie and the Description from Rating. Use Movie.RatingCode and Rating.Code for your join condition.

For your final challenge, write the entire query from scratch. Ensure you use UPPERCASE for keywords and dot notation for your columns.

• Write a full INNER JOIN query.
• Apply dot notation to all selected columns.
• Correctly map foreign and primary keys.

Debug the Query

Diagnostic Challenge

A junior analyst wrote the query below, but it's returning an error. Diagnose the problem in 1-2 sentences.

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

Look at this query carefully. While it might work if column names are unique, why is it considered poor practice or potentially broken? Type your diagnosis and submit.

• Identifying missing dot notation
• Understanding professional SQL best practices