Scalar subqueries in MySQL

Introduction

Scalar subqueries let you use a small query inside another query to get a single value. This helps you compare or calculate values easily.

When you want to find a value from another table to use in a condition.

When you need to calculate a value on the fly for each row in your main query.

When you want to compare a column to a single value returned by another query.

When you want to add a calculated column based on a query result.

When you want to filter rows based on a single value from another query.

Syntax

MySQL

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

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

If the subquery returns more than one value, MySQL will give an error.

Examples

This gets each player's name and the highest score from the scores table.

MySQL

SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM players;

This shows each employee's salary and the average salary of all employees.

MySQL

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

This adds the discount for each product by looking it up in the discounts table.

MySQL

SELECT product_name, price, (SELECT discount FROM discounts WHERE product_id = products.id LIMIT 1) AS discount FROM products;

Sample Program

This query lists each employee's name and salary, and uses a scalar subquery to find the department name for each employee.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), department_id INT, salary INT);
INSERT INTO employees VALUES (1, 'Alice', 10, 5000), (2, 'Bob', 20, 6000), (3, 'Charlie', 10, 5500);
CREATE TABLE departments (id INT, name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'HR');

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

Important Notes

Scalar subqueries must return only one value. If they return zero rows, the result is NULL.

They are useful for adding extra info from another table without joining.

Summary

Scalar subqueries return a single value to use inside another query.

They help compare or add calculated values easily.

Make sure the subquery returns exactly one value to avoid errors.