SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Scalar Subqueries

Learn SQL Scalar Subqueries in detail, understand how they return a single value, how they are used in SELECT, WHERE, HAVING, and ORDER BY clauses, and solve advanced SQL problems efficiently.

You've covered simple subqueries, nested ones, correlated ones, EXISTS, and ALL/ANY operators.

All these subqueries return different amounts of data:

text example

One Value
Multiple Values
Multiple Rows
Multiple Columns

But lots of operations need just one single value.

Think about:

Average salary
Maximum product price
Minimum student marks
Total number of orders

When a subquery returns exactly one value, it's called a Scalar Subquery.

These are super common because aggregate functions like AVG(), MAX(), MIN(), SUM(), and COUNT() typically return a single value.

Why "Scalar"?

In math and programming, scalar means a single value (not a vector or array).

Examples:

Output

10
25
100

In SQL:

sql example

SELECT AVG(Salary)
FROM Employees;

Returns:

Output

One value only. That's scalar.

Characteristics

A scalar subquery:

✅ Returns one row

✅ Returns one column

✅ Can go wherever a single value is expected

You can use it with:

sql example

=
>
<
>=
<=
<>

and arithmetic operations.

Basic Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

The subquery:

sql example

SELECT AVG(Salary)
FROM Employees;

Returns:

Output

The outer query becomes:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;

One scalar value drives the comparison.

How They Work

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Execution:

Example

Step 1: Calculate AVG(Price) ↓ Step 2: Return One Value ↓ Step 3: Outer Query Uses Value ↓ Final Result

The subquery runs first.

Visual Flow

text example

Subquery
     │
     ↓
Single Value
     │
     ↓
Outer Query
     │
     ↓
Result

Simple and direct.

Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);

Scalar Subquery with AVG()

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Subquery returns:

Output

Output:

EmployeeName
--------------
Amit
Neha

Scalar Subquery with MAX()

Find the highest-paid employee.

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

Subquery returns:

Output

Output:

EmployeeName
--------------
Neha

Scalar Subquery with MIN()

Find the lowest-paid employee.

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MIN(Salary)
    FROM Employees
);

Output:

EmployeeName
--------------
Rahul

Scalar Subquery with COUNT()

Query:

sql example

SELECT
(
    SELECT COUNT(*)
    FROM Employees
) AS TotalEmployees;

Output:

TotalEmployees
----------------
4

Returns a single numeric value.

Scalar in SELECT Clause

Put a scalar subquery directly in SELECT:

sql example

SELECT
EmployeeName,
(
    SELECT AVG(Salary)
    FROM Employees
) AS AverageSalary
FROM Employees;

Output:

EmployeeName	AverageSalary
Rahul	65000
Priya	65000
Amit	65000
Neha	65000

Each row shows the company average.

Scalar in WHERE Clause

Most common.

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Uses the scalar value as a filter.

Scalar in HAVING Clause

Filter grouped results:

sql example

SELECT DepartmentID,
       AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) >
(
    SELECT AVG(Salary)
    FROM Employees
);

Returns departments above company average.

Scalar in ORDER BY Clause

Uncommon but possible:

sql example

SELECT EmployeeName,
       Salary
FROM Employees
ORDER BY
(
    SELECT AVG(Salary)
    FROM Employees
);

Not usually recommended.

Scalar in INSERT

Insert the scalar value:

sql example

INSERT INTO EmployeeSummary
VALUES
(
    (
        SELECT COUNT(*)
        FROM Employees
    )
);

The scalar value is inserted.

Scalar in UPDATE

Update based on a condition:

sql example

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary <
(
    SELECT AVG(Salary)
    FROM Employees
);

Employees below average get a raise.

Real-World: E-Commerce

Want: Products above average price.

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Real-World: Banking

Want: Accounts above average balance.

Query:

sql example

SELECT AccountName
FROM Accounts
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
);

Real-World: HR

Want: Employees earning above company average.

Query:

sql example

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

Scalar with Strings

Scalars aren't just numbers. Strings work too:

