Subqueries and nested queries in DBMS Theory - Full Explanation

Introduction

When working with databases, sometimes you need to ask a question that depends on the answer to another question. This is where subqueries and nested queries come in handy. They let you use one query inside another to get more precise results.

Explanation

What is a Subquery

A subquery is a query placed inside another query. It runs first and provides a result that the outer query uses. This helps break down complex questions into smaller parts that the database can handle step-by-step.

A subquery is a query inside another query that runs first to help the main query.

Types of Subqueries

Subqueries can appear in different parts of a query, like in the WHERE clause to filter results, or in the SELECT clause to calculate values. They can return single values, lists, or even tables depending on how they are used.

Subqueries can be used in various parts of a query and return different types of results.

Nested Queries

Nested queries are subqueries placed inside other subqueries, creating multiple layers. Each inner query runs first, and its result feeds into the next outer query. This allows very detailed and specific data retrieval.

Nested queries are multiple layers of subqueries, each feeding results to the next.

When to Use Subqueries

Use subqueries when you need to filter or calculate data based on another query’s result. They simplify complex logic and make queries easier to read and maintain compared to writing one large query.

Subqueries simplify complex data retrieval by breaking it into smaller, manageable parts.

Real World Analogy

Imagine you want to find the best restaurant in a city, but first, you need to find all restaurants that have good reviews. You first make a list of good-reviewed restaurants, then from that list, you pick the best one. This step-by-step approach is like using subqueries.

Subquery → Making a list of good-reviewed restaurants before choosing the best

Types of Subqueries → Using the list to filter by location or to calculate average ratings

Nested Queries → Making a list of good-reviewed restaurants, then from that list, making another list of affordable ones

When to Use Subqueries → Breaking down the decision into smaller steps to find the best restaurant easily

Diagram

Diagram

┌─────────────┐
│ Outer Query │
│ (Main task) │
└─────┬───────┘
      │ uses result from
┌─────▼───────┐
│ Subquery 1  │
│ (First step)│
└─────┬───────┘
      │ uses result from
┌─────▼───────┐
│ Subquery 2  │
│ (Inner step)│
└─────────────┘

This diagram shows how nested queries work with inner subqueries feeding results to outer queries.

Key Facts

Subquery → A query embedded inside another query that runs first.

Nested Query → A subquery placed inside another subquery, creating multiple layers.

WHERE Clause Subquery → A subquery used to filter rows based on its result.

SELECT Clause Subquery → A subquery used to calculate or return values for each row.

Single-row Subquery → A subquery that returns only one value.

Multi-row Subquery → A subquery that returns multiple values or rows.

Code Example

DBMS Theory

SELECT employee_name FROM employees WHERE department_id = (
  SELECT department_id FROM departments WHERE department_name = 'Sales'
);

Common Confusions

Believing subqueries always return multiple rows.

Believing subqueries always return multiple rows. Subqueries can return a single value, multiple rows, or even a table depending on their use.

Thinking nested queries are inefficient and should be avoided.

Thinking nested queries are inefficient and should be avoided. Nested queries can be efficient and useful; proper indexing and query design help maintain performance.

Assuming subqueries and joins do the same thing.

Assuming subqueries and joins do the same thing. Subqueries and joins can sometimes achieve similar results, but they work differently and are suited for different situations.

Summary

Subqueries let you use one query inside another to break complex questions into smaller steps.

Nested queries are multiple layers of subqueries, each providing data to the next.

Using subqueries helps write clearer and more manageable database queries.