The Ultimate Guide to SQL Subqueries in Interviews

SQL (Structured Query Language) is one of the most sought-after skills in tech interviews, especially for roles involving databases or backend development. Among the key SQL topics, subqueries are a common subject for interview questions. Whether you’re a fresher or an experienced candidate, mastering SQL subqueries will help you demonstrate your problem-solving skills effectively.

In this ultimate guide, we will break down the concept of SQL subqueries, how to write them, and provide you with useful tips and tricks to help you ace SQL-related interview questions.

What is a SQL Subquery?

A SQL subquery (also called a nested query) is a query within another query. It allows you to execute a query inside a SELECT, INSERT, UPDATE, or DELETE statement. Subqueries can be used to perform operations that require two or more steps or that rely on intermediate results.

Example of a subquery:

        SELECT employee_id, employee_name
        FROM employees
        WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'HR');
    

In the example above, the subquery retrieves department_id values for the HR department, and the main query uses this result to filter employees who belong to that department.

Types of SQL Subqueries

1. Single-row Subqueries

This type of subquery returns a single value (or row). It is typically used with operators like =, <, >, <=, and >=.

Example:

        SELECT employee_name
        FROM employees
        WHERE salary > (SELECT avg(salary) FROM employees);
    

2. Multiple-row Subqueries

A multiple-row subquery returns more than one row. It is used with operators like IN, ANY, and ALL.

Example:

        SELECT employee_name
        FROM employees
        WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
    

3. Correlated Subqueries

A correlated subquery references a column from the outer query. This type of subquery executes once for each row of the outer query.

Example:

        SELECT e1.employee_name
        FROM employees e1
        WHERE e1.salary > (SELECT avg(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id);
    

4. Scalar Subqueries

Scalar subqueries return a single value (a single column and row). They can be used wherever an expression is allowed.

Example:

        SELECT employee_name
        FROM employees
        WHERE salary = (SELECT max(salary) FROM employees);
    

Why are SQL Subqueries Important in Interviews?

SQL subqueries are commonly used in database-related interview questions to assess:

• Problem-solving skills
• Logical thinking
• The ability to work with complex data sets
• The depth of knowledge in SQL syntax and performance optimization

Understanding how to write efficient subqueries is essential to solving complex database problems, which is why they are often asked in technical interviews.

Tips for Writing Efficient SQL Subqueries

• Avoid Using Subqueries When Possible: Sometimes, you can rewrite a query to avoid using a subquery by joining tables. This can improve performance, as joins are generally faster than subqueries.
• Use Aliases: Using table aliases in correlated subqueries makes your queries more readable and efficient.
• Limit the Result Set: Use the LIMIT clause (or equivalent) when working with large datasets to prevent performance degradation.
• Optimize Subqueries: In some cases, it might be better to use a temporary table or common table expression (CTE) for better readability and performance.
• Understand Nested Subqueries: Be aware of how subqueries interact with each other, especially in complex queries. Try to simplify them for better clarity.

Common Interview Questions on SQL Subqueries

1. What is the difference between a subquery and a join in SQL?

Answer: A subquery is a query inside another query, whereas a join is used to combine data from two or more tables based on a related column. While subqueries can be used with SELECT, INSERT, UPDATE, or DELETE statements, joins are more efficient for combining tables.

2. Can you use a subquery in a WHERE clause?

Answer: Yes, subqueries are often used in the WHERE clause to filter results based on the output of another query. For example, you can use a subquery to return values that meet specific conditions in a nested query.

3. What is a correlated subquery?

Answer: A correlated subquery is a type of subquery that refers to a column from the outer query. It is executed once for every row in the outer query, making it more resource-intensive than non-correlated subqueries.

4. What are the potential performance issues with using subqueries?

Answer: Subqueries can lead to performance issues when they are nested multiple times or when they return a large result set. In such cases, it might be better to refactor the query using joins or temporary tables.