SQL Joins Interview Questions and Answers

Joins are the most tested topic in any SQL interview, because they reveal whether you actually understand how relational data fits together. This set walks through the join questions interviewers reuse constantly — inner vs outer, LEFT vs RIGHT, self joins, anti-joins and the ON vs WHERE trap — each with a spoken answer and a query you can write live.

Why interviewers ask about joins

Real applications spread data across many tables, so joining is not optional — it is how you answer nearly every reporting question. An interviewer who watches you write a join learns instantly whether you think in sets or guess at syntax.

The favourite trick is not an exotic join; it is a simple LEFT JOIN with a condition in the wrong clause, or a question about which rows disappear. If you can explain which rows each join keeps and drops, you have answered eighty percent of join questions before they are asked.

If joins still feel abstract, read the visual SQL joins explained tutorial first, then use this page to rehearse the interview phrasing. These questions build on the fundamentals in the SQL freshers set.

How to answer join questions

For every join question, name which table is "kept in full" and what happens to unmatched rows. Say it out loud: "LEFT JOIN keeps all left rows; unmatched right columns become NULL." That single sentence answers most of them.

Draw two overlapping circles or sketch two three-row tables and point at the result. Interviewers reward a candidate who can show the output, not just recite a definition. Keep one example schema — employees and departments — ready so you never waste time inventing tables.

Q1. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only rows with a match in both tables. OUTER JOIN (LEFT, RIGHT or FULL) keeps unmatched rows from one or both tables and fills the missing side with NULL.

The choice depends on whether "no match" is a result you care about. If you want employees and the ones without a department, you need an outer join.

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

This drops any employee whose department_id is NULL or points to a missing department.

Interview note: Follow-up: "how many rows does INNER JOIN return if no rows match?" Zero — no match means no output row.

Q2. LEFT JOIN vs RIGHT JOIN — what is the real difference?

LEFT JOIN keeps all rows from the left (first) table; RIGHT JOIN keeps all rows from the right (second) table. They are mirror images — any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order.

Most developers standardize on LEFT JOIN because it reads in the same direction as the FROM clause. RIGHT JOIN is rare in practice and appears mostly to test whether you understand the symmetry.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Interview note: Trap: "rewrite this RIGHT JOIN as a LEFT JOIN." Swap the two table names and change RIGHT to LEFT — the result is identical.

Q3. How do you find rows in one table with no match in another?

Use a LEFT JOIN and filter WHERE the right table's key IS NULL. This anti-join keeps only left-table rows that found no match.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

This returns employees assigned to no valid department. It is one of the most-asked join questions because it combines outer joins with NULL handling.

Interview note: Follow-up: "why check the right table's key specifically?" Because after a LEFT JOIN, unmatched right rows are entirely NULL, so its key being NULL marks a non-match.

Q4. What is a self join, and when do you use one?

A self join joins a table to itself using aliases. You use it when rows relate to other rows in the same table — like employees and their managers, who are also employees.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

The LEFT JOIN version also lists the top boss, whose manager_id is NULL. Self joins feel odd at first but are just a normal join where both sides happen to be the same table.

Interview note: Trap: "why must you use aliases in a self join?" Without aliases the two copies of the table are indistinguishable, so column references are ambiguous.

Q5. What is a cross join?

A CROSS JOIN returns the Cartesian product: every row of the first table paired with every row of the second. If one table has 4 rows and the other 3, you get 12 rows.

You use it deliberately to generate combinations — all size-and-color pairs of a product, for example. More often, a cross join appears by accident when you forget the ON condition.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Interview note: Follow-up: "how can a normal join accidentally become a cross join?" Omitting the join condition (an old-style comma join with no WHERE) produces the full Cartesian product.

Q6. Does a condition in ON behave differently from the same condition in WHERE?

For a LEFT JOIN, yes. A condition on the right table in the ON clause still keeps unmatched left rows as NULLs, while the same condition in WHERE removes those NULL rows and effectively converts the LEFT JOIN into an INNER JOIN.

