Subqueries and Nested Queries

Ever stumbled upon a situation where you needed a query within a query? Today, we're diving into subqueries and nested queries in SQL. Trust me, once you get the hang of these, your SQL skills will level up.

Table of Contents

1. What Are Subqueries?
2. Using Subqueries in SELECT, FROM, WHERE Clauses
3. Correlated Subqueries

What Are Subqueries?

So, what's a subquery? Simply put, it's a query nested inside another query. Think of it as a mini-query that helps the main query get its job done.

Imagine you have two tasks:

• Find the highest salary in the company.
• Get the names of employees who earn that salary.

You could tackle this with a subquery.

Basic Structure

SELECT column_name(s)
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

Using Subqueries in SELECT, FROM, WHERE Clauses

Subqueries can be used in various places within your main query. Let's explore some common scenarios.

Subquery in WHERE Clause

Let's find all products that are more expensive than the average price.

SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Here, the subquery (SELECT AVG(Price) FROM Products) calculates the average price. The main query then fetches products priced above that average.

Subquery in SELECT Clause

Want to know how many orders each customer has placed?

SELECT CustomerName,
    (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

In this example, the subquery counts the number of orders for each customer.

Subquery in FROM Clause

Sometimes, you might need to treat the result of a subquery as a temporary table.

SELECT Sub.CustomerID, Sub.TotalSpent
FROM
    (SELECT CustomerID, SUM(Amount) AS TotalSpent
     FROM Orders
     GROUP BY CustomerID) AS Sub
WHERE Sub.TotalSpent > 1000;

Here, the subquery calculates total spending per customer. The main query then selects customers who spent more than $1000.

Correlated Subqueries

Correlated subqueries are a bit more advanced. They reference a column from the outer query. It's like a loop within a loop.

Example of a Correlated Subquery

Find employees who earn more than the average salary in their department.

SELECT e1.EmployeeName, e1.Salary
FROM Employees e1
WHERE e1.Salary > (SELECT AVG(e2.Salary)
                   FROM Employees e2
                   WHERE e2.DepartmentID = e1.DepartmentID);

In this query, the subquery depends on the outer query for e1.DepartmentID.

The result? Employees who are above average earners in their own departments.