SQL Server ANY

Summary: in this tutorial, you will learn how to use the SQL Server ANY operator to compare a value with a single-column set of values returned by a subquery.

Introduction to SQL Server ANY operator #

The ANY operator is a logical operator that compares a scalar value with a single-column set of values returned by a subquery.

The following shows the syntax of the ANY operator:

scalar_expression comparison_operator ANY (subquery)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• scalar_expression is any valid expression.
• comparison_operator is any comparison operator.
• subquery is a SELECT statement which returns a result set of a single column with the data is the same as the data type of the scalar expression.

Suppose the subquery returns a list of values v1, v2, …,  vn. The ANY operator returns TRUE if any comparison (scalar_expression, vi) returns TRUE. Otherwise, it returns FALSE.

Note that the SOME operator is equivalent to the ANY operator.

SQL Server ANY operator example #

See the following products table from the sample database.

The following example finds the products that were sold with more than two units in a sales order:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    product_id = ANY (
        SELECT
            product_id
        FROM
            sales.order_items
        WHERE
            quantity >= 2
    )
ORDER BY
    product_name;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server ANY operator to compare a value with a single-column set of values.