Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-all-operator/ below:

PostgreSQL - ALL Operator - GeeksforGeeks

Last Updated : 12 Jul, 2025

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.

comparison_operator ALL (subquery)

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.

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

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.

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4