Scalar subqueries in PostgreSQL

Introduction

Scalar subqueries let you use a small query inside another query to get a single value. This helps you get extra information without writing many separate queries.

When you want to find a value related to each row in a table, like the highest score for each player.

When you need to calculate a value on the fly, like the average price of products to compare with each product's price.

When you want to add extra details from another table without joining all rows, like showing the latest order date for each customer.

When you want to filter rows based on a single value from another query, like selecting employees whose salary is above the company average.

Syntax

PostgreSQL

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

The subquery inside parentheses 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 player's name and their highest game score using a scalar subquery.

PostgreSQL

SELECT name, (SELECT MAX(score) FROM games WHERE games.player_id = players.id) AS max_score FROM players;

Show each product's price and the average price of all products.

PostgreSQL

SELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_price FROM products;

Select employees who earn more than the average salary.

PostgreSQL

SELECT employee_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Sample Program

This example creates two tables: employees and departments. Then it selects each employee's name, salary, and their department's name using a scalar subquery.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department_id INT, salary INT);
INSERT INTO employees (name, department_id, salary) VALUES
('Alice', 1, 5000),
('Bob', 1, 6000),
('Charlie', 2, 5500);

CREATE TABLE departments (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO departments (name) VALUES ('HR'), ('IT');

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

Important Notes

Scalar subqueries must return only one value; otherwise, you get an error.

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

Scalar subqueries are useful for adding calculated or related values without complex joins.

Summary

Scalar subqueries return a single value used inside another query.

They help get related or calculated data for each row simply.

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