ALL, ANY, SOME with subqueries in PostgreSQL

Introduction

These keywords help you compare a value to a list of values from another query. They make it easy to check if a value matches all, any, or some of the results from a subquery.

You want to find products priced higher than all prices in a certain category.

You want to check if a student's score is greater than any score in a test.

You want to find employees whose salary is less than some salaries in another department.

You want to filter records based on comparisons with a list of values from another table.

Syntax

PostgreSQL

value operator ALL (subquery)
value operator ANY (subquery)
value operator SOME (subquery)

ALL means the condition must be true for every value returned by the subquery.

ANY and SOME mean the condition must be true for at least one value returned by the subquery. They are interchangeable.

Examples

Selects products priced higher than every book's price.

PostgreSQL

SELECT product_name FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

Selects students whose score is at least equal to any score from test 1.

PostgreSQL

SELECT student_name FROM students WHERE score >= ANY (SELECT score FROM tests WHERE test_id = 1);

Selects employees earning less than some employees in Sales.

PostgreSQL

SELECT employee_name FROM employees WHERE salary < SOME (SELECT salary FROM employees WHERE department = 'Sales');

Sample Program

This creates a products table, adds some items, then finds products priced higher than every book.

PostgreSQL

CREATE TABLE products (product_id SERIAL PRIMARY KEY, product_name TEXT, category TEXT, price NUMERIC);

INSERT INTO products (product_name, category, price) VALUES
('Book A', 'Books', 10),
('Book B', 'Books', 15),
('Pen', 'Stationery', 5),
('Notebook', 'Stationery', 12),
('Lamp', 'Electronics', 20);

-- Find products priced higher than all books
SELECT product_name FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

Important Notes

ALL requires the condition to be true for every value in the subquery result.

ANY and SOME are the same and require the condition to be true for at least one value.

Use these to compare a single value against multiple values returned by a subquery.

Summary

ALL checks if a condition holds for every value from a subquery.

ANY and SOME check if a condition holds for at least one value.

They help compare values easily without writing complex joins or loops.