Scalar Subqueries

Scalar subqueries are one of the most powerful and frequently used features of SQL. They allow for embedding queries within other queries to fetch a single value.

What Are Scalar Subqueries?

Definition

A scalar subquery is a subquery that returns exactly one value (one row with one column). This single value can then be used as an input for other parts of an SQL query, such as in a SELECT list, a WHERE clause, or even within expressions.

Importance in SQL

Scalar subqueries are essential for:

Dynamically retrieving values from related tables.
Simplifying complex queries.
Replacing hardcoded values with dynamically calculated ones.

Syntax of Scalar Subqueries

The syntax of scalar subqueries is straightforward:

				
					SELECT column1, (SELECT single_value_column FROM table WHERE condition) AS alias
FROM main_table;

Key Points:

The subquery must return only one value (one row, one column).
If the subquery returns multiple rows, SQL will throw an error unless aggregate functions like MAX, MIN, or AVG are used.

Where Can Scalar Subqueries Be Used?

In SELECT Clauses

Scalar subqueries can be used to calculate values dynamically in the SELECT statement.

Example:

				
					SELECT name, 
       (SELECT AVG(salary) FROM employees) AS avg_salary 
FROM employees;

Explanation:

The subquery calculates the average salary of all employees.
The main query displays each employee’s name along with this average salary.

Output:

Name	Salary
Alice	70000
John	80000

In INSERT Statements

Scalar subqueries can provide values for new rows.

Example:

				
					INSERT INTO high_earners (name, salary) 
VALUES ('Alice', (SELECT MAX(salary) FROM employees));

Explanation:

The subquery fetches the highest salary from the employees’ table.
Alice is inserted into the high_earners table with that salary.

In UPDATE Statements

Update values dynamically using scalar subqueries.

Example:

				
					UPDATE employees 
SET bonus = (SELECT AVG(bonus) FROM employees) 
WHERE department_id = 101;

Explanation:

The subquery calculates the average bonus.
Employees in department 101 receive this average bonus.

In DELETE Statements

Filter rows to delete based on scalar subquery conditions.

Example:

				
					DELETE FROM employees 
WHERE salary < (SELECT MIN(salary) FROM employees WHERE department_id = 102);

Explanation:

The subquery finds the lowest salary in department 102.
Employees earning less than this value are deleted.

Examples of Scalar Subqueries

Basic Example: Fetching a Constant Value

				
					SELECT name, 
       (SELECT 'Manager' FROM dual) AS role 
FROM employees;

Output:

Name	Role
Alice	Manager
Bob	Manager

Advanced Example: Nested Scalar Subqueries

				
					SELECT name, 
       (SELECT MAX(salary) 
        FROM employees 
        WHERE department_id = (SELECT department_id 
                               FROM departments 
                               WHERE name = 'HR')) AS max_salary_in_hr 
FROM employees;

Explanation:

The innermost query fetches the department ID for ‘HR’.
The middle query calculates the maximum salary in this department.
The outer query retrieves each employee’s name with the max salary.

Output:

Name	Max_Salary_in_HR
Alice	90000
Bob	90000