SQL ALL Operator

Last update on February 21 2026 12:27:42 (UTC/GMT +8 hours)

ALL Operator

ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows. For example, ALL means greater than every value, means greater than the maximum value. Suppose ALL (1, 2, 3) means greater than 3.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

Parameters:

Name	Description
column_name	Name of the column of the table.
expression1	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name	Name of the table.
WHERE expression2	Compares a scalar expression, such as a column against every value in the subquery for ALL operator. All rows must match the expression to return a Boolean TRUE value for the ALL operator.
comparison_operator	Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=.

Visual Presentation: SQL ALL Operator

DBMS Support: ALL Operator

DBMS	Command
MySQL	Supported
PostgreSQL	Supported
SQL Server	Supported
Oracle	Supported

Example: SQL ALL operator

Sample table: orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
..........
    200102       2000            300 25-MAY-08 C00012          A012

View the table

Sample table: despatch

DES_NUM    DES_DATE  DES_AMOUNT    ORD_NUM ORD_DATE  ORD_AMOUNT AGENT_CODE
---------- --------- ---------- ---------- --------- ---------- ----------
D002       10-JUN-08       2000     200112 30-MAY-08       2000 A007
D005       19-OCT-08       4000     200119 16-SEP-08       4000 A010
D001       12-JAN-08       3800     200113 10-JUN-08       4000 A002
D003       25-OCT-08        900     200117 20-OCT-08        800 A001
D004       20-AUG-08        450     200120 20-JUL-08        500 A002
D006       24-JUL-08       4500     200128 20-JUL-08       3500 A002

To get 'des_date','des_amount' and 'ord_amount' columns from the 'despatch' table with following conditions -

1. 'des_amount' of 'despatch' table is more than 'ord_amount' from 'orders' table which satisfies the condition bellow:

a) 'ord_amount' must be equal to 2000,

the following SQL statement can be used:


SELECT des_date, des_amount, ord_amount
-- Selecting columns des_date, des_amount, ord_amount
FROM despatch
-- From the table named "despatch"
WHERE des_amount > ALL (
    SELECT ord_amount FROM orders
    -- Selecting column ord_amount from the table "orders"
    WHERE ord_amount = 2000
    -- Where the value in column ord_amount is 2000
);
-- Where the value in column des_amount is greater than all values returned by the subquery

Explanation:

SELECT des_date, des_amount, ord_amount: This specifies that we want to retrieve columns des_date, des_amount, and ord_amount.

FROM despatch: This indicates the table from which we want to retrieve the data. In this case, the table is named "despatch".

WHERE des_amount > ALL (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column "des_amount" is greater than all values returned by the subquery should be included in the result set.

The subquery inside ALL (...) selects the ord_amount column from the "orders" table where the ord_amount is equal to 2000.

ALL (...): This operator is used to compare a value with all values returned by a subquery and returns true if the comparison holds true for all values.

Output:

DES_DATE  DES_AMOUNT ORD_AMOUNT
--------- ---------- ----------
12-JAN-08       3800       4000
19-OCT-08       4000       4000
24-JUL-08       4500       3500

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

PREV :ANY
NEXT : SOME