🔍 SQL ALL Operator – Compare Against All Values in a Subquery

If you’re looking to perform comparisons against a set of values returned by a subquery, the ALL operator in SQL is your secret weapon.

In this guide, you’ll learn:

• What the ALL operator does
• How to use it in SELECT, WHERE, and HAVING clauses
• Real-world examples and use cases
• Key differences between ALL, ANY, and IN

---

📘 What is SQL ALL?

The ALL keyword allows you to compare a value to every value in a list or result set from a subquery.

🔧 Syntax:

expression operator ALL (subquery)

• The subquery must return a single column.
• The operator can be =, !=, >, <, >=, or <=.

---

✅ Use Case of SQL ALL

Imagine you want to find products priced higher than every item in another category. That’s exactly where ALL is useful.

---

🧠 SQL ALL Operator Example

📌 Example: Find Products That Are More Expensive Than ALL Products in Category 2

SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 2
);

🧾 Explanation:

• The subquery returns a list of prices for products in category 2.
• The outer query selects only those products whose price is greater than every price in that list.

---

🆚 SQL ALL vs ANY vs IN

Operator	Compares Against…	Example Meaning
ALL	All values in the subquery	Value must meet the condition for every subquery row
ANY	Any value in the subquery	Value must meet the condition for at least one row
IN	Exact value match in subquery	Value must be equal to one of the values

---

📌 Example: Using ALL with <=

SELECT emp_name, salary
FROM employees
WHERE salary <= ALL (
    SELECT salary
    FROM employees
    WHERE dept_id = 10
);

🔍 Result: Employees whose salary is less than or equal to the lowest salary in department 10.

---

⚠️ Things to Keep in Mind

• The subquery must return a scalar column (i.e., only one column).
• If the subquery returns no rows, the condition with ALL evaluates to TRUE.
• If any value in the subquery is NULL, the result is unknown—be cautious with nulls!

---

📈 Real-World Use Cases

• Salary Analysis: Identify employees earning more than everyone in a specific department
• Price Comparison: Find premium products across categories
• Performance Review: Select students who scored higher than all others in a specific subject

---

📝 Summary

The SQL ALL operator is a powerful tool when you need to compare a value against every result from a subquery. Whether you’re filtering data, checking conditions, or validating across multiple rows, ALL helps you write concise and logical SQL queries.

Mastering it alongside ANY, IN, and EXISTS will take your SQL skills to the next level.