Description

The SQL provides the ANY and ALL operators to compare a single column value against a range of other values.

  • The operator ANY returns TRUE if any of the subquery values meet the condition.
  • The operator ALL returns TRUE if all of the subquery values meet the condition.
  • The comparison operator must be a standard comparison operator, like =, <, >, <=, >=, <>, !=, etc.,
  • These operators can be used only on SELECT statements, within the WHERE and HAVING clauses that define conditions.

Syntax

Here is the basic syntax of the SELECT statement using ANY and ALL operators.

  • The column_list represents the list of table columns.
  • The table_name represents the table name.
  • The column_name represents the column name that is being compared with the subquery values.
  • The operator represents a standard comparison operator, like =, <, >, <=, >=, <>, !=, etc.,
  • The condition represents a condition within the inner query.
SELECT column_list
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
SELECT column_list
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

Table Data

Before executing any queries, consider the below data on the tables.

Customers Table Data

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 1 3
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 3 3
10253 2014-07-19 2575.3 4 2
10254 2014-07-24 1256.25 5 3
10255 2014-07-31 9247.5 1 1

SELECT With ANY Operator

The below SELECT statement returns the customers, having a customer ID that is equal to any of the customer IDs returned by the inner query.

  • The inner SELECT statement returns customer IDs from the orders table, having an order value of more than 2000. So, it returns the customers having ID values 1, 3, and 4.
  • The outer SELECT statement returns customers, having customer ID equal to any of the 1, 3, and 4. So, it returns all the three customers having those IDs.

Run this on IDE

SELECT *
FROM customers
WHERE customer_id = ANY (SELECT customer_id FROM orders WHERE order_value > 2000);

After successful execution, the output contains the below data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

SELECT With ALL Operator

The below SELECT statement returns the customers, having a customer ID that is equal to all the customer IDs returned by the inner query.

  • The inner SELECT query returns customer IDs from the orders table, having the order values specified. So it returns two records both having the same customer ID 1.
  • The outer SELECT query returns customers having customer ID equals to all the values returned (which is 1) by the inner query. So, it returns only the customer with ID 1.

Run this on IDE

SELECT *
FROM customers
WHERE customer_id = ALL (SELECT customer_id FROM orders WHERE order_value IN (1807.5, 9247.5));

After successful execution, the output contains the below data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209

Overall

We now know how to compare a single column value against a range of other values in a SELECT statement using ANY or ALL operators.

Related Links