25 Joins questions with answers for sql roles — 8 easy, 13 medium, 4 hard.
About Joins
Joins combine data from multiple tables. Practise inner, left, right, full outer, cross and self joins, plus semi and anti joins with EXISTS. Learn how ON versus WHERE changes results, why joins duplicate rows, how to join on ranges, and how databases execute joins with nested loop, hash and merge algorithms.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
A join combines rows from two or more tables based on a related column. INNER JOIN returns matching rows only. LEFT JOIN returns all rows from the left table with matches or NULLs. RIGHT JOIN does the opposite. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A SELF JOIN joins a table to itself, such as employees and managers.
02
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.
A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.
A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.
SELECT o.order_id, o.order_date, c.name, o.amount
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;
Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Alternative:
SELECT customer_id, name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
Situation: after college I knew only basic SELECT queries. Task: become interview-ready for data roles. Action: I solved two practice problems daily, read execution plans for my own queries, revised window functions weekly and built a small project analysing public sales data in PostgreSQL. Result: within three months I could solve medium-level problems confidently, and I continue practising regularly.
08
What happens when you join on a column that has NULL values?
NULL never equals NULL in a join condition, so rows with NULL in the join column do not match each other in an inner join. In a LEFT JOIN, left rows with NULL keys are kept but get NULLs for right columns. If you really need NULLs to match, use a condition such as a.key IS NOT DISTINCT FROM b.key, or COALESCE both sides carefully.
09
What is the difference between a join and a union?
A join combines columns from different tables side by side based on a matching condition, making rows wider. A union stacks results of queries with the same column structure vertically, making the result longer. Use a join to bring customer names next to orders, and a union to combine 2024 and 2025 order tables into one list.
10
What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?
In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.
11
What are equi joins, non-equi joins and natural joins?
An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.
A semi join returns rows from the first table that have at least one match in the second, without duplicating them, usually written with EXISTS or IN. An anti join returns rows with no match, written with NOT EXISTS or LEFT JOIN with WHERE right.key IS NULL. For example, customers who never ordered is an anti join. They avoid duplicates that a normal join could create.
13
Write a query to show each employee with their manager's name.
SELECT e.emp_id,
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.
14
Join three tables to get total sales by product category.
SELECT cat.category_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;
Join from the most detailed table outward and aggregate at the end.
15
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
16
After joining orders with payments, revenue doubled. What went wrong?
Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.
17
A LEFT JOIN is returning fewer rows than the left table. Why could that happen?
A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.
18
You need customers who bought both product A and product B. How would you write it?
Group by customer and count distinct matching products: SELECT customer_id FROM order_items WHERE product_id IN ('A', 'B') GROUP BY customer_id HAVING COUNT(DISTINCT product_id) = 2. Alternatively use INTERSECT of two queries, or two EXISTS conditions. The HAVING approach is concise, scans the table once and is easy to extend to three or more products.
19
Describe a time you found and fixed a wrong number in a report.
Situation: a monthly dashboard showed 30 percent higher revenue than finance figures. Task: find the cause before the leadership review. Action: I traced the SQL, found a join to the shipments table that duplicated orders with split shipments, rewrote it with a pre-aggregated CTE and reconciled totals with finance. Result: numbers matched exactly and I added a row-count test to the report pipeline.
20
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
21
Compare this month's and last month's sales per product using a full outer join.
SELECT COALESCE(c.product_id, p.product_id) AS product_id,
COALESCE(c.sales, 0) AS this_month,
COALESCE(p.sales, 0) AS last_month
FROM cur_month c
FULL OUTER JOIN prev_month p
ON c.product_id = p.product_id;
Products sold in only one of the months still appear, with zero for the other month.
Optimisers choose between nested loop joins, efficient when one side is small and the other has an index on the join key, hash joins, which build a hash table on the smaller input and suit large unsorted equality joins, and merge joins, which work on inputs sorted by the join key. Statistics, indexes and memory influence the choice, visible in the execution plan.
23
Show all dates in a month with sales, including days with zero sales.
WITH days AS (
SELECT generate_series(DATE '2025-01-01',
DATE '2025-01-31', INTERVAL '1 day')::date AS d
)
SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales
FROM days d
LEFT JOIN sales s ON s.sale_date = d.d
GROUP BY d.d
ORDER BY d.d;
This PostgreSQL example uses a date series; other databases use a calendar table.
24
A join between two large tables takes minutes. How would you speed it up?
Check the execution plan for scans, join type and row estimates. Ensure join keys have indexes and matching data types, because implicit conversion blocks index use. Filter both sides early, select only needed columns, update statistics, and avoid functions on join columns. For very large data, consider partitioning, pre-aggregated tables, or batch processing outside peak hours.
25
Two systems store customer IDs differently, one as INT and one as VARCHAR with leading zeros. How do you join them?
Convert consistently, for example CAST(b.cust_code AS INT) = a.customer_id, but understand this prevents index use on b.cust_code and fails on non-numeric values. Better options are a computed or persisted column with the converted value and an index, or a mapping table built during ETL. Also validate unmatched rows to catch formatting issues such as spaces.
Frequently asked questions
How important is Joins in SQL interviews?
Joins combine data from multiple tables. Practise inner, left, right, full outer, cross and self joins, plus semi and anti joins with EXISTS. Learn how ON versus WHERE changes results, why joins duplicate rows, how to join on ranges, and how databases execute joins with nested loop, hash and merge algorithms.
How should I use the Joins sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.