ALL & ANY are logical operators in SQL. They return boolean value as a result.
ALL operator is used to select all tuples of SELECT STATEMENT. It is also used to compare a value to every value in another value set or result from a subquery.
- The ALL operator returns TRUE iff all of the subqueries values meet the condition. The ALL must be preceded by comparison operators and evaluates true if all of the subqueries values meet the condition.
- ALL is used with SELECT, WHERE, HAVING statement.
ALL with SELECT Statement:
Syntax: SELECT ALL field_name FROM table_name WHERE condition(s);
ALL with WHERE or HAVING Statement:
Syntax: SELECT column_name(s) FROM table_name WHERE column_name comparison_operator ALL (SELECT column_name FROM table_name WHERE condition(s));
Consider the following Products Table and OrderDetails Table,
- Find the name of the all the product.
SELECT ALL ProductName FROM Products WHERE TRUE;
- Find the name of the product if all the records in the OrderDetails has Quantity either equal to 6 or 2.
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductId FROM OrderDetails WHERE Quantity = 6 OR Quantity = 2);
- Find the OrderID whose maximum Quantity among all product of that OrderID is greater than average quantity of all OrderID.
SELECT OrderID FROM OrderDetails GROUP BY OrderID HAVING max(Quantity) > ALL (SELECT avg(Quantity) FROM OrderDetails GROUP BY OrderID);
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 return true if any of the subqueries values meet the condition.
- ANY must be preceded by comparison operators.
SELECT column_name(s) FROM table_name WHERE column_name comparison_operator ANY (SELECT column_name FROM table_name WHERE condition(s));
- Find the Distinct CategoryID of the products which have any record in OrderDetails Table.
SELECT DISTINCT CategoryID FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails);
- Finds any records in the OrderDetails table that Quantity = 9.
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 9);
This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- Print all the prime numbers between 'm' and 'n' in PL/SQL
- Union and Union All in MS SQL Server
- SQL | AND and OR operators
- SQL | Functions (Aggregate and Scalar Functions)
- Basic SQL Injection and Mitigation with Example
- SQL | DDL, DML, TCL and DCL
- Difference between Static and Dynamic SQL
- Difference between Simple and Complex View in SQL
- Difference between SQL and NoSQL
- Difference between SQL and PLSQL
- Difference between DELETE and DROP in SQL
- Difference between MySQL and MS SQL Server
- Differences between Views and Materialized Views in SQL
- SQL query using COUNT and HAVING clause
- Difference between JOIN and UNION in SQL
- Difference between COMMIT and ROLLBACK in SQL
- Advantages and Disadvantages of SQL