Lesson 13 of 14 · 8 min · Database theory for interviews

Join types and indexes: the DBMS interview view

Covers: Standard relational database theory with PostgreSQL syntax; MySQL InnoDB notes. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • A one-line meaning of every join type, with a tiny example
  • How a database actually performs a join
  • Clustered versus non-clustered indexes
  • B-tree and hash indexes
  • Answer join and index questions in an interview

In simple words

Joins and indexes are the two topics every database interview asks about. A join glues two tables together by matching a column. An index is a lookup list that makes finding rows fast. The earlier lessons showed how to use them; this lesson puts the vocabulary and explanations in the form an interviewer expects, in a few words each.

Words to know

  • Equi-join: a join that matches rows where two columns are equal.
  • Nested loop: a join method that, for each row of one table, looks for matches in the other.
  • Hash join: a join method that builds a lookup table in memory from one side and probes it with the other.
  • Clustered index: an index that decides the physical order of the table's rows.

Join types in one line each

Use two tiny tables: A has ids 1, 2, 3 and B has ids 2, 3, 4, joined on id.

Join Keeps Result ids
INNER JOIN rows matching in both 2, 3
LEFT JOIN all of A, matches from B 1, 2, 3 (B side NULL for 1)
RIGHT JOIN all of B, matches from A 2, 3, 4 (A side NULL for 4)
FULL OUTER JOIN all rows of both 1, 2, 3, 4
CROSS JOIN every pairing 3 x 3 = 9 rows
Self join a table joined to itself for example employee and manager

Two more worth knowing:

  • Natural join (NATURAL JOIN) matches automatically on all columns with the same name. Avoid it: adding a column later silently changes the result.
  • Non-equi join: the ON condition uses <, > or BETWEEN, for example matching a score to a grade band.

MySQL has no FULL OUTER JOIN; combine a left and right join with UNION. Set operators work on rows, not on matching keys: UNION (distinct), UNION ALL (keeps duplicates, faster), INTERSECT, EXCEPT (PostgreSQL; MySQL 8.0.31 and later).

How a database performs a join

The planner picks one of three main strategies:

  1. Nested loop: for each row of the outer table, probe the inner table, ideally through an index. Best when the outer side is small and the inner join column is indexed.
  2. Hash join: build an in-memory hash table from the smaller side, then scan the larger side and look up each row. Good for large unsorted inputs with equality conditions.
  3. Merge join: sort both inputs on the join key (or use indexes that are already sorted) and walk them together. Good when both sides are large and already ordered.

You do not choose; the planner estimates costs from table statistics. EXPLAIN shows which one it picked. If a join is slow, the usual fix is an index on the join column of the large table.

Indexes: the interview version

An index is a sorted structure separate from the table that speeds up lookups at the cost of storage and write speed (see the previous index lesson).

Clustered versus non-clustered:

  • A clustered index stores the table's rows themselves in index order, so there can be only one per table. In MySQL InnoDB the primary key is always the clustered index. SQL Server lets you pick. PostgreSQL has no clustered index in this sense: tables are heaps, and CLUSTER reorders once but is not maintained.
  • A non-clustered (secondary) index is a separate structure holding the indexed values and a pointer to the row (in InnoDB, the primary key value). You can have many.

B-tree versus hash:

  • A B-tree keeps keys in sorted order in a balanced tree. It supports =, <, >, BETWEEN, ORDER BY and prefix LIKE 'abc%'. It is the default.
  • A hash index supports only equality (=) and no ordering or ranges. It is rarely needed; PostgreSQL has it, and MySQL InnoDB uses an adaptive hash internally.

Other types you may meet: bitmap indexes (Oracle, for low-cardinality columns), full-text indexes (text search), and PostgreSQL's GIN, GiST and BRIN for special data.

Cardinality and selectivity: an index on a column with few distinct values (gender, a yes/no flag) is rarely useful alone, because it narrows nothing. High-cardinality columns (email, id) are good index candidates.

Typical interview answers

  • "Why is a join slow?" No index on the join column, a huge intermediate result from a missing filter, or bad statistics. Check EXPLAIN.
  • "Does an index always speed things up?" No. It speeds reads on filter, join and sort columns, but slows writes and may be ignored on small tables or non-selective filters.
  • "Primary key versus index?" A primary key is a constraint (unique and not NULL) and is backed by an index. An index alone enforces nothing.
  • "What is a covering index?" An index that holds every column a query needs, so the table is never read.

Common mistakes

  • Saying a left join "has more rows than inner" without noting it equals inner plus the unmatched left rows.
  • Confusing UNION with a join: union stacks rows, a join widens rows.
  • Believing the join order you write is the order executed; the optimiser reorders.
  • Claiming PostgreSQL has a clustered index in the SQL Server sense.

Try it yourself

  1. Create tables A(id) and B(id) with the values above and run all join types, noting the rows.
  2. Run EXPLAIN on a join of two tables with and without an index on the join column. Which join method appears?
  3. Explain in your own words to a friend why an index on a yes/no column is usually not helpful.
  4. Write a non-equi join that assigns a grade from a grades(min_score, max_score, grade) table.

Check yourself

How many rows does a CROSS JOIN of a 3-row table and a 4-row table return?

12, every row paired with every row (3 x 4).

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows, which costs a sort or hash. UNION ALL keeps every row and is faster.

Why can a table have only one clustered index?

The clustered index defines the physical order of the rows, and rows can be stored in only one order.

Which index type supports range queries, B-tree or hash?

B-tree. A hash index supports only equality.

Interview questions

What is the difference between an inner join and an outer join?

An inner join returns only matched rows. An outer join also returns unmatched rows from one side (left or right) or both (full), filling the missing side with NULLs.

How does a hash join work?

The database builds a hash table in memory from the smaller input keyed on the join column, then reads the larger input and probes the hash table for matches, giving fast equality joins on unsorted data.

What is the difference between a clustered and a non-clustered index?

A clustered index is the table's data ordered by the key (one per table). A non-clustered index is a separate structure pointing to rows, and a table can have many.

Summary

  • Inner keeps matches; left, right and full outer keep unmatched rows; cross pairs everything; avoid natural joins.
  • The database runs joins as nested loop, hash or merge, chosen by cost; an index on the join column often fixes a slow one.
  • A clustered index orders the table (one per table); non-clustered indexes are separate (many).
  • B-trees support ranges and sorting; hash indexes support equality only.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.