The Complete Guide to Mastering Subquery SQL Interview Questions

Subqueries are one of the most powerful yet tricky concepts in SQL. As an aspiring database professional you can expect subquery questions to feature prominently in technical interviews.

In this comprehensive guide, I’ll walk you through everything you need to know to tackle subquery SQL interview questions and impress interviewers with your mastery of this complex SQL feature.

What are Subqueries in SQL?

A subquery is an SQL query nested within another SQL query It allows you to perform operations on data sets retrieved by the outer query.

Subqueries enable you to

• Simplify complex SQL operations by breaking them into smaller, more manageable components
• Retrieve data from multiple tables in a single command
• Filter and manipulate data dynamically based on values calculated by another query

With subqueries, you can achieve in one line of code what would otherwise require multiple steps of logic across queries. This power and flexibility makes subqueries an indispensable tool for any SQL developer.

Now let’s look at some common subquery interview questions and how to tackle them like a pro!

Top Subquery SQL Interview Questions and Answers

Q1. Explain the Difference Between Subqueries and Joins

Answer: The key difference is that subqueries allow you to perform multi-step operations in a single query, while joins combine data from multiple tables in one go.

Subqueries are best for:

• Filtering or calculating values needed for the outer query
• Complex logic across queries
• Queries involving unlinked data

Joins are better when:

• Direct relationships exist between tables
• You want to retrieve data from multiple tables simultaneously
• Performance is critical

• Subqueries simplify complex, multi-step SQL operations
• Joins efficiently combine directly related data

Q2. When Would Subqueries be More Efficient Than Joins?

Answer: Subqueries can outperform joins when:

• You need to filter or manipulate data before joining it. For example, calculating averages or finding top customers.

• Tables lack direct relationships. Subqueries can retrieve data from unrelated tables.

• You want to avoid joining large tables just to filter a few rows. Subqueries can operate on subsets.

• Query logic involves multi-step calculations or aggregations. Subqueries modularize operations.

So subqueries shine for complex logic, unlinked data, or when operating on subsets before joining.

Q3. Explain Correlated vs Non-Correlated Subqueries

Answer:

• Non-correlated subqueries execute once before the outer query. They are self-contained and don’t depend on the outer query.

• Correlated subqueries execute once for each row processed by the outer query. They reference values from the outer query’s rows.

For example, a non-correlated subquery calculates average salary just once:

SELECT name FROM employeesWHERE salary > (SELECT AVG(salary) FROM employees)

A correlated subquery recalculates the average for each employee:

SELECT nameFROM employees outerWHERE salary > (SELECT AVG(salary)                FROM employees inner               WHERE inner.dept = outer.dept)

So correlated subqueries rerun for every row, making them slower on large data sets.

Q4. How Can You Improve Subquery Performance?

Answer: Optimization strategies include:

• Switch to joins where possible as they are faster

• Use EXISTS over IN for existence checks

• Avoid correlated subqueries which require row-by-row processing

• Break complex subqueries into smaller, simpler components

• Ensure tables have optimal indexes, especially on join/where columns

• Check execution plans to identify inefficient operations

• Monitor server load to rule out external factors

Q5. When Would You Use a Subquery in SELECT vs WHERE?

Answer:

In SELECT: When you want to return a calculated value or aggregate result:

SELECT name,        (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees

In WHERE: To filter rows based on a condition using a subquery:

SELECT nameFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees) 

So:

• SELECT to return calculated columns
• WHERE to filter rows

This distinguishes their uses cases clearly.

Q6. Can You Provide an Example of a Subquery in INSERT, UPDATE, DELETE?

Answer:

INSERT: Insert records using a subquery:

INSERT INTO managers (name, dept)SELECT name, dept FROM employeesWHERE level = 'Manager'

UPDATE: Update values based on a subquery condition:

UPDATE employees SET salary = salary * 1.1WHERE dept IN (SELECT dept                FROM departments                WHERE headcount < 10)

DELETE: Delete rows not meeting subquery criteria:

DELETE FROM order_items WHERE order_id NOT IN (SELECT id FROM orders WHERE year > 2020)

The key is using subquery results in the WHERE clause to determine inserts, updates, and deletes.

Q7. How Do You Handle a Subquery Returning NULL?

Answer: To handle NULL results from a subquery:

• Use IS NULL to check if the subquery result is NULL

• Use COALESCE to replace NULL with a default value

For example:

SELECT COALESCE(  (SELECT col FROM tab WHERE cond),   0))

Properly handling NULLs avoids unexpected query behavior.

Q8. Write a Subquery to Find the Nth Highest Salary

Answer: We can find the Nth highest salary using ORDER BY and OFFSET in a subquery:

SELECT salaryFROM employeesORDER BY salary DESCOFFSET n-1 ROWS FETCH FIRST 1 ROW ONLY

Where n is the Nth highest salary we want.

The OFFSET skips the first n-1 rows, while FETCH FIRST 1 row ONLY returns the Nth row – giving us the Nth highest salary cleanly in a subquery.

Q9. Explain Different Types of Subqueries

Answer: The main subquery types are:

• Single-row – Returns one column value from the inner SELECT

• Multiple-row – Returns multiple rows using IN, ANY, or ALL

• Correlated – Dependent on and executed once per outer query row

• Non-correlated – Self-contained, execute once before outer query

• Nested – A subquery within another subquery

Understanding the subtype determines how you can use the subquery result in the outer query.

Q10. When Would You Use Subqueries in HAVING vs WHERE?

Answer:

WHERE filters rows before aggregation.

HAVING filters aggregated results.

For example:

SELECT year, SUM(revenue) AS total_revenueFROM financeWHERE revenue > 1000 -- Filter before groupingGROUP BY yearHAVING total_revenue > 1000000 -- Filter after aggregation

So use:

• WHERE to filter raw rows before aggregation
• HAVING to filter based on aggregated results

This illustrates the difference in their filter contexts.

Putting it All Together

Mastering subquery SQL interview questions requires understanding:

• How subqueries differ from joins
• Various subquery types and use cases
• Performance optimization strategies
• Techniques to handle NULLs and duplicates
• Using subqueries across INSERT, UPDATE, DELETE, SELECT
• Filtering context of WHERE vs HAVING

I hope these examples and explanations have equipped you to handle any subquery question thrown your way. Use this guide to gain confidence and stand out in your next technical interview!