SQL Server Universal Comparison Quantified Predicates (ANY, ALL, SOME)

Quantified Comparison Predicates are used to compare the value with every value in the set returned by the subquery. There are mainly two predicates available in SQL Server that are ALL and ANY/SOME.

This clause is very useful when you have to compare two sets of values. These clauses are used for broader comparison just like OR and AND. Like, for ANY you can think of it as a combination of OR clauses in which you are comparing the value with all the values of the Subquery. While ALL is simpler for shortening the expression with an AND clause between the two comparisons.

Example: If you want to compare one value with a set of values, you can use the OR/AND operator for conjunction like this

price=100 OR price=200 OR price=300

But, what if these values are not static and are coming from the table OR would be a waste of our efforts and thus ANY and ALL comes into the play?

Before jumping to the explanation of the clause, let’s prepare the database that we can use for practice in this article.

Let’s have a table of Products shown below:

Query for Creation of database

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    UnitPrice DECIMAL(10, 2),
    CategoryID INT
);

Inserting the Records in Products Table.

-- Insert sample data into the Products table

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (1, 'Laptop', 1200.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (2, 'Smartphone', 800.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (3, 'Headphones', 100.00, 2);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (4, 'Monitor', 500.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (5, 'Keyboard', 50.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (6, 'Mouse', 20.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (7, 'Tablet', 300.00, 1);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (8, 'Printer', 150.00, 3);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (9, 'External Hard Drive', 80.00, 3);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (10, 'Graphics Card', 350.00, 4);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (11, 'RAM', 70.00, 4);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (12, 'SSD Drive', 100.00, 4);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (13, 'Camera', 400.00, 5);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (14, 'TV', 600.00, 5);

INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID)
VALUES (15, 'Speakers', 80.00, 5);

Product Table

ALL

ALL is used with the WHERE clause and it returns TRUE or FALSE according to the results. Syntax of ALL clause is given below

scalar_expression ALL (subquery )

Now, let’s consider a case where you want to Find products in each category that are cheaper than ALL other products in the same category.

SELECT ProductName
FROM Products p
WHERE UnitPrice < 
           ALL  (SELECT UnitPrice FROM Products WHERE CategoryID = p.CategoryID AND ProductID <> p.ProductID);

Explanation of the Query:

1. Subquery will return the UnitPrice of all the products that are from the same Category but the ProductID is different. So for the Mouse which has an Id = 6, Subquery will return every record’s unit price which has the same Category except Id 6.

Output of the SubQuery

2. Now this set of records is being compared with the 20. And if it is less than from Every value of the set then it will return TRUE in WHERE statement which means it will be printed in output.

So, the result of the query is shown below.

Final Output

ANY/SOME

ANY and SOME are the synonyms and can be used interchangeably. So, ANY is similar to ALL. Only the difference is it will compare only till the first comparison where it can get the output TRUE. So, ANY stats that I will compare the value and returns true if I find ANY comparison as TRUE, and if I don’t get any one from them then I will return FALSE.

Let’s explore this with a problem: Find products that are priced greater than ANY product in a different category.

The query for this will be very similar to the above one.

SELECT ProductName
FROM Products p
WHERE UnitPrice > ANY (SELECT UnitPrice FROM Products WHERE CategoryID <> p.CategoryID);

Explanation of the Query

1. Subquery will return the UnitPrice of all the products that are not from the same Category. So for the Laptop which is having a CategoryID = 1, Subquery will return all records of CategoryID other than 1.

output

2. Now this set of records is being compared with the 100. And if it is more than ANY value of the set then it will return TRUE in WHERE statement which means it will be printed in output.

So, the Final Result of the query is as follows:

Final Result

Conclusion

So now we have the idea regarding this predicates about the definition and there uses. Talking about the real-time use of such predicates are generally with Stored Procedure. Thus, it would take less execution time.

Some cases where such predicates are used widely and you can practice such situations:

1. Analyzing courses to find those with enrollments higher than any or all other courses in a specific category.
2. Comparing user ratings to find products with ratings higher or lower than the average rating of products in a specific category.
3. Identifying stocks that are trading at prices higher or lower than any or all other stocks in a particular sector.
4. Analyzing sales data from two different time periods and identifying products with sales higher or lower than any or all products in the previous period.