Why Subqueries and nested queries in DBMS Theory? - Purpose & Use Cases

The Big Idea

What if you could ask complex questions to your database in just one simple step?

The Scenario

Imagine you want to find all customers who bought products costing more than the average price of all products in a store.

Without subqueries, you'd have to manually calculate the average price first, then use that number to filter customers.

The Problem

This manual approach is slow and error-prone because you must run multiple separate queries and keep track of intermediate results yourself.

It's easy to make mistakes or miss updates if the data changes.

The Solution

Subqueries let you write one query inside another, so the database does all the work in one step.

This makes your queries simpler, faster, and less likely to have errors.

Before vs After

✗ Before

SELECT * FROM customers WHERE product_price > 50;  -- 50 is manually calculated average

✓ After

SELECT * FROM customers WHERE product_price > (SELECT AVG(price) FROM products);

What It Enables

Subqueries enable powerful, dynamic filtering and data retrieval without extra manual calculations.

Real Life Example

Finding employees who earn more than the average salary in their department using a single query.

Key Takeaways

Manual multi-step queries are slow and risky.

Subqueries combine steps into one clear query.

This improves accuracy and efficiency in data handling.