In This Article, You Will Know About SQL ANY and ALL operators.
SQL ANY and ALL – Before moving ahead, let’s know a bit about the SQL EXISTS Operator.
Table of Contents
SQL ANY and ALL Operators
The ANY and ALL operators are used to make a comparison between a column’s value and a range of other values.
SQL ANY Operator
The SQL ANY Operator returns a Boolean value. It returns TRUE if any values meet the condition.
ANY means if any condition will be TRUE in a range of values, then it returns TRUE.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
SQL ALL Operator
The SQL ALL operator returns TRUE if ALL conditions meet TRUE. It uses SELECT, WHERE, and HAVING statements.
ALL means conditions will be TRUE if all conditions are TRUE in a range of values.
Syntax
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Example Database
Table name: Data_1
S.No | Name | Class |
1 | Alex | 5 |
2 | Rolex | 6 |
3 | Salex | 7 |
4 | Jalex | 8 |
5 | Kalex | 9 |
Table name: Data_2
S.No | Roll_No | Section_ID |
6 | 451 | A |
7 | 452 | B |
8 | 453 | C |
9 | 454 | B |
10 | 455 | E |
SQL ANY Examples
Example: Use the ANY SQL operator to return TRUE if conditions meet TRUE.
SELECT Name
FROM Data_1
WHERE Roll_No = ANY
(SELECT Roll_No
FROM Data_2
WHERE Section_ID = 'B');
Example: Use the ANY SQL operator to return TRUE if conditions meet TRUE.
SELECT Name
FROM Data_1
WHERE Section_ID = ANY
(SELECT Section_ID
FROM Data_2
WHERE Roll_No < 454);
Example: Use the ANY SQL operator to return TRUE if condition meet TRUE.
SELECT Name
FROM Data_1
WHERE Section_ID = ANY
(SELECT Section_ID
FROM Data_2
WHERE Roll_No > 452);
SQL ALL Examples
Example: Use the ALL SQL operator to return TRUE if all conditions meet TRUE.
SELECT Name
FROM Data_1
WHERE TRUE;
Example: Use the ALL SQL operator to return TRUE if all conditions meet TRUE.
SELECT Name
FROM Data_1
WHERE Roll_No = ALL
(SELECT Roll_No
FROM Data_2
WHERE = Section_ID = 'A');
As a result, it returns 0 no. of records because Section_ID doesn’t contain only “A”
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
