Get Started: SQL ANY and ALL Operators

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.

Connect on:

Leave a Comment

Stay in the loop

codingstreets