SQL ALL Operator
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 <=. |
Pictorical 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
Sample table: despatch
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
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics