Subqueries

Nested querry or Inner querry.

Introduction

In SQL, a subquery is a query nested inside another query. Subqueries are often used to perform intermediate steps or calculations that help the main query achieve more complex results. You can place subqueries in various SQL statements like SELECT, INSERT, UPDATE, or DELETE.

In customer management, subqueries are particularly useful for:

  • Finding specific customer data who have purchased certain products.
  • Aggregating data like total purchases, last transactions, or average spending per customer.

Key Characteristics of Subqueries

  • Subqueries must be enclosed in parentheses.
  • The output of a subquery can be used as a condition or as part of the result set for the outer query.
  • They can return a single value (scalar subquery), a list of values (row subquery), or even a full result set (table subquery).

Syntax

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

Tables

Customers

SQL logo

Orders

SQL logo

Types of Subqueries

Scalar Subquery

A scalar subquery returns a single value, which can be used in conditions or inserted directly into the select list. It is commonly used in comparison operations.

Example: To fetch customer names along with the total number of orders placed by each customer.

SELECT 
    customer_name, 
    (SELECT COUNT(*) 
     FROM Orders 
     WHERE Orders.customer_id = Customers.customer_id) AS total_orders
FROM 
    Customers;
SQL logo

Best Practices

  • Simplify complex queries by breaking them into smaller, more manageable subqueries.
  • Common Table Expressions (CTEs) are often more readable than subqueries, especially for complex queries.
  • Correlated subqueries are evaluated for each row, which can be slow. Use joins or CTEs when possible.
  • Avoid Subqueries in WHERE Clauses for Large Datasets. These can cause performance issues. Use joins or indexed columns instead.
  • Use EXISTS for Better Performance: In place of IN, EXISTS is often faster for checking the existence of rows.
  • Avoid deeply nested subqueries, as they can be hard to read and maintain.
  • Use Subqueries in the SELECT Clause Sparingly, as these can be expensive if repeated for every row.

Conclusion

Subqueries allow you to write complex queries by nesting one query within another. They are powerful for filtering data, computing aggregate values, and applying conditions across multiple tables. In customer management scenarios, subqueries help answer questions like finding top spenders, filtering by behavior, and deriving insights from complex purchase patterns.

Asked in Interview

  • What is the difference between a subquery and a JOIN?
  • What is a correlated subquery? Can you give an example?
  • Explain the difference between correlated and non-correlated subqueries.
  • How do you use a subquery with an EXISTS clause?
  • What is the difference between IN and EXISTS in subqueries?
  • What are the performance considerations when using subqueries in SQL?
  • Can a subquery be used in a SELECT statement? Give an example.