sql example

SELECT *
FROM Products
WHERE CategoryName =
(
    SELECT CategoryName
    FROM Categories
    WHERE CategoryID = 5
);

Scalar with Dates

sql example

SELECT *
FROM Orders
WHERE OrderDate >
(
    SELECT MIN(OrderDate)
    FROM Orders
);

Returns orders after the earliest order date.

Error to Avoid: Multiple Rows

If your subquery returns more than one row, SQL will throw an error:

sql example

SELECT *
FROM Employees
WHERE Salary >
(
    SELECT Salary
    FROM Employees
);

This returns four salary values, not one. SQL rejects it.

Fix: Use an aggregate function to return one value, or use IN/ANY/EXISTS instead.

Performance

Scalar subqueries in SELECT clauses run once per row of the outer query. For large datasets, this can be slow.

Consider:

JOINs for better performance
Pre-calculation in aggregation

Key Takeaways

✅ Scalar subqueries return one value

✅ Work with aggregate functions (AVG, MAX, MIN, COUNT, SUM)

✅ Can appear in SELECT, WHERE, HAVING, ORDER BY

✅ Simple and direct

✅ Perfect for single-value comparisons

✅ Watch out for multiple-row subqueries (error)

✅ May be slow in SELECT (runs per row)

Use scalar subqueries for single-value logic. They're clean and readable.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, scalar, scalar subqueries

Continue learning this concept

SubqueriesANY and ALL OperatorsLearn SQL ANY and ALL Operators in detail, understand how they compare values against multiple rows returned by subqueries, and solve advanced filtering problems.SubqueriesCASE Expression in SQLLearn how to use the CASE expression in SQL for conditional logic within queries, understand simple and searched CASE syntax, and apply it in real-world scenarios.SubqueriesCorrelated SubqueryLearn SQL Correlated Subqueries in detail, understand row-by-row execution, how they differ from normal subqueries, and solve advanced business problems efficiently.SubqueriesEXISTS OperatorLearn SQL EXISTS Operator in detail, understand how it checks for the existence of rows in a subquery, improves query efficiency, and is used in real-world database applications.SubqueriesNested Subqueries in SQLLearn how to write nested subqueries (subqueries within subqueries) in SQL, understand multiple levels of nesting, and solve complex data retrieval problems.SubqueriesSubquery IntroductionLearn SQL Subqueries in detail, understand how queries can be nested inside other queries, and discover real-world use cases for filtering, comparisons, and advanced data retrieval.

PreviousNested Subqueries in SQL NextSubquery Introduction

SQL Notes

Scalar Subqueries

Learn SQL Scalar Subqueries in detail, understand how they return a single value, how they are used in SELECT, WHERE, HAVING, and ORDER BY clauses, and solve advanced SQL problems efficiently.

You've covered simple subqueries, nested ones, correlated ones, EXISTS, and ALL/ANY operators.

All these subqueries return different amounts of data:

text example

One Value
Multiple Values
Multiple Rows
Multiple Columns

But lots of operations need just one single value.

Think about:

Average salary
Maximum product price
Minimum student marks
Total number of orders

When a subquery returns exactly one value, it's called a Scalar Subquery.

These are super common because aggregate functions like AVG(), MAX(), MIN(), SUM(), and COUNT() typically return a single value.

Why "Scalar"?

In math and programming, scalar means a single value (not a vector or array).

Examples:

Output

10
25
100

In SQL:

sql example

SELECT AVG(Salary)
FROM Employees;

Returns:

Output

One value only. That's scalar.

Characteristics

A scalar subquery:

✅ Returns one row

✅ Returns one column

✅ Can go wherever a single value is expected

You can use it with:

sql example

=
>
<
>=
<=
<>

and arithmetic operations.

Basic Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

The subquery:

sql example

SELECT AVG(Salary)
FROM Employees;

Returns:

Output

The outer query becomes:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;

One scalar value drives the comparison.

How They Work

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Execution:

Example

