RDBMS Subqueries

Subqueries in RDBMS

As you learn SQL, you will come across situations where one query depends on the result of another query. Instead of running two separate SQL statements, you can write one query inside another. This is called a subquery.

A subquery is a query nested inside another SQL query. The inner query runs first and returns a result, which the outer query then uses to complete its task.

Subqueries make SQL more powerful and help solve complex problems with fewer steps.

What is a Subquery?

A subquery is an SQL query placed inside another SQL statement. It is usually enclosed within parentheses.

The inner query is executed first, and its result is passed to the outer query.

SELECT column_name
FROM table_name
WHERE column_name =
(
    SELECT column_name
    FROM another_table
);

Although this may look confusing at first, it becomes easy to understand with examples.

Why Do We Need Subqueries?

Imagine a school database. You want to find the student who scored the highest marks.

One way is to first find the highest marks and then search for the student separately. A subquery combines both steps into a single SQL statement.

Subqueries help:

• Simplify complex queries.
• Reduce the need for multiple SQL statements.
• Improve readability in many situations.
• Retrieve data based on calculated results.

Simple Subquery Example

Suppose you have a Students table.

To display the student with the highest marks:

SELECT Name, Marks
FROM Students
WHERE Marks =
(
    SELECT MAX(Marks)
    FROM Students
);

How it Works

The inner query runs first:

SELECT MAX(Marks)
FROM Students;

This returns 92. The outer query then becomes:

SELECT Name, Marks
FROM Students
WHERE Marks = 92;

The database performs both steps automatically.

Subquery with WHERE Clause

Subqueries are often used with the WHERE clause.

Suppose you have an Employees table.

To display employees earning more than the average salary:

SELECT Name, Salary
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

The inner query calculates the average salary. The outer query displays only employees whose salary is greater than that average.

Subquery with IN Operator

The IN operator is useful when the subquery returns multiple values.

Customers

Orders

To display customers who have placed orders:

SELECT Name
FROM Customers
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
);

The inner query returns customer IDs from the Orders table. The outer query displays the names of those customers.

Types of Subqueries

There are different kinds of subqueries depending on the result they return.

Single-Row Subquery

A single-row subquery returns only one value.

SELECT MAX(Marks)
FROM Students;

Functions such as MAX(), MIN(), and AVG() often produce a single-row subquery.

Multiple-Row Subquery

A multiple-row subquery returns more than one value.

SELECT CustomerID
FROM Orders;

When multiple values are returned, operators like IN, ANY, or ALL are commonly used.

Best Practices

When writing subqueries, follow these tips:

• Keep the inner query simple and easy to understand.
• Test the inner query separately before combining it with the outer query.
• Use meaningful table and column names.
• Choose the correct operator, such as =, IN, or EXISTS, depending on the result returned.
• Avoid unnecessary nesting, because deeply nested queries can become harder to read and maintain.