Prev Next

PostgreSQL ALL

The ALL operator checks if a value meets a condition against every element in a list or every row returned by a subquery. It returns TRUE only if the condition is true for all elements in the comparison set.

Key Topics

1. Basic Usage

To check if a value compares a certain way to all items in a list:

SELECT *
FROM employees
WHERE department <> ALL (ARRAY['Finance', 'Legal']);

This returns only employees who are in neither Finance nor Legal, since <> ALL means the column value must differ from every item in the array.

2. Subquery Example

Comparing salaries to all salaries in a specific department:

SELECT e.*
FROM employees e
WHERE e.salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
);

Here, only employees whose salary is greater than every salary in the Sales department are returned.

Best Practices

  • Use ALL when you need to confirm a condition is met for every element in a set.
  • Combine with subqueries to dynamically compare columns in different tables.
  • Optimize subqueries with indexes to speed up performance, especially on large datasets.

Key Takeaways

  • ALL only returns TRUE if the condition is true for every element in the list/subquery.
  • Perfect for strict comparisons where one row must exceed or match all values in another subset.
Prev Next