SQL Topics
Scalar Subqueries
title: Scalar Subqueries
In previous lessons, you learned about:
- Simple Subqueries
- Nested Subqueries
- Correlated Subqueries
- EXISTS Operator
- ANY and ALL Operators
All of these subqueries can return different amounts of data.
For example:
One Value
Multiple Values
Multiple Rows
Multiple ColumnsHowever, many SQL operations require only a single value.
Examples:
- Average salary
- Maximum product price
- Minimum student marks
- Total number of orders
When a subquery returns exactly one value, it is called a Scalar Subquery.
Scalar Subqueries are among the most commonly used subqueries because aggregate functions such as AVG(), MAX(), MIN(), SUM(), and COUNT() typically return a single value.
What is a Scalar Subquery?
A Scalar Subquery is a subquery that returns:
Exactly One Row
and
Exactly One ColumnResult:
Single ValueExamples:
50000
100
7500.50
'Electronics'All of these are scalar values.
Why is it Called Scalar?
In mathematics and programming:
Scalar
=
Single ValueExamples:
10
25
100are scalar values.
Similarly:
SELECT AVG(Salary)
FROM Employees;returns:
65000One value only.
Therefore, it is a scalar subquery.
Characteristics of Scalar Subqueries
A scalar subquery:
✅ Returns one row
✅ Returns one column
✅ Can be used wherever a value is expected
Examples:
=
>
<
>=
<=
<>and arithmetic expressions.
Basic Scalar Subquery Example
Query:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Subquery:
SELECT AVG(Salary)
FROM Employees;Result:
65000Outer Query becomes:
SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;The scalar value drives the comparison.
How Scalar Subqueries Work
Example:
SELECT ProductName
FROM Products
WHERE Price >
(
SELECT AVG(Price)
FROM Products
);Execution:
The subquery executes first.
Visual Representation
Subquery
│
▼
Single Value
│
▼
Outer Query
│
▼
ResultThis is the essence of a scalar subquery.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert records:
INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);Scalar Subquery with AVG()
Query:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Subquery Result:
65000Output:
| EmployeeName |
|---|
| ------------- |
| Amit |
| Neha |
Scalar Subquery with MAX()
Find the highest-paid employee.
Query:
SELECT EmployeeName
FROM Employees
WHERE Salary =
(
SELECT MAX(Salary)
FROM Employees
);Subquery Result:
80000Output:
| EmployeeName |
|---|
| ------------- |
| Neha |
Scalar Subquery with MIN()
Find the lowest-paid employee.
Query:
SELECT EmployeeName
FROM Employees
WHERE Salary =
(
SELECT MIN(Salary)
FROM Employees
);Output:
| EmployeeName |
|---|
| ------------- |
| Rahul |
Scalar Subquery with COUNT()
Query:
SELECT
(
SELECT COUNT(*)
FROM Employees
) AS TotalEmployees;Output:
| TotalEmployees |
|---|
| --------------- |
| 4 |
The subquery returns a single numeric value.
Scalar Subquery in SELECT Clause
A scalar subquery can appear directly inside SELECT.
Example:
SELECT
EmployeeName,
(
SELECT AVG(Salary)
FROM Employees
) AS AverageSalary
FROM Employees;Output:
| EmployeeName | AverageSalary |
|---|---|
| Rahul | 65000 |
| Priya | 65000 |
| Amit | 65000 |
| Neha | 65000 |
Scalar Subquery in WHERE Clause
The most common usage.
Example:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);The scalar value is used as a filter.
Scalar Subquery in HAVING Clause
Example:
SELECT DepartmentID,
AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) >
(
SELECT AVG(Salary)
FROM Employees
);Departments above company average salary are returned.
Scalar Subquery in ORDER BY Clause
Example:
SELECT EmployeeName,
Salary
FROM Employees
ORDER BY
(
SELECT AVG(Salary)
FROM Employees
);Although uncommon, scalar subqueries can appear here as well.
Scalar Subquery in INSERT
Example:
INSERT INTO EmployeeSummary
VALUES
(
(
SELECT COUNT(*)
FROM Employees
)
);The scalar value is inserted.
Scalar Subquery in UPDATE
Example:
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary <
(
SELECT AVG(Salary)
FROM Employees
);Employees below average receive a raise.
Real-World Example: E-Commerce
Requirement:
Products Above Average PriceQuery:
SELECT ProductName
FROM Products
WHERE Price >
(
SELECT AVG(Price)
FROM Products
);Real-World Example: Education System
Requirement:
Students Scoring Above AverageQuery:
SELECT StudentName
FROM Students
WHERE Marks >
(
SELECT AVG(Marks)
FROM Students
);Real-World Example: Banking
Requirement:
Accounts With Balance
Above Branch AverageScalar values from aggregate calculations are often used.
Scalar Subquery vs Simple Subquery
Simple Subquery:
May Return
One Value
or
Multiple ValuesScalar Subquery:
Must Return
Exactly One ValueComparison Table
| Feature | Scalar Subquery | Regular Subquery |
|---|---|---|
| One Row | Yes | Optional |
| One Column | Yes | Optional |
| One Value | Yes | Optional |
| Aggregate Functions | Common | Optional |
| Used as Value | Yes | Not Always |
Common Aggregate Functions Used
Scalar subqueries frequently use:
AVG()
MAX()
MIN()
SUM()
COUNT()because these functions normally return a single value.
Performance Considerations
Scalar subqueries are usually efficient because:
One Value ReturnedHowever:
Correlated Scalar Subqueriesmay execute repeatedly and become expensive.
Performance depends on:
- Table Size
- Indexes
- Query Complexity
- Database Engine
Common Errors
Returning Multiple Rows
Wrong:
SELECT EmployeeName
FROM Employees
WHERE Salary =
(
SELECT Salary
FROM Employees
);If multiple salaries exist:
Error:
Subquery Returns More Than One RowReturning Multiple Columns
Wrong:
SELECT *
FROM Employees
WHERE Salary =
(
SELECT Salary,
DepartmentID
FROM Employees
);A scalar subquery must return only one column.
Forgetting Aggregate Functions
Many developers expect one value but accidentally return multiple rows.
Using Scalar Subqueries Unnecessarily
Sometimes a JOIN is simpler and faster.
Best Practices
Ensure One Value is Returned
Use:
AVG()
MAX()
MIN()
COUNT()
SUM()when appropriate.
Test Subqueries Independently
Run the subquery first.
Use Meaningful Aliases
Improves readability.
Avoid Deep Nesting
Keep queries maintainable.
Consider JOIN Alternatives
Some scenarios perform better with JOINs.
Common Interview Questions
What is a Scalar Subquery?
A subquery that returns exactly one row and one column.
Why is it called scalar?
Because it returns a single value.
Which functions commonly create scalar subqueries?
AVG()
MAX()
MIN()
SUM()
COUNT()Can scalar subqueries be used in SELECT?
Yes.
They can be used anywhere a single value is expected.
What happens if multiple rows are returned?
Most databases raise an error.
Summary
Scalar Subqueries are one of the most widely used SQL subquery types because they return a single value that can be used in comparisons, calculations, filtering, reporting, and data manipulation.
In this lesson, you learned:
- What a Scalar Subquery is
- Why it is important
- How it works
- Scalar subqueries in SELECT
- Scalar subqueries in WHERE
- Scalar subqueries in HAVING
- Scalar subqueries in UPDATE
- Aggregate functions
- Real-world examples
- Common mistakes
- Best practices
Mastering Scalar Subqueries is essential because they form the foundation of many advanced SQL calculations and reporting queries.
Next Step
Continue to the next lesson:
Common Table Expressions (CTE) →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Scalar Subqueries.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, subqueries, scalar
Related SQL Topics