Single-Row Subquery
Learn how to use a subquery that returns only one row or one value.
What is a Single-Row Subquery?
A Single-Row Subquery is a subquery that returns only one row or one value as its result.
Because the subquery returns a single value, we can commonly use comparison operators such as =, >, <, >= and <=.
Basic Syntax
SELECT column_name
FROM table_name
WHERE column_name = (
SELECT column_name
FROM table_name
WHERE condition
);
The inner query should return only one value when using a single-value comparison operator.
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 30000 |
| 2 | Priya | HR | 45000 |
| 3 | Amit | IT | 50000 |
| 4 | Sneha | Finance | 40000 |
Example 1: Using = Operator
Suppose we want to find employees who have the same salary as Priya.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (
SELECT Salary
FROM Employees
WHERE EmployeeName = 'Priya'
);
How it works
- The inner query finds Priya's salary.
- The inner query returns one value.
- The outer query finds employees whose salary matches that value.
Example 2: Using > Operator
Find employees whose salary is greater than Rahul's salary.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
SELECT Salary
FROM Employees
WHERE EmployeeName = 'Rahul'
);
The inner query returns Rahul's salary. The outer query then returns employees whose salary is greater than Rahul's salary.
Example 3: Using Aggregate Function
Aggregate functions such as MAX(), MIN() and AVG() normally return a single value, making them useful with single-row subqueries.
Find employees whose salary is equal to the highest salary in the table:
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (
SELECT MAX(Salary)
FROM Employees
);
The inner query returns the highest salary. The outer query finds the employee having that salary.
Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | Salary = (Subquery) |
| > | Greater than | Salary > (Subquery) |
| < | Less than | Salary < (Subquery) |
| >= | Greater than or equal to | Salary >= (Subquery) |
| <= | Less than or equal to | Salary <= (Subquery) |
Important Points
- A single-row subquery returns one row or one value.
- It can commonly be used with comparison operators.
- The inner query is normally enclosed in parentheses.
- Aggregate functions such as MAX(), MIN() and AVG() can return a single value.
- If a subquery returns multiple rows while using a single-value operator such as =, SQL Server can generate an error.
Summary
A Single-Row Subquery returns one row or one value and is commonly used with comparison operators such as =, >, <, >= and <=. It is useful when the result of one query is required for comparison in another query.