🎓 Velnov

🗄️ SQL · Topic 11 of 20

INNER JOIN — SQL Questions with Answers

30 practice questions on INNER JOIN, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

3 easy · 15 medium · 12 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

JOINRelationshipsINNER JOINMatchingSyntaxONJoin conditionAliasesQualified namesCROSS JOINCartesian productMulti-table joinsSelf joinHierarchiesCommutativityUSINGJoin syntaxNATURAL JOINBest practicesComposite keysJoin conditionsGROUP BYFan-outOne-to-many

Jump to: Easy · Medium · Hard

Easy questions (3)

Q1. easy

What does a JOIN do?

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
  1. A Combines rows from two or more tables based on a related column
  2. B Merges two databases
  3. C Sorts a table
  4. D Deletes duplicate rows

Answer: A — Combines rows from two or more tables based on a related column

Joins reunite data that normalisation split across tables - for example matching each order to its customer via customer_id.

JOINRelationships

Q2. easy

What does INNER JOIN return?

  1. A Only rows that have a MATCH in BOTH tables
  2. B All rows from both tables
  3. C All rows from the left table
  4. D Rows with no match

Answer: A — Only rows that have a MATCH in BOTH tables

INNER JOIN keeps only matched pairs. An order with a NULL or non-existent customer_id, or a customer with no orders, is excluded.

INNER JOINMatching

Q3. easy

Is 'JOIN' the same as 'INNER JOIN'?

  1. A Yes - INNER is the default, so plain JOIN means INNER JOIN
  2. B No, JOIN means CROSS JOIN
  3. C No, JOIN means LEFT JOIN
  4. D They are different in every database

Answer: A — Yes - INNER is the default, so plain JOIN means INNER JOIN

Writing INNER is optional but makes the intent explicit. Many style guides recommend spelling it out for clarity.

INNER JOINSyntax

Medium questions (15)

Q4. medium

What does the ON clause specify?

... JOIN customers c ON o.customer_id = c.id
  1. A The condition that matches rows between the tables (the join key)
  2. B Which columns to return
  3. C How to sort the result
  4. D The table name

Answer: A — The condition that matches rows between the tables (the join key)

ON defines how rows relate, usually foreign key = primary key. Without a correct ON condition you get wrong or exploded results.

ONJoin condition

Q5. medium

Why are table aliases (o, c) useful in joins?

  1. A They shorten qualified column names and are required when the same column exists in both tables
  2. B They speed up the query
  3. C They sort the tables
  4. D They are never needed

Answer: A — They shorten qualified column names and are required when the same column exists in both tables

When both tables have an 'id' column, you must qualify it (o.id vs c.id). Short aliases keep long joins readable.

AliasesQualified names

Q7. medium

How do you join THREE tables?

SELECT o.id, c.name, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id;
  1. A Chain JOINs: FROM a JOIN b ON ... JOIN c ON ...
  2. B Use three FROM clauses
  3. C You cannot join more than two
  4. D Nest three SELECTs

Answer: A — Chain JOINs: FROM a JOIN b ON ... JOIN c ON ...

Each additional JOIN adds another table and its ON condition. Order rarely affects results for inner joins, but readability improves when you follow the relationships.

Multi-table joins

Q8. medium

Can you join a table to itself?

SELECT e.name AS emp, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.id;
  1. A Yes - a self join uses two aliases of the same table (e.g. employees to their managers)
  2. B No, that is forbidden
  3. C Only with UNION
  4. D Only for numbers

Answer: A — Yes - a self join uses two aliases of the same table (e.g. employees to their managers)

Self joins model hierarchies and comparisons within one table. Aliases are mandatory to distinguish the two roles. (Covered further in Advanced Joins.)

Self joinHierarchies

Q10. medium

What does USING(customer_id) do?

  1. A A shorthand for ON when both tables share the same column name - it also merges the column in output
  2. B Selects a column
  3. C Creates an index
  4. D Filters rows

Answer: A — A shorthand for ON when both tables share the same column name - it also merges the column in output

JOIN customers USING(customer_id) equals ON a.customer_id = b.customer_id. It works only when the join column has an identical name in both tables.

USINGJoin syntax

Q12. medium

Can the ON condition use more than one column?

  1. A Yes - combine with AND for composite keys: ON a.x = b.x AND a.y = b.y
  2. B No, only one column
  3. C Only with USING
  4. D Only two tables allow it

Answer: A — Yes - combine with AND for composite keys: ON a.x = b.x AND a.y = b.y

Composite-key tables (like a junction table) need multi-column ON conditions to match correctly.

Composite keysON

Q14. medium

What does this return? SELECT c.name, COUNT(o.id) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

  1. A Each customer WHO HAS orders, with their order count
  2. B All customers including those with zero orders
  3. C Only customers with no orders
  4. D An error

Answer: A — Each customer WHO HAS orders, with their order count

INNER JOIN excludes customers with no matching orders, so they never appear - even with COUNT. To include zero-order customers you need a LEFT JOIN.

INNER JOINGROUP BY

Q16. medium

