PostgreSQL - ALL Operator

The PostgreSQL ALL operator is a powerful tool for comparing a value with a list of values returned by a subquery. This operator is essential for filtering and querying data based on comparisons with multiple values, making it a valuable addition to any PostgreSQL user's toolkit.

Let us better understand the ALL Operator in PostgreSQL from this article.

Syntax

comparison_operator ALL (subquery)

Key Rules for Using the ALL Operator

The below rules need to be followed while using the ALL operator:

• Preceded by a Comparison Operator: The ALL operator always needs to be preceded by a comparison operator(=, !=, <, >, >=, <=).
• Followed by a Subquery: It must always be followed by a subquery surrounded by parentheses.

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

PostgreSQL ALL Operator Examples

Let us take a look at some of the examples of ALL Operator in PostgreSQL to better understand the concept.

Example 1: Querying Films Longer Than Average Lengths

Here we will query for all films whose lengths are greater than the list of the average lengths by using the ALL and greater than operator(>).

Query:

SELECT
    film_id,
    title,
    length
FROM
    film
WHERE
    length > ALL (
            SELECT
                ROUND(AVG (length), 2)
            FROM
                film
            GROUP BY
                rating
    )
ORDER BY
    length;

Output:

Explanation:

• The subquery calculates the average length of films for each rating.
• The main query selects films whose length is greater than all the average lengths returned by the subquery.
• This query helps identify films that are significantly longer than the average for their rating category.

Example 2: Querying Films with Lower Rental Rates Than Average

Here we will query for all films whose rental_rate is less than the list of the average rental_rate by using the ALL and less than operator(<).

Query:

SELECT
    film_id,
    title,
    rental_rate
FROM
    film
WHERE
    rental_rate < ALL (
            SELECT
                ROUND(AVG (rental_rate), 2)
            FROM
                film
            GROUP BY
                rating
    )
ORDER BY
    rental_rate;

Output:

Explanation:

• The subquery calculates the average rental rate of films for each rating.
• The main query selects films whose rental rate is less than all the average rental rates returned by the subquery.
• This query helps identify films that are more affordable compared to the average rental rates in their rating category.

Important Points About the PostgreSQL ALL Operator

• The ALL operator can be used with various comparison operators to filter data based on multiple conditions.
• The ALL operator must always be used with a subquery that provides the list of values for comparison.
• Using the ALL operator with large datasets can impact performance. It is crucial to ensure that subqueries are optimized for efficient execution.