INNER JOIN with table aliases in SQL - Step-by-Step Execution

Concept Flow - INNER JOIN with table aliases

Start with two tables

↓

Assign aliases to tables

↓

Write INNER JOIN using aliases

↓

Match rows where join condition is true

↓

Combine matched rows into result

↓

Return joined rows as output

We start with two tables, give them short names (aliases), then join rows where a condition matches, producing combined rows.

Execution Sample

SQL

SELECT a.id, a.name, b.order_id
FROM customers AS a
INNER JOIN orders AS b ON a.id = b.customer_id;

This query joins customers and orders tables using aliases 'a' and 'b' to match customer IDs.

Execution Table

Step	Action	Table Alias	Row from customers (a)	Row from orders (b)	Join Condition (a.id = b.customer_id)	Output Row
1	Check first row of customers	a	{id:1, name:'Alice'}
2	Check first row of orders	b		{order_id:101, customer_id:1}	1 = 1 (True)	{id:1, name:'Alice', order_id:101}
3	Check second row of orders	b		{order_id:102, customer_id:2}	1 = 2 (False)
4	Check second row of customers	a	{id:2, name:'Bob'}
5	Check first row of orders	b		{order_id:101, customer_id:1}	2 = 1 (False)
6	Check second row of orders	b		{order_id:102, customer_id:2}	2 = 2 (True)	{id:2, name:'Bob', order_id:102}
7	No more rows in customers

💡 All rows checked; join condition matched only when a.id equals b.customer_id

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
a (customer row)	none	{id:1, name:'Alice'}	{id:2, name:'Bob'}	none (end)
b (order row)	none	{order_id:101, customer_id:1}	{order_id:102, customer_id:2}	none (end)
Output Rows	empty	[{id:1, name:'Alice', order_id:101}]	[{id:1, name:'Alice', order_id:101}, {id:2, name:'Bob', order_id:102}]	final two joined rows

Key Moments - 2 Insights

Why do we use aliases like 'a' and 'b' instead of full table names?

Aliases make the query shorter and easier to read, especially when joining tables. In the execution_table, you see 'a' and 'b' used to refer to customers and orders rows.

What happens if the join condition is false for some rows?

Rows that do not meet the join condition are skipped and not included in the output. For example, in steps 3 and 5, the condition is false, so no output row is created.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What is the output row?

A {id:1, name:'Alice', order_id:102}

B {id:1, name:'Alice', order_id:101}

C {id:2, name:'Bob', order_id:101}

D No output row

💡 Hint

Check the 'Output Row' column in step 2 of the execution_table.

At which step does the join condition first become false?

A Step 3

B Step 5

C Step 2

D Step 6

💡 Hint

Look at the 'Join Condition' column in the execution_table rows.

If we changed the join condition to a.id = b.order_id, how would the output change?

A Only rows with matching IDs would join as before

B All rows would join

C No rows would join because customer IDs don't match order IDs

D Query would error out

💡 Hint

Compare the join condition column and think about matching values.

Concept Snapshot

INNER JOIN combines rows from two tables where a condition matches.
Use aliases (short names) to simplify table references.
Syntax: FROM table1 AS a INNER JOIN table2 AS b ON a.col = b.col
Only rows matching the condition appear in the result.
Aliases help avoid repeating long table names.

Full Transcript

This visual execution shows how INNER JOIN works with table aliases. We start with two tables, customers and orders, and give them short names 'a' and 'b'. The query joins rows where a.id equals b.customer_id. Step by step, each row from customers is checked against each row from orders. When the join condition is true, the rows combine into one output row. Rows that don't match are skipped. The output includes only matched rows. Using aliases makes the query easier to write and read. The execution table tracks each step, showing which rows are compared and when output rows are created.

Practice

1. What does an INNER JOIN with table aliases do in SQL?

easy

A. Deletes rows from both tables using aliases.

B. Updates rows in one table based on another without aliases.

C. Creates a new table without any conditions.

D. Combines rows from two tables where the join condition matches, using short names for tables.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows where matching keys exist in both tables.

