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.

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query