Introduction

In this article, we will learn how to use ANY, ALL operators with a SQL select query.

The ANY and ALL operators are used with a WHERE clause or HAVING clause.

The ANY Operator

• If any of the subquery values meet the condition, the ANY operator returns TRUE.

Syntax

SELECT column_name(s)
FROM TABLE_NAME1
WHERE column_name operator ANY
(SELECT column_name FROM TABLE_NAME2 WHERE condition);

Example

The subsequent statement would return TRUE and lists the technologies if it finds ANY records in the Article table with Views > 100:

SELECT *
FROM Technology
WHERE ID = ANY (SELECT TechID FROM Article WHERE Views > 100);

The ALL Operator

• If all of the subquery values meet the condition, the ALL operator returns TRUE.

Syntax

SELECT column_name(s)
FROM TABLE_NAME1
WHERE column_name operator ALL
(SELECT column_name FROM TABLE_NAME2 WHERE condition);

Example

The subsequent statement would return TRUE and lists the technologies if ALL the records in the Article table have Views > 100:

SELECT *
FROM Technology
WHERE ID = ALL (SELECT TechID FROM Article WHERE Views > 100);

Also, check How To Use Exists Operator In SQL