-- Keeps all employees; only shows dept_name for HR
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id AND d.dept_name = 'HR';

Move d.dept_name = 'HR' to a WHERE and every non-HR employee vanishes, because their NULL dept_name fails the filter.

Interview note: Trap: this exact question separates people who memorized joins from people who understand them. Always ask yourself where the filter should live.

Q7. Can you join more than two tables? How?

Yes — chain joins one after another, each with its own ON condition. The result of the first join becomes the left input to the next.

SELECT e.name, d.dept_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN locations l ON d.location_id = l.id;

Order matters for readability but the optimizer decides the physical execution order. Keep join conditions explicit so no accidental cross join sneaks in.

Interview note: Follow-up: "does the order you write joins change the result?" For inner joins the final set is the same; for outer joins the order can change which rows are preserved.

Q8. What is the difference between JOIN and UNION?

A JOIN combines columns from two tables side by side based on a related key, widening each row. A UNION stacks the rows of two queries on top of each other, requiring the same columns.

Think of JOIN as horizontal (more columns per row) and UNION as vertical (more rows). They solve completely different problems and are only confused by name.

Interview note: Trap: "can you use JOIN to combine two tables with identical structure into one list?" That is a UNION job, not a join.

Q9. How do NULLs behave in join conditions?

A join condition like a.id = b.id never matches when either side is NULL, because NULL = NULL is unknown, not true. So NULL keys never join and always end up as unmatched rows.

This is why an outer join surfaces rows with NULL foreign keys as non-matches. If you need NULLs to be treated as equal, some databases offer a null-safe operator, but the default is no match.

Interview note: Follow-up: "will two rows with NULL department_id join to each other?" No — NULL never equals NULL in a standard join.

Q10. What is an equi join versus a non-equi join?

An equi join uses = to match rows on equal values — the everyday join. A non-equi join uses another operator like <, > or BETWEEN, matching on a range instead of equality.

Non-equi joins solve problems like assigning a salary to a grade band.

SELECT e.name, g.grade
FROM employees e
JOIN salary_grades g
  ON e.salary BETWEEN g.low AND g.high;

Interview note: Trap: "is BETWEEN a join condition?" Yes — a range predicate in ON is a perfectly valid non-equi join.

Q11. How do joins interact with GROUP BY and aggregates?

You often join first to bring related columns together, then GROUP BY to summarize. But a join that multiplies rows (one-to-many) will inflate SUM and COUNT, so you must be aware of the join's cardinality.

SELECT d.dept_name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.dept_name;

Using LEFT JOIN here keeps empty departments with a count of zero. Combine this with the GROUP BY tutorial to see aggregation clearly.

Interview note: Follow-up: "why COUNT(e.id) and not COUNT(*)?" COUNT(*) would count the NULL row produced for an empty department as one; COUNT(e.id) correctly returns zero.

Q12. When would you use a subquery instead of a join?

Use a join when you need columns from both tables in the output. Use a subquery when you only need to filter or check existence against another table, such as WHERE department_id IN (SELECT ...).

Joins and subqueries often produce the same result, and the optimizer may run them similarly. Choose the one that reads more clearly; for existence checks, EXISTS and anti-joins are usually cleanest.

SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

The line between joins and subqueries matters for harder questions like the second highest salary problem; the subqueries tutorial covers the trade-offs.

Interview note: Trap: "does a subquery always run once per outer row?" Only a correlated subquery does; an independent subquery runs once.

How to prepare

Create two related tables locally and reproduce every result above — especially the anti-join (LEFT JOIN ... WHERE key IS NULL) and the ON vs WHERE difference, which are the two questions that trip up most candidates. Seeing the rows appear and disappear fixes the concept for good.

Rehearse by narrating which rows each join keeps before you type. Then stress-test yourself against the broader SQL freshers questions and learning hub for mixed rounds. Solid joins are the backbone of the database work in the Java Full Stack with AI course, so the effort pays off well beyond the interview.