Sign in Subscribe
2 min read

SQL - Subqueries

Subqueries in SQL are queries embedded within other queries, and they can be used in various parts of a SQL statement, such as the SELECT, FROM, or WHERE clauses. They allow you to run a select from a customized set of data that may not initially exist in the original table/column structure.

They may look daunting at first, but it comes down to breaking the query down into steps, the way you would do with a regular query. Here are some advanced examples of using subqueries:

-- select all the rows from the Customers table with the minimum age
SELECT *
FROM Customers
WHERE age = (
  SELECT MIN(age)
  FROM Customers
);

In a subquery, the outer query's result depends on the result set of the inner subquery. That's why subqueries are also called nested queries.

If you break down the above example, we are selecting all columns from Customers table but running a condition for the age. Now we are running a subquery to find the minimum age of the customer, and that is the value we set for the query condition for our initial age in the WHERE age = operator.

1. Subquery in the WHERE Clause with EXISTS:

You can use a subquery in the WHERE clause with the EXISTS keyword to check for the existence of certain conditions.

SELECT ProductName
FROM Products
WHERE EXISTS (
    SELECT 1
    FROM OrderDetails
    WHERE Products.ProductID = OrderDetails.ProductID
    AND Quantity > 10
);

This query retrieves product names where the product has been ordered in quantities greater than 10 in the OrderDetails table.

2. Subquery in the FROM Clause with JOIN:

Subqueries can be used in the FROM clause to create a derived table, which can then be joined with other tables.

SELECT Customers.CustomerName, OrderTotals.TotalAmount
FROM Customers
JOIN (
    SELECT CustomerID, SUM(Price * Quantity) AS TotalAmount
    FROM OrderDetails
    GROUP BY CustomerID
) AS OrderTotals ON Customers.CustomerID = OrderTotals.CustomerID;

Here, the subquery calculates the total amount spent by each customer in the OrderDetails table, and the main query joins it with the Customers table to retrieve customer names along with their total order amounts.

3. Scalar Subquery in the SELECT Clause:

A subquery can be used to return a single value (a scalar) in the SELECT clause.

SELECT ProductName, Price, (
    SELECT AVG(Price)
    FROM Products
) AS AvgProductPrice
FROM Products;

In this example, each row in the result set includes the average product price calculated using the subquery in the SELECT clause.

Our scalar value in the subquery, we are selecting finding the AVG price which becomes the single value and using the AS alias to set it as the AvgProductPrice in our result.

4. Correlated Subquery:

A correlated subquery is one where the inner query references columns from the outer query. It is executed for each row processed by the outer query.

SELECT EmployeeID, FirstName, LastName,
       (SELECT COUNT(OrderID) FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID) AS OrderCount
FROM Employees;

This query retrieves employee information along with the count of orders for each employee. The subquery is correlated to the outer query by referencing the EmployeeID column.

5. Subquery with Aggregation in HAVING Clause:

Subqueries can be used in the HAVING clause to filter groups based on aggregated values.

SELECT CategoryID, AVG(Price) AS AvgPrice
FROM Products
GROUP BY CategoryID
HAVING AVG(Price) > (
    SELECT AVG(Price)
    FROM Products
);

Here, the main query groups products by CategoryID and only includes those categories where the average price is greater than the overall average price of all products.

6. Subquery with IN Operator:

Use the IN operator with a subquery to filter results based on a set of values returned by the subquery.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE OrderDate >= '2023-01-01'
);

This query retrieves customers who have placed orders after a specific date.

Conclusion:

Subqueries add flexibility and depth to your SQL queries, allowing for more complex and context-dependent operations. By understanding how to use subqueries in various parts of your queries, you can perform advanced data manipulations and gain valuable insights from your relational databases.

Published by:

Tahsin