[Basic Grammar] SQL ANY and ALL

• SQL ANY and ALL Operators 

The ANY and ALL operators allow you to compare between a single column value and a range of other values. 

 

• SQL ANY Operator 

• The ANY operator gives you a boolen value as a result.
• It means that the condition will be true if the operation is true for any of the values in the range.
• It returns TRUE if any of the subquery is true. 

 

• 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 <=). **

 

• The SQL ALL Operator 

• It returns a boolean value as a result.
• It gives you TRUE if all of the subquery values meet the condition. 
• It usually is used with SELECT, WHERE, and HAVING statements. 

• ALL Syntax with SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

 

• ALL Syntax with WHERE or HAVING 

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

 

Exercises 

1. Write a SQL query to list the ProductName if there are ANY records in the OrderDetails table with a UnitPrice greater than 50. Provide the SQL query to achieve this.

SELECT DISTINCT p.ProductName
FROM Products p
JOIN OrderDetails o ON p.ProductID = o.ProductID
WHERE o.UnitPrice > 50;

 

2. Create a SQL query to list the ProductName if ALL the records in the OrderDetails table have a Discount greater than 0.1. Provide the SQL query to achieve this.

SELECT DISTINCT p.ProductName
FROM Products p
LEFT JOIN OrderDetails o ON p.ProductID = o.ProductID
WHERE NOT EXISTS (
    SELECT 1
    FROM OrderDetails od
    WHERE od.ProductID = p.ProductID
      AND od.Discount <= 0.1
);