If you’ve ever sat in a technical interview for a data analyst, backend developer, or data engineer role, you already know how much weight SQL holds. Interviewers often test not just your ability to write queries but also your understanding of how databases work behind the scenes. Among the most frequent database interview questions, topics like joins, subqueries, and indexing tend to appear repeatedly — and for good reason.
These concepts form the foundation of database performance, data relationships, and query optimization — skills every data professional must master. In this blog, we’ll break down these topics, share common interview questions, and explain how to approach them effectively.
In relational databases, data is often stored across multiple tables to reduce redundancy. To retrieve meaningful information, you need to combine these tables — and that’s where SQL joins come in.
Common Types of Joins
INNER JOIN: Returns rows when there is a match in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right.
RIGHT JOIN: Returns all rows from the right table and matching ones from the left.
FULL JOIN: Returns all rows when there’s a match in either table.
CROSS JOIN: Returns the Cartesian product of both tables (every possible combination).
Interview Example
Question:
What is the difference between INNER JOIN and LEFT JOIN?
Answer:
INNER JOIN returns only the matching records from both tables, whereas LEFT JOIN returns all records from the left table and the matched records from the right table. When there’s no match, the result for the right table columns is NULL.
Example Query:
SELECT e.employee_name, d.department_name
FROM Employees e
LEFT JOIN Departments d
ON e.department_id = d.department_id;
Follow-Up Question:
How would you find employees who don’t belong to any department?
Answer:
Use a LEFT JOIN and filter where the right table column is NULL:
SELECT e.employee_name
FROM Employees e
LEFT JOIN Departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
A subquery (or nested query) is a query inside another SQL query. Subqueries are often used to filter, aggregate, or compare data dynamically.
Common Types of Subqueries
Scalar subquery: Returns a single value.
Row subquery: Returns a single row with multiple columns.
Table subquery: Returns a complete table result.
Correlated subquery: References a column from the outer query, executing repeatedly for each row.
Interview Example
Question:
Write a query to find employees earning more than the average salary of their department.
Answer:
SELECT e.employee_name, e.salary
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM Employees
WHERE department_id = e.department_id
);
This is a correlated subquery, as the inner query references a column (department_id) from the outer query.
Follow-Up Question:
When would you use a JOIN instead of a subquery?
Answer:
Use a JOIN when you need data from multiple tables in a single dataset. Use a subquery when you need to filter or calculate values dynamically. Subqueries can be less performant than joins, especially when not indexed properly.
When working with large datasets, performance becomes crucial. That’s where indexes come in. An index helps the database engine find rows faster without scanning the entire table.
Common Types of Indexes
Clustered Index: Determines the physical order of data in a table. (One per table)
Non-Clustered Index: A separate structure that points to the data. (Multiple allowed per table)
Composite Index: An index on multiple columns.
Unique Index: Ensures that all values in a column are unique.
Interview Example
Question:
How does an index improve query performance, and when can it become a problem?
Answer:
Indexes make SELECT queries faster by allowing the database to find data more efficiently. However, they can slow down INSERT, UPDATE, and DELETE operations because the database must also update the index each time the underlying data changes.
Follow-Up Question:
How do you identify if an index is being used by a query?
Answer:
Use the EXPLAIN (MySQL/PostgreSQL) or EXPLAIN PLAN (Oracle) command before your query to check if the database is using an index for data retrieval.
Interviewers often test your understanding of joins, subqueries, and indexing together using real-world problems.
Example Problem
Find the top 3 departments with the highest average salary.
Answer:
SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM Employees e
JOIN Departments d
ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC
LIMIT 3;
Here, you use a JOIN to combine data, GROUP BY for aggregation, and ORDER BY for ranking — a classic combination in many database interview questions.
Knowing theory isn’t enough — you need to understand why certain queries perform better.
Interview Tip: When discussing query performance, always mention these points:
Use indexes wisely. Avoid indexing every column — focus on frequently searched or joined columns.
**Avoid SELECT *. Always specify columns to reduce data load.
Use EXISTS instead of IN for large datasets in subqueries.
Analyze query plans to understand performance bottlenecks.
Use CTEs (Common Table Expressions) for better readability and maintainability.
To prepare effectively:
Practice writing queries — use platforms like LeetCode, HackerRank, or Talent Titan’s practice section.
Understand the theory — especially around normalization, transactions, and constraints.
Work with real datasets — apply joins and indexing on large tables to see the performance difference.
Study common database interview questions — these often repeat across companies with small variations.
Mastering joins, subqueries, and indexing is not just about clearing interviews — it’s about understanding how databases truly work. These topics form the backbone of efficient SQL query writing and data manipulation.
If you’re preparing for upcoming technical interviews, make sure to revisit these core topics and practice writing real-world queries. Many database interview questions revolve around your ability to think logically about data — and these concepts are your toolkit for doing just that.
For more interview preparation resources, check out Talent Titan, where you can explore curated guides, coding challenges, and expert-led insights to boost your database skills and confidence.