INNER JOIN with table aliases in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of using table aliases in an INNER JOIN?

Table aliases give shorter or easier names to tables in a query. This makes the query simpler to write and read, especially when joining multiple tables.

beginner

Write a simple INNER JOIN query using table aliases for tables employees and departments.

SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

beginner

In the query SELECT e.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;, what do e and d represent?

They are aliases for the tables <code>employees</code> and <code>departments</code> respectively. They let you refer to these tables with shorter names.

intermediate

Why is it important to use aliases when joining tables that have columns with the same name?

Aliases help specify which table a column belongs to, avoiding confusion and errors when columns have the same name in different tables.

intermediate

Can you use any name as a table alias? Are there any rules?

Yes, you can use almost any name as an alias, but it should not be a reserved SQL keyword and should be easy to understand. It must follow SQL naming rules (no spaces, start with a letter, etc.).

What does the alias e represent in this query?
SELECT e.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

A The departments table

B The employees table

C A column name

D A function

The alias e is used for the employees table to simplify the query.

Why do we use INNER JOIN in SQL?

A To combine rows from two tables where there is a match in both

B To select all rows from the first table only

C To delete rows from a table

D To update rows in a table

INNER JOIN returns rows where there is a matching value in both tables.

Which of the following is a correct way to alias a table named orders?

A orders AS *

B orders AS 123

C orders AS o

D orders AS select

Aliases must be valid names, so o is valid, but numbers or reserved keywords like select are not.

In an INNER JOIN with aliases, how do you refer to the id column from the second table aliased as d?

A d.id

B id.d

C d->id

D id

You use the alias followed by a dot and the column name to specify the column from that table.

What happens if you don’t use aliases in a query joining two tables with columns of the same name?

A The query will run fine without issues

B The columns will be ignored

C The database will rename columns automatically

D The query will fail or be ambiguous

Without aliases, the database cannot tell which column you mean if names are the same, causing errors.

Explain how to write an INNER JOIN query using table aliases and why aliases are helpful.

Think about how you can shorten table names and why that helps.

Describe a situation where using table aliases in an INNER JOIN is necessary.

Imagine two tables both have a column named 'id'.

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