How do you select all columns from one table but only some from another?

  1. A SELECT o.*, c.name FROM orders o JOIN customers c ON ...
  2. B SELECT * FROM both
  3. C SELECT o AND c.name
  4. D It is not possible

Answer: A — SELECT o.*, c.name FROM orders o JOIN customers c ON ...

table.* expands to all columns of that table, and you can mix it with individually named columns from others.

SELECTQualified *

Q18. medium

What does joining on the WRONG columns typically produce?

  1. A Wrong matches or a near-Cartesian explosion of rows
  2. B A syntax error always
  3. C An empty result always
  4. D The correct result anyway

Answer: A — Wrong matches or a near-Cartesian explosion of rows

Joining orders.id = customers.id (instead of orders.customer_id = customers.id) matches unrelated rows by coincidence. Always verify the join key represents the real relationship.

Join keysBugs

Q20. medium

What does this find? SELECT p.title FROM products p JOIN order_items i ON p.id = i.product_id;

  1. A Titles of products that appear in at least one order (with duplicates per order line)
  2. B All products
  3. C Products never ordered
  4. D One row

Answer: A — Titles of products that appear in at least one order (with duplicates per order line)

INNER JOIN returns only products with matching order items, repeated once per line item. Add DISTINCT for a unique list of sold products.

INNER JOINDISTINCT

Q22. medium

Can you filter a joined result with WHERE?

... JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'India';
  1. A Yes - WHERE applies to the combined rows after the join
  2. B No, only ON filters joins
  3. C Only for the left table
  4. D Only with HAVING

Answer: A — Yes - WHERE applies to the combined rows after the join

For INNER JOIN, an equality filter behaves the same in ON or WHERE. Use WHERE for conditions on the final result and ON for the matching logic.

WHEREJOIN

Q24. medium

Which columns should typically be indexed to speed up joins?

  1. A The foreign key columns used in ON conditions
  2. B Every text column
  3. C Only the primary key
  4. D None

Answer: A — The foreign key columns used in ON conditions

Primary keys are indexed automatically, but foreign keys often are not. Indexing them is one of the highest-impact tuning steps for join-heavy queries.

IndexesForeign keys

Q26. medium

How do you join and show a computed column from both tables?

  1. A SELECT i.quantity * p.price AS line_total FROM order_items i JOIN products p ON i.product_id = p.id;
  2. B SELECT quantity * price without a join
  3. C SELECT SUM(both tables)
  4. D It cannot be done

Answer: A — SELECT i.quantity * p.price AS line_total FROM order_items i JOIN products p ON i.product_id = p.id;

After joining, columns from both tables are available in the same row, so you can compute expressions like quantity times price.

ExpressionsJOIN

Q28. medium

What does DISTINCT do in a joined query?

  1. A Removes duplicate rows caused by one-to-many fan-out
  2. B Speeds up the join
  3. C Adds a column
  4. D Sorts the result

Answer: A — Removes duplicate rows caused by one-to-many fan-out

When a join repeats parent rows, DISTINCT can collapse them - but it is a blunt tool. Often the better fix is aggregating or selecting only the columns you need.

DISTINCTFan-out

Q30. medium

What is the everyday purpose of INNER JOIN?

  1. A To reassemble related data that normalisation split into separate tables
  2. B To back up tables
  3. C To delete rows
  4. D To rename columns

Answer: A — To reassemble related data that normalisation split into separate tables

Because good design stores customers, orders and products separately, nearly every real report joins them back together to answer business questions.

NormalizationReporting

Hard questions (12)

Q6. hard

What happens if you JOIN two tables but forget the ON clause?

  1. A You get a CROSS JOIN (Cartesian product) - every row of A paired with every row of B
  2. B A syntax error always
  3. C An empty result
  4. D Only matching rows

Answer: A — You get a CROSS JOIN (Cartesian product) - every row of A paired with every row of B

1000 rows joined to 1000 rows with no ON gives 1,000,000 rows. Modern databases often require ON for INNER JOIN, but implicit comma-joins without a WHERE cause this explosion.

  • A classic accidental-Cartesian-product bug.

CROSS JOINCartesian product

Q9. hard

Does the ORDER of tables matter in an INNER JOIN result?

  1. A No - INNER JOIN is symmetric; A JOIN B and B JOIN A return the same rows (columns may be ordered differently)
  2. B Yes, it changes which rows match
  3. C Yes, it changes the count
  4. D It causes an error

Answer: A — No - INNER JOIN is symmetric; A JOIN B and B JOIN A return the same rows (columns may be ordered differently)

The result set is identical. (The optimiser may choose a different execution order for performance, but that is invisible in the output.) This symmetry does NOT hold for outer joins.

INNER JOINCommutativity

Q11. hard

What is a NATURAL JOIN, and why is it risky?

  1. A It auto-joins on ALL columns with matching names - risky because adding a column later can silently change the join
  2. B It joins without any condition
  3. C It is the same as CROSS JOIN
  4. D It only joins numeric columns

Answer: A — It auto-joins on ALL columns with matching names - risky because adding a column later can silently change the join

