TUTORIAL

    Any ,All in Sql Server

    Last modified on April 25th, 2020 by DigitalIndiaInfo Team.

    ANY Operator

    ANY compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. ANY must be preceded by comparison operators. Suppose using greater than ( >) with ANY means greater than at least one value.

    Syntax:

    SELECT [column_name... | expression1 ]
                FROM [table_name]
                WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

    Parameters:

    Name Description
    column_name Name of the column of the table.
    expression1 Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
    table_name Name of the table.
    WHERE expression2 Compares a scalar expression until a match is found for ANY operator. One or more rows must match the expression to return a Boolean TRUE value for the ANY operator.
    comparison_operator Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

    ALL Operator

    ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows. For example, ALL means greater than every value, means greater than the maximum value. Suppose ALL (1, 2, 3) means greater than 3.

    Syntax:

    SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

    Parameters:

    Name Description
    column_name Name of the column of the table.
    expression1 Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
    table_name Name of the table.
    WHERE expression2 Compares a scalar expression, such as a column against every value in the subquery for ALL operator. All rows must match the expression to return a Boolean TRUE value for the ALL operator.
    comparison_operator Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=.

    References :

    • www.tutorial.digitalindiainfo.com