Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Multi Row Subqueries
VideosDiscussion
Real world databases rarely return just a single value. Most of the time, we deal with lists of values, such as:

  • Employees working in multiple departments
  • Products belonging to several categories
  • Customers from different cities
  • Orders placed across multiple years
In such cases, a single-row subquery is not sufficient. We need a mechanism where the outer query can compare its values against multiple rows returned by a subquery.

This is exactly where multi-row subqueries come into play.


What Is a Multi-Row Subquery?

  • A multi-row subquery is a subquery that:
  • Returns more than one row
  • Is commonly used in the WHERE clause
  • Works with special operators like:
              1. IN
              2. ANY
              3. ALL

Instead of comparing with a single value, the outer query compares its value with a set of values.


Why Normal Operators Do not Work Here

Consider this subquery:

SELECT department_id
FROM employees;

This returns multiple department IDs.


If you try

WHERE department_id = (subquery)

  • SQL throws an error because = expects only one value.
  • That is why SQL provides IN, ANY, and ALL.


Using IN Operator

The Most Common Multi-Row Subquery
The IN operator checks whether a value matches any value in the list returned by the subquery.


Example: Employees Working in IT and HR Departments

SELECT emp_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'HR')
);

How This Works

  • Subquery returns multiple department IDs
  • Outer query checks if an employee belongs to any one of them
  • Simple, readable, and very popular
 IN is the safest and most commonly used multi-row operator.


Using ANY Operator

Compare With At Least One Value
The ANY operator compares a value with any one value returned by the subquery.

It is often used with:

  • >
  • <
  • >=
  • <=


Example: Employees Earning More Than Any IT Employee

SELECT emp_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

  • Subquery returns salaries of IT employees
  • If an employee earns more than at least one IT employee - selected
Even beating the lowest salary in the IT department is enough.


Using ALL Operator

Compare With Every Value
The ALL operator compares a value against all values returned by the subquery. This is stricter than ANY.


Example: Employees Earning More Than All IT Employees

SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

  • Subquery returns all IT salaries
  • Employee must earn more than the highest IT salary
  • Only top earners qualify
ALL is commonly used to find extreme records.


Difference Between IN, ANY and ALL

OperatorMeaningSelection Rule
INMatch any valueEqual to at least one value
ANYCompare with anyTrue for one match
ALLCompare with allMust satisfy all values


Real-World Scenarios

Multi-row subqueries are useful when:

  • Filtering data across multiple categories
  • Comparing salaries, prices, or scores
  • Handling grouped or departmental data
  • Writing dynamic and scalable queries

Common examples:

  • Employees from selected departments
  • Products cheaper than competitors
  • Students scoring higher than others


Two Minute Drill

  • Multirow subqueries return multiple rows
  • Used with IN, ANY and ALL
  • IN is the most common and beginner friendly
  • ANY checks against at least one value
  • ALL checks against every value
  • Essential for real world SQL logic and interviews