SQL ALL

What is SQL ALL?

ALL is a set-quantifier and comparison operator modifier that tells SQL to consider every row returned by a subquery or to return every row (including duplicates) in a SELECT.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ALL: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, MariaDB, Snowflake, IBM Db2

SQL ALL Full Explanation

ALL appears in two main contexts:1. Set quantifier in SELECT – "SELECT ALL" explicitly requests that every row be returned, duplicates included. Because ALL is the default, it is rarely written, but it can make intent clear when paired with SELECT DISTINCT in the same statement or when generating dynamic SQL.2. Comparison operator modifier – Placed after a comparison operator and before a subquery, ALL requires the comparison to succeed against every value produced by the subquery. For example, x > ALL (subquery) means x is greater than the maximum value in that subquery. If the subquery returns zero rows, the comparison with ALL evaluates to TRUE. If any subquery value is NULL, the result is UNKNOWN unless eliminated with a WHERE clause.Behavior notes:- Works with =, <>, >, <, >=, <=.- The subquery must return a single column.- Comparison with ALL is semantically equivalent to comparing with MIN or MAX in many cases but keeps the query declarative.- Unlike ANY/SOME, ALL demands that the predicate hold for every subquery value.- Supported in the SQL standard and by most major databases.

SQL ALL Syntax

-- As set quantifier
SELECT ALL column_list
FROM table_name
[WHERE condition];

-- As comparison modifier
expression operator ALL (subquery);

SQL ALL Parameters

Example Queries Using SQL ALL

-- 1. Explicitly show duplicates
SELECT ALL country
FROM customers;

-- 2. Find employees paid more than every employee in department 5
SELECT employee_id, salary
FROM employees e
WHERE salary > ALL (SELECT salary
                    FROM employees
                    WHERE department_id = 5);

-- 3. List products cheaper than every product in the 'Electronics' category
SELECT product_name, price
FROM products p
WHERE price < ALL (SELECT price
                   FROM products
                   WHERE category = 'Electronics');

Expected Output Using SQL ALL

Returns every country row, including duplicates.
Returns employees whose salary exceeds the maximum salary in department 5.
Returns products that cost less than the cheapest Electronics product.

Use Cases with SQL ALL

Make intent explicit when mixing DISTINCT and non-DISTINCT selects in complex queries.
Compare a value to the full set of returned values without writing min/max aggregates.
Business rules like "price must undercut every competitor" or "score exceeds all previous scores".

Common Mistakes with SQL ALL

Confusing ALL with DISTINCT; DISTINCT removes duplicates while ALL keeps them.
Mixing up ALL with ANY/SOME – ALL requires the predicate to be true for every value, not just one.
Forgetting that NULLs cause UNKNOWN results in comparisons.
Placing ALL before the operator (e.g., ALL > (subquery)) – the operator must precede ALL.

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What does SQL ALL do?

ALL returns every row in a SELECT or forces a comparison to be true for every value returned by a subquery.

When should I use SELECT ALL?

Use it when you want to document that duplicates are intentionally kept, especially in dynamic SQL or mixed DISTINCT queries.

How is ALL different from ANY/SOME?

ANY or SOME succeeds if the predicate matches at least one subquery row. ALL requires the predicate to match every subquery row.

Does ALL work in every database?

Yes. ALL is defined in the SQL standard and implemented by PostgreSQL, MySQL, SQL Server, Oracle, SQLite and most other relational databases.