MySQL ALL Operator

MySQL ALL Operator: Testing Against Every Subquery Value

The MySQL ALL operator compares a single column or expression against every value returned by a subquery. The condition returns true only if the comparison holds for every single row in that subquery.

Syntax

Pair ALL with a standard comparison operator (such as greater than, less than, equal, or not equal) immediately before a single-column subquery:

SELECT columns
FROM table_name
WHERE column_name > ALL (
    SELECT target_column
    FROM other_table
    WHERE target_column IS NOT NULL
);

If the subquery returns zero rows, ALL evaluates to true for every outer row (vacuous truth in SQL).

Practical SQL Example

This query finds products in demo_products whose price is strictly higher than every item in the 'Accessories' category:

SELECT id, product_name, category, price
FROM demo_products
WHERE price > ALL (
    SELECT price
    FROM demo_products
    WHERE category = 'Accessories'
      AND price IS NOT NULL
)
ORDER BY price ASC;

Notice that comparing against ALL behaves much like comparing against MAX(price), except when the subquery is empty: comparing with ALL on an empty set returns every row, while comparing against MAX() returns NULL and drops every row.

Common Mistakes and Best Practices

• Filter NULL Inside the Subquery: If the subquery returns even one NULL value, ALL comparisons evaluate to UNKNOWN and drop every outer row. Always add WHERE target_column IS NOT NULL.
• Prefer MAX() or NOT EXISTS for Readability: Most teams find MAX(price) or NOT EXISTS easier to read and tune than ALL subqueries.

Frequently Asked Questions

What is not-equal ALL equivalent to in MySQL?

Comparing with not-equal ALL means a value differs from every row in the subquery, which is logically identical to NOT IN (subquery).

Why did > ALL (subquery) return all rows in my table?

Your subquery returned zero rows, and SQL defines comparisons against an empty ALL set as true.

Can I pass a static list like > ALL (10, 20, 30) in MySQL?

No, ALL only accepts a subquery in MySQL; for static numbers, simply compare against the largest value (> 30).