Step 1: Calculate AVG(Price) ↓ Step 2: Return One Value ↓ Step 3: Outer Query Uses Value ↓ Final Result

The subquery runs first.

Visual Flow

text example

Subquery
     │
     ↓
Single Value
     │
     ↓
Outer Query
     │
     ↓
Result

Simple and direct.

Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);

Scalar Subquery with AVG()

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Subquery returns:

Output

Output:

EmployeeName
--------------
Amit
Neha

Scalar Subquery with MAX()

Find the highest-paid employee.

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

Subquery returns:

Output

Output:

EmployeeName
--------------
Neha

Scalar Subquery with MIN()

Find the lowest-paid employee.

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MIN(Salary)
    FROM Employees
);

Output:

EmployeeName
--------------
Rahul

Scalar Subquery with COUNT()

Query:

sql example

SELECT
(
    SELECT COUNT(*)
    FROM Employees
) AS TotalEmployees;

Output:

TotalEmployees
----------------
4

Returns a single numeric value.

Scalar in SELECT Clause

Put a scalar subquery directly in SELECT:

sql example

SELECT
EmployeeName,
(
    SELECT AVG(Salary)
    FROM Employees
) AS AverageSalary
FROM Employees;

Output:

EmployeeName	AverageSalary
Rahul	65000
Priya	65000
Amit	65000
Neha	65000

Each row shows the company average.

Scalar in WHERE Clause

Most common.

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Uses the scalar value as a filter.

Scalar in HAVING Clause

Filter grouped results:

sql example

SELECT DepartmentID,
       AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) >
(
    SELECT AVG(Salary)
    FROM Employees
);

Returns departments above company average.

Scalar in ORDER BY Clause

Uncommon but possible:

sql example

SELECT EmployeeName,
       Salary
FROM Employees
ORDER BY
(
    SELECT AVG(Salary)
    FROM Employees
);

Not usually recommended.

Scalar in INSERT

Insert the scalar value:

sql example

INSERT INTO EmployeeSummary
VALUES
(
    (
        SELECT COUNT(*)
        FROM Employees
    )
);

The scalar value is inserted.

Scalar in UPDATE

Update based on a condition:

sql example

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary <
(
    SELECT AVG(Salary)
    FROM Employees
);

Employees below average get a raise.

Real-World: E-Commerce

Want: Products above average price.

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Real-World: Banking

Want: Accounts above average balance.

Query:

sql example

SELECT AccountName
FROM Accounts
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
);

Real-World: HR

Want: Employees earning above company average.

Query:

sql example

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

Scalar with Strings

Scalars aren't just numbers. Strings work too:

sql example

SELECT *
FROM Products
WHERE CategoryName =
(
    SELECT CategoryName
    FROM Categories
    WHERE CategoryID = 5
);

Scalar with Dates

sql example

SELECT *
FROM Orders
WHERE OrderDate >
(
    SELECT MIN(OrderDate)
    FROM Orders
);

Returns orders after the earliest order date.

Error to Avoid: Multiple Rows

If your subquery returns more than one row, SQL will throw an error:

sql example

SELECT *
FROM Employees
WHERE Salary >
(
    SELECT Salary
    FROM Employees
);

This returns four salary values, not one. SQL rejects it.

Fix: Use an aggregate function to return one value, or use IN/ANY/EXISTS instead.

Performance

Scalar subqueries in SELECT clauses run once per row of the outer query. For large datasets, this can be slow.

Consider:

JOINs for better performance
Pre-calculation in aggregation

Key Takeaways

✅ Scalar subqueries return one value

✅ Work with aggregate functions (AVG, MAX, MIN, COUNT, SUM)

✅ Can appear in SELECT, WHERE, HAVING, ORDER BY

✅ Simple and direct

✅ Perfect for single-value comparisons

✅ Watch out for multiple-row subqueries (error)

✅ May be slow in SELECT (runs per row)

Use scalar subqueries for single-value logic. They're clean and readable.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, scalar, scalar subqueries

Continue learning this concept

PreviousNested Subqueries in SQL NextSubquery Introduction