SQL Subqueries – Using Queries Inside Queries

Lesson 11: SQL Subqueries – Using Queries Inside Queries

Estimated Read Time: 25-30 minutes

Objective: In this lesson, you will learn about SQL Subqueries, a powerful concept where one query is embedded inside another. We will cover how to use subqueries in SELECT, INSERT, UPDATE, and DELETE statements.

1. What is a Subquery?

A subquery is a query inside another query. It is used to retrieve data that will be used in the main query. Subqueries help you perform complex operations by breaking them down into smaller, manageable parts.

Subqueries are typically used in the WHERE, FROM, and SELECT clauses to filter, transform, and manipulate data dynamically.

2. Types of Subqueries

• Single-row Subquery: Returns only one row of results, and the comparison in the main query uses a single value.
• Multiple-row Subquery: Returns multiple rows of results, often used with operators like IN, ANY, or ALL.
• Correlated Subquery: A subquery that depends on the outer query. The subquery uses values from the outer query.
• Non-correlated Subquery: A subquery that can be executed independently of the outer query.

3. Using Subqueries in SELECT Statements

Subqueries are often used in the SELECT clause to return a value that is used in the main query.

Example:

Let’s assume we have a Sales table with columns SalesID, ProductID, Amount, and SaleDate. We want to find the products with the highest sales amount.

Query: To get the highest sale amount from the Sales table:

SELECT ProductID, Amount
FROM Sales
WHERE Amount = (SELECT MAX(Amount) FROM Sales);

Explanation: The subquery SELECT MAX(Amount) FROM Sales is executed first to find the maximum sale amount, and then the main query selects the products with that amount.

4. Using Subqueries in the WHERE Clause

Subqueries in the WHERE clause help filter records based on the results of another query. They are useful when you want to filter data based on a dynamic condition.

Example:

Query: To find the sales where the amount is greater than the average sale amount:

SELECT ProductID, Amount
FROM Sales
WHERE Amount > (SELECT AVG(Amount) FROM Sales);

Explanation: The subquery SELECT AVG(Amount) FROM Sales calculates the average sales amount, and the main query selects the sales that are greater than this average.

5. Using Subqueries with IN, ANY, and ALL Operators

Subqueries can also be used with operators like IN, ANY, and ALL to perform more complex filtering based on multiple values.

Example with IN:

Query: To find products that were sold in the same month as the highest sale amount:

SELECT ProductID
FROM Sales
WHERE SaleDate IN (SELECT SaleDate FROM Sales WHERE Amount = (SELECT MAX(Amount) FROM Sales));

Example with ANY:

Query: To find products with a sale amount greater than any sale amount in the Sales table:

SELECT ProductID
FROM Sales
WHERE Amount > ANY (SELECT Amount FROM Sales WHERE Amount < 100);

Example with ALL:

Query: To find products with a sale amount greater than all sales in the Sales table:

SELECT ProductID
FROM Sales
WHERE Amount > ALL (SELECT Amount FROM Sales WHERE Amount < 100);

6. Using Correlated Subqueries

A correlated subquery is a subquery that depends on the outer query. It uses values from the outer query to complete its execution.

Example:

Query: To find products where the sale amount is greater than the average sale amount for that specific product:

SELECT ProductID, Amount
FROM Sales outer
WHERE Amount > (SELECT AVG(Amount) FROM Sales inner WHERE inner.ProductID = outer.ProductID);

Explanation: The inner query refers to the outer.ProductID, making it a correlated subquery.

7. Using Subqueries in INSERT, UPDATE, and DELETE Statements

Subqueries can also be used in the INSERT, UPDATE, and DELETE statements to dynamically insert, update, or delete records based on another query's results.

INSERT Example:

Query: To insert a new record into the Products table, using the maximum price from the Sales table:

INSERT INTO Products (ProductID, ProductName, Price)
SELECT 101, 'New Product', MAX(Amount)
FROM Sales;

UPDATE Example:

Query: To increase the price of all products that have been sold for more than the average sale amount:

UPDATE Products
SET Price = Price * 1.10
WHERE ProductID IN (SELECT ProductID FROM Sales WHERE Amount > (SELECT AVG(Amount) FROM Sales));

DELETE Example:

Query: To delete products that have not been sold:

DELETE FROM Products
WHERE ProductID NOT IN (SELECT ProductID FROM Sales);

8. Practice Exercises

Try these exercises to practice using subqueries:

• Write a query to find products with the lowest sale amount using a subquery.
• Write a query to find customers who made a purchase amount greater than the average purchase amount in the Orders table.
• Write a query to increase the price of all products sold in the last month by 10%.

9. Conclusion

In this lesson, you learned how to use SQL subqueries to make your queries more dynamic and efficient. You explored how to use subqueries in the SELECT, WHERE, and other clauses, and learned about correlated and non-correlated subqueries.

Key Takeaways:

• Subqueries help break complex queries into smaller, manageable parts.
• Correlated subqueries depend on values from the outer query.
• Non-correlated subqueries can be executed independently.
• Subqueries can be used in INSERT, UPDATE, and DELETE statements.