SQL Functions
TOP
❮ ❯

SQL ANY and ALL Operators

SQL ANY and ALL Description

The SQL statements ANY and ALL allow you to perform a comparison between a value in one column and a range of other values.


SQL ANY Description

Operator ANY:

  • returns a boolean value as the result
  • returns TRUE if ANY of the subquery values ​​matches the condition

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

    ANY Syntax

    SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
   FROM table_name
   WHERE condition)
    The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

    SQL ALL Description

    Operator ALL:

  • returns a boolean value as the result
  • returns TRUE if ALL subquery values ​​match the condition
  • used with SELECT, WHERE and HAVING operators

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

    ALL Syntax together with SELECT

    SELECT ALL column_name(s)
FROM table_name
WHERE condition

    ALL Syntax together with WHERE or HAVING

    SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
   FROM table_name
   WHERE condition)
    The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

    Demonstration database

    The following is a sample from the "Products" ("Products") table of the "Northwind" database:

    ProductIDProductNameSupplierIDCategoryIDUnitPrice
    1Chais1110 boxes x 20 bags18.00
    2Chang1124 - 12 oz bottles19.00
    3Aniseed Syrup1212 - 550 ml bottles10.00
    4Chef Anton's Cajun Seasoning2248 - 6 oz jars22.00
    5Chef Anton's Gumbo Mix2236 boxes21.35
    6Grandma's Boysenberry Spread3212 - 8 oz jars25.00
    7Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.30.00
    8Northwoods Cranberry Sauce3212 - 12 oz jars40.00
    9Mishi Kobe Niku4618 - 500 g pkgs.97.00
    10Ikura4812 - 200 ml jars31.00

    And the sample from the table "OrderDetails" ("Order details"):

    OrderDetailIDOrderIDProductIDQuantity
    1102481112
    2102484210
    310248725
    410249149
    5102495140
    6102504110
    7102505135
    8102506515
    910251226
    10102515715

    SQL ANY Examples

    The following SQL query lists ProductName if it finds any (ANY) record in the OrderDetails table with a Quantity value equal to 10 (this will return TRUE because the Quantity column has some values ​​of 10):

    Run SQLSELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
   FROM OrderDetails
   WHERE Quantity = 10)

    The following SQL query lists ProductName if it finds any (ANY) record in the OrderDetails table with a quantity greater than 99 (this will return TRUE because the Quantity column contains some values ​​greater than 99):

    Run SQLSELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
   FROM OrderDetails
   WHERE Quantity > 99)

    Another SQL statement lists ProductName if it finds any (ANY) record in the OrderDetails table with a quantity greater than 1000 (this will return FALSE because the Quantity column does not contain values ​​greater than 1000):

    Run SQLSELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
   FROM OrderDetails
   WHERE Quantity > 1000)

    SQL ALL Examples

    The following SQL statement lists all (ALL) product names:

    Run SQLSELECT ALL ProductName
FROM Products
WHERE TRUE

    The following SQL statement lists ProductName if all (ALL) records in the OrderDetails table have a count equal to 10. Of course, this will return FALSE because the column Quantity has many different values ​​(not just the value 10):

    Run SQLSELECT ProductName
FROM Products
WHERE ProductID = ALL
  (SELECT ProductID
   FROM OrderDetails
   WHERE Quantity = 10)
    ❮ Previous Next ❯