NATURAL JOIN is convenient but fragile: if both tables gain a 'created_at' column, it suddenly becomes part of the join key. Explicit ON is safer.

NATURAL JOINBest practices

Q13. hard

What extra condition can go in ON besides equality?

  1. A Any boolean condition - ranges, inequalities, or extra filters (e.g. ON a.id = b.a_id AND b.active = 1)
  2. B Only = is allowed
  3. C Only column names
  4. D Nothing

Answer: A — Any boolean condition - ranges, inequalities, or extra filters (e.g. ON a.id = b.a_id AND b.active = 1)

ON accepts any condition. For INNER JOIN, putting a filter in ON or WHERE gives the same result - but for OUTER joins the placement matters greatly (covered next topic).

ONJoin conditions

Q15. hard

Why might an INNER JOIN return MORE rows than the left table has?

  1. A A one-to-many relationship duplicates left rows - one order joined to its many items produces multiple rows
  2. B Joins never add rows
  3. C It is always a bug
  4. D Only with DISTINCT

Answer: A — A one-to-many relationship duplicates left rows - one order joined to its many items produces multiple rows

This 'fan-out' is expected and correct when matching a parent to many children. It becomes a problem only when you then aggregate the parent's columns, inflating the totals.

Fan-outOne-to-many

Q17. hard

What is the effect of an index on the join column?

  1. A It lets the database find matching rows quickly instead of scanning the whole table for each row
  2. B It has no effect on joins
  3. C It slows joins down
  4. D It changes the result

Answer: A — It lets the database find matching rows quickly instead of scanning the whole table for each row

Foreign key columns are prime candidates for indexes. Joining on unindexed columns can force slow nested-loop scans or full table sorts.

IndexesPerformance

Q19. hard

In older syntax, how was an inner join written?

  1. A FROM a, b WHERE a.id = b.a_id - the comma (implicit) join with the condition in WHERE
  2. B FROM a INNER b
  3. C MERGE a WITH b
  4. D It was impossible

Answer: A — FROM a, b WHERE a.id = b.a_id - the comma (implicit) join with the condition in WHERE

The comma form still works but is discouraged: forgetting the WHERE condition silently produces a Cartesian product. Explicit JOIN ... ON keeps the condition attached to the join.

Implicit joinLegacy syntax

Q21. hard

How do you count how many DISTINCT customers placed orders?

  1. A SELECT COUNT(DISTINCT c.id) FROM customers c JOIN orders o ON c.id = o.customer_id;
  2. B SELECT COUNT(*) FROM customers JOIN orders;
  3. C SELECT COUNT(o.id) ...
  4. D SELECT COUNT(c.id) ...

Answer: A — SELECT COUNT(DISTINCT c.id) FROM customers c JOIN orders o ON c.id = o.customer_id;

Because the join fans out one customer to many orders, COUNT(c.id) or COUNT(*) would count orders. DISTINCT collapses each customer to one.

COUNT DISTINCTFan-out

Q23. hard

What does joining a junction table achieve in a many-to-many design?

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id;
  1. A It connects the two main tables - e.g. students to courses via an enrollments table
  2. B It sorts the tables
  3. C It removes duplicates
  4. D Nothing useful

Answer: A — It connects the two main tables - e.g. students to courses via an enrollments table

A many-to-many relationship requires two joins through the junction table: students JOIN enrollments JOIN courses. The junction may also carry extra data like enrolment date.

Many-to-manyJunction table

Q25. hard

What is the result if a join column contains NULLs in an INNER JOIN?

  1. A Those rows are excluded - NULL never equals anything, so they find no match
  2. B They match all rows
  3. C They cause an error
  4. D They match other NULLs

Answer: A — Those rows are excluded - NULL never equals anything, so they find no match

An order with customer_id = NULL cannot match any customer. If you need such rows, use a LEFT JOIN (which keeps them with NULLs on the right side).

NULLINNER JOIN

Q27. hard

Why prefer explicit JOIN syntax over comma-joins?

  1. A It separates the join CONDITION (ON) from row FILTERS (WHERE), preventing accidental Cartesian products and clarifying intent
  2. B It runs faster
  3. C Comma joins are invalid
  4. D There is no reason

Answer: A — It separates the join CONDITION (ON) from row FILTERS (WHERE), preventing accidental Cartesian products and clarifying intent

With explicit JOIN, forgetting the ON condition is an immediate, obvious error. It also makes outer joins possible, which comma syntax cannot express portably.

Best practicesReadability

Q29. hard

How does the query optimiser decide the physical join method?

  1. A Based on table sizes, indexes and statistics it picks nested-loop, hash or merge joins automatically
  2. B It always uses nested loops
  3. C The programmer must specify it
  4. D It joins randomly

Answer: A — Based on table sizes, indexes and statistics it picks nested-loop, hash or merge joins automatically

You write WHAT to join; the optimiser chooses HOW. EXPLAIN shows the plan. Keeping statistics current and indexes appropriate helps it choose well.

OptimiserEXPLAIN

Practice “INNER JOIN” as a quiz