2. Step 2: Role of table aliases

   Aliases are short names to simplify table references in queries.

3. Final Answer:

   Combines rows from two tables where the join condition matches, using short names for tables. -> Option D

4. Quick Check:

   INNER JOIN + aliases = matched rows with short table names [OK]

Hint: INNER JOIN matches rows; aliases shorten table names [OK]

Common Mistakes:

• Confusing INNER JOIN with DELETE or UPDATE
• Thinking aliases create new tables
• Ignoring the join condition in INNER JOIN

2. Which of the following is the correct syntax for an INNER JOIN with table aliases?

easy

A. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id;

B. SELECT a.name, b.salary FROM employees AS a JOIN salaries b WHERE a.id = b.emp_id;

C. SELECT a.name, b.salary FROM employees a INNER JOIN salaries b USING a.id = b.emp_id;

D. SELECT a.name, b.salary FROM employees a JOIN salaries b ON a.id == b.emp_id;

Solution

1. Step 1: Check INNER JOIN syntax

   Correct syntax uses INNER JOIN with ON clause for join condition.

2. Step 2: Validate alias usage and condition

   Aliases 'a' and 'b' are used correctly; ON clause uses single '=' for comparison.

3. Final Answer:

   SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.id = b.emp_id; -> Option A

4. Quick Check:

   INNER JOIN + ON + aliases = correct syntax [OK]

Hint: Use ON with single = for join condition [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals '==' in SQL
• Incorrect USING clause syntax

3. Given tables students (id, name) and grades (student_id, grade), what is the output of this query?

SELECT s.name, g.grade FROM students s INNER JOIN grades g ON s.id = g.student_id;

medium

A. Syntax error due to missing alias

B. [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}]

C. [{"student_id": 1, "grade": "A"}]

D. [{"name": "Alice"}, {"grade": "A"}]

Solution

1. Step 1: Understand the join condition

   The query joins students and grades where students.id matches grades.student_id.

2. Step 2: Predict output rows

   Only students with matching grades appear, showing their name and grade as pairs.

3. Final Answer:

   [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "B"}] -> Option B

4. Quick Check:

   INNER JOIN returns matched student names with grades [OK]

Hint: INNER JOIN shows matched rows with selected columns [OK]

Common Mistakes:

• Expecting unmatched rows to appear
• Confusing alias names in SELECT
• Assuming syntax error without cause

4. Identify the error in this SQL query using INNER JOIN with aliases:

SELECT e.name, d.department FROM employees e INNER JOIN departments d ON e.dept_id = d.idd;

medium

A. Alias 'e' is not defined.

B. Missing AS keyword for aliases.

C. Column 'idd' does not exist in departments table.

D. INNER JOIN should be LEFT JOIN.

Solution

1. Step 1: Check alias definitions

   Aliases 'e' and 'd' are correctly defined for employees and departments.

2. Step 2: Verify join condition columns

   Column 'idd' in departments does not exist; likely a typo for 'id'.

3. Final Answer:

   Column 'idd' does not exist in departments table. -> Option C

4. Quick Check:

   Incorrect column name in ON clause causes error [OK]

Hint: Check column names in ON clause carefully [OK]

Common Mistakes:

• Assuming missing AS keyword causes error
• Confusing alias definition errors
• Changing join type unnecessarily

5. You have two tables: orders (order_id, customer_id, amount) and customers (cust_id, name). Which query correctly uses INNER JOIN with aliases to list customer names and their total order amounts, grouping by customer name?

hard

A. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.name;

C. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY o.name;

Solution

1. Step 1: Match join keys correctly

   Join customers.cust_id with orders.customer_id to link orders to customers.

2. Step 2: Use aliases and group by customer name

   Use aliases 'c' and 'o' and group results by c.name to sum amounts per customer.

3. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c INNER JOIN orders o ON c.cust_id = o.customer_id GROUP BY c.name; -> Option A

4. Quick Check:

   Correct join keys and grouping produce total per customer [OK]

Hint: Join on matching keys, group by customer name [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Grouping by wrong column
• Mixing up alias names