Scalar subquery in SELECT in SQL

Introduction

A scalar subquery in SELECT lets you get a single value from another table or query to use in each row of your main query. It helps add extra info without joining tables.

When you want to show a related value from another table for each row, like the latest order date for each customer.

When you need to calculate a small summary value, like the average score, to show alongside each record.

When you want to add a single value result from a query inside your main query without making it complex.

When you want to compare each row's value with a single value from another query.

Syntax

SQL

SELECT column1, (SELECT single_value FROM table2 WHERE condition) AS alias_name FROM table1;

The subquery inside SELECT must return exactly one value (one row, one column).

If the subquery returns no rows, the result is NULL for that row.

Examples

Get each student's name and their highest test score using a scalar subquery.

SQL

SELECT name, (SELECT MAX(score) FROM tests WHERE tests.student_id = students.id) AS max_score FROM students;

Show each product with the total number of sales it has.

SQL

SELECT product_name, (SELECT COUNT(*) FROM sales WHERE sales.product_id = products.id) AS total_sales FROM products;

Add the department name for each employee using a scalar subquery.

SQL

SELECT employee_name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS dept FROM employees;

Sample Program

This query lists each employee's name and their department name by using a scalar subquery in the SELECT clause.

SQL

CREATE TABLE employees (id INT, name VARCHAR(20), department_id INT);
CREATE TABLE departments (id INT, department_name VARCHAR(20));

INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO departments VALUES (10, 'HR'), (20, 'Sales');

SELECT name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS department FROM employees ORDER BY id;

Important Notes

Scalar subqueries must return only one value; otherwise, SQL will give an error.

If the subquery finds no matching row, the result is NULL for that row in the main query.

Scalar subqueries can be slower than joins for large data, so use them wisely.

Summary

Scalar subqueries in SELECT let you add one value from another query for each row.

They must return exactly one value (one row, one column).

Useful for adding related info without complex joins.