PostgreSQL ALL Operator

Summary: in this tutorial, you will learn how to use the PostgreSQL ALL operator to compare a value with a list of values returned by a subquery.

Overview of the PostgreSQL ALL operator

The PostgreSQL ALL operator allows you to compare a value with all values in a set returned by a subquery.

Here’s the basic syntax of the ALL operator:

expression operator ALL(subquery)

In this syntax:

• The ALL operator must be preceded by a comparison operator such as equal (=), not equal (<>), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=).
• The ALL operator must be followed by a subquery which also must be surrounded by the parentheses.

If the subquery returns a non-empty result set, the ALL operator works as follows:

• value > ALL (subquery) returns true if the value is greater than the biggest value returned by the subquery.
• value >= ALL (subquery) returns true if the value is greater than or equal to the biggest value returned by the subquery.
• value < ALL (subquery) returns true if the value is less than the smallest value returned by the subquery.
• value <= ALL (subquery) returns true if the value is less than or equal to the smallest value returned by the subquery.
• value = ALL (subquery) returns true if the value equals every value returned by the subquery.
• value != ALL (subquery) returns true if the value does not equal any value returned by the subquery.

If the subquery returns no row, then the ALL operator always evaluates to true.

PostgreSQL ALL operator examples

Let’s explore some examples of using the PostgreSQL ALL operator.

Setting up a sample table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE managers(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (first_name, last_name, salary)
VALUES
('Bob', 'Williams', 75000.00),
('Charlie', 'Davis', 55000.00),
('David', 'Jones', 50000.00),
('Emma', 'Brown', 48000.00),
('Frank', 'Miller', 52000.00),
('Grace', 'Wilson', 49000.00),
('Harry', 'Taylor', 53000.00),
('Ivy', 'Moore', 47000.00),
('Jack', 'Anderson', 56000.00),
('Kate', 'Hill',  44000.00),
('Liam', 'Clark', 59000.00),
('Mia', 'Parker', 42000.00);

INSERT INTO managers(first_name, last_name, salary)
VALUES
('John', 'Doe',  60000.00),
('Jane', 'Smith', 55000.00),
('Alice', 'Johnson',  58000.00);

1) Using the ALL operator with the greater than operator (>) example

The following example uses the ALL operator for employees who have salaries greater than all managers

SELECT
  *
FROM
  employees
WHERE
  salary > ALL(
    select
      salary
    from
      managers
  );

Output:

id | first_name | last_name |  salary
----+------------+-----------+----------
  1 | Bob        | Williams  | 75000.00
(1 row)

The query returns one row with a salary of 75K greater than the highest salary of all managers (60K).

2) Using the ALL operator with the less than operator (<) example

The following example uses the ALL operator for employees who have salaries less than all managers:

SELECT
  *
FROM
  employees
WHERE
  salary < ALL(
    select
      salary
    from
      managers
  )
ORDER BY salary DESC;

Output:

id | first_name | last_name |  salary
----+------------+-----------+----------
  7 | Harry      | Taylor    | 53000.00
  5 | Frank      | Miller    | 52000.00
  3 | David      | Jones     | 50000.00
  6 | Grace      | Wilson    | 49000.00
  4 | Emma       | Brown     | 48000.00
  8 | Ivy        | Moore     | 47000.00
 10 | Kate       | Hill      | 44000.00
 12 | Mia        | Parker    | 42000.00
(8 rows)

It returns all the employees whose salaries are less than the lowest salary of all managers which is 55K.

Summary

• Use the PostgreSQL ALL operator to compare a value with all values in a set of values returned by a subquery.