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 <=. |
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
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.