PostgreSQL - ALL Operator

PostgreSQL ALL Operator

The PostgreSQL ALL operator is a powerful comparison tool that allows you to compare a single value to every value in a list or a subquery result. It returns true if your comparison holds true for all elements in the set; otherwise, it returns false.

Syntax

The ALL operator is used within a WHERE or HAVING clause:

expression operator ALL (subquery | ARRAY[...])

Where:

• expression is the value you are testing.
• operator is a standard comparison operator such as =, <>, >, <, >=, or <=`.
• The set can be a subquery that returns a single column, or an explicit ARRAY constructor.

Practical Example: Candidate Skill Assessment

Imagine a hiring scenario where we need to find candidates who meet strict criteria across multiple assessment scores. The ALL operator is perfect for this.

First, let’s create and populate our candidates table.

DROP TABLE IF EXISTS candidates;
CREATE TABLE candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    experience_years INT,
    assessment_scores INT[] -- An array of integer scores
);

INSERT INTO candidates (full_name, experience_years, assessment_scores) VALUES
('Alice Johnson', 5, ARRAY[85, 90, 95]),
('Bob Smith', 7, ARRAY[88, 92, 99]),
('Charlie Brown', 2, ARRAY[60, 75, 80]),
('Diana Prince', 6, ARRAY[70, 85, 88]),
('Edward King', 4, ARRAY[55, 65, 70]),
('Fiona Glenanne', 10, ARRAY[95, 98, 100]);

Query 1: Find candidates where ALL scores are above 80

We want to find elite candidates whose every single assessment score is greater than 80. The query 80 < ALL(assessment_scores) translates to: “80 is less than the first score AND 80 is less than the second score AND…”

SELECT full_name, assessment_scores
FROM candidates
WHERE 80 < ALL(assessment_scores);

This query produces the following result:

   full_name    | assessment_scores
----------------+-------------------
 Alice Johnson  | {85,90,95}
 Bob Smith      | {88,92,99}
 Fiona Glenanne | {95,98,100}
(3 rows)

Query 2: Using a Subquery with ALL

A more common use case for ALL is with a subquery. Let’s create another table, products, to demonstrate this. We want to find all products in the ‘Premium’ category that are more expensive than every single product in the ‘Standard’ category.

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (product_name, category, price) VALUES
('Standard Keyboard', 'Standard', 25.00),
('Standard Mouse', 'Standard', 15.00),
('Premium Keyboard', 'Premium', 90.00),
('Premium Mouse', 'Premium', 75.00),
('Ultra-Premium Keyboard', 'Premium', 120.00);

The subquery (SELECT price FROM products WHERE category = 'Standard') returns the set {25.00, 15.00}. The outer query checks which ‘Premium’ products have a price greater than both of these values.

SELECT product_name, price
FROM products
WHERE category = 'Premium'
  AND price > ALL (SELECT price FROM products WHERE category = 'Standard');

Result:

      product_name      | price
------------------------+--------
 Premium Keyboard       |  90.00
 Premium Mouse          |  75.00
 Ultra-Premium Keyboard | 120.00
(3 rows)

Comparison with Other Operators

• ALL vs ANY/SOME: ANY returns true if the comparison is true for at least one value in the set. ALL requires all values to satisfy the condition. > ANY means ‘greater than the minimum’, while > ALL means ‘greater than the maximum’.
• ALL vs IN: expression = ANY(set) is equivalent to expression IN (set). However, expression <> ALL(set) is different from expression NOT IN (set). The first means the expression is not equal to any value in the set, while the second checks for non-membership.

The ALL operator is a specific and powerful tool for making universal comparisons against a set of values, making it invaluable for queries that enforce strict, all-encompassing rules.