The SQL ANY and ALL Operators

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

The SQL ANY Operators

The ANY operators:

returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

Example

 ANY Syntax:
              
 SELECT column_name(s)

  FROM  table_name 

 WHERE column_name operator ANY (
 SELECT column_name
 FROM  table_name   WHERE condition);

You can click on above box to edit the code and run again.

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

The SQL ALL Operators

The ALL operators:

returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all the values in the range.

ALL Syntax With SELECT

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 <=).

Demo Products table

This product table is used in examples:

Demo Category table

This category table is used in examples:

SQL ANY Example

The following SQL statement lists the Product_name if it finds ANY records in the category table has category_id equal to 6 (this will return TRUE because the id column has value of 6):

Example

 SELECT Product_name

 FROM  products 

 WHERE Product_id =  ANY 
(
 SELECT Product_id
 FROM  category   WHERE 
Category_id = 6)  ;

You can click on above box to edit the code and run again.

The following SQL statement lists the Product_name if it finds ANY records in the category table has category_id larger than 4 (this will return TRUE because the id column has some values larger than 4):

Example

 SELECT Product_name
 FROM  products 
 WHERE Product_id =  ANY (
 SELECT Product_id
 FROM  category   WHERE 
 Category_id > 4)  ;

You can click on above box to edit the code and run again.

The following SQL statement lists the Product_name if it finds ANY records in the category table has category_id larger than 8 (this will return FALSE because the id column has no values larger than 8):

Example

 SELECT Product_name
 FROM  products 
 WHERE Product_id =  ANY (
 SELECT Product_id
 FROM  category   WHERE 
Category_id > 8)  ;

You can click on above box to edit the code and run again.

SQL ALL Example

The following SQL statement lists ALL the product names:

Example

 SELECT ALL Product_name

 FROM  products 

 WHERE  TRUE;

You can click on above box to edit the code and run again.

The following SQL statement lists the Product_name if ALL the records in the category table has id equal to 5. This will of course return FALSE because the id column has many different values (not only the value of 5):

Example

 SELECT Product_name

 FROM  products 

 WHERE Product_id = ALL 
(
SELECT Product_id
 FROM  category 
  WHERE 
Category_id = 5);

You can click on above box to edit the code and run again.