Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Single Row Subqueries
VideosDiscussion
In many real world database scenarios, decisions are based on a single calculated value. For example, a company may want to find employees earning more than the company’s average salary, or products priced below the cheapest product in a specific category. In such cases, the comparison is always made against one final value, not a list of values.

SQL handles these scenarios using single-row subqueries. These subqueries return exactly one row and one column, making them perfect for comparisons using operators like =, <, and >.


What Is a Single-Row Subquery?

A single-row subquery is a subquery that:

  • Returns only one value
  • Is commonly used in the WHERE clause
  • Works with comparison operators such as:
                1. = (equal to)
                2. < (less than)
                3. > (greater than)

The inner query runs first, produces one value, and the outer query uses that value to filter results.


Why Single-Row Subqueries Are Useful

Single-row subqueries are ideal when:

  • A value needs to be calculated dynamically
  • Hard-coding values is not practical
  • Business rules may change over time
Instead of writing fixed numbers in queries, SQL lets the database decide the value automatically.


Example - 1: Using > Operator

Find Employees Earning More Than Average Salary


employees

emp_namesalary
Amit40000
Neha55000
Rahul30000
Priya60000


SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

How This Works

  • Inner query calculates the average salary
  • Outer query compares each employee’s salary with that value
  • Only employees earning more than average are returned
This is one of the most common interview examples of subqueries.


Example - 2: Using < Operator

Find Products Cheaper Than the Maximum Price


products

productprice
Laptop60000
Tablet30000
Mobile45000


SELECT product, price
FROM products
WHERE price < (
    SELECT MAX(price)
    FROM products
);

Key Idea

  • Inner query returns the highest price
  • Outer query selects products cheaper than that price
Useful when comparing against extreme values like MAX or MIN.


Example - 3: Using = Operator

Find Employee with Minimum Salary

SELECT emp_name, salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

Why = Works Here

  • MIN(salary) returns only one value
  • Safe to use equality comparison
If the subquery returns more than one row, = will throw an error.


Where Single-Row Subqueries Are Commonly Used

Single-row subqueries are frequently used with:

  • Aggregate functions (AVG, MIN, MAX, COUNT)
  • Business rules (highest, lowest, average values)
  • Dynamic comparisons

Typical use cases include:

  • Top-earning employee
  • Lowest priced product
  • Salary above company average


Important Rules to Remember

  • Subquery must return exactly one row
  • Use = only when one value is guaranteed
  • If multiple rows are returned → error occurs
  • Aggregate functions usually ensure single-row output


Two Minute Drill

  • Single row subquery returns one value
  • Inner query runs first
  • Used with =, <, >
  • Best paired with aggregate functions
  • Error occurs if multiple rows are returned
  • Common in salary and price comparisons