(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | ALL and ANY
ALL & ANY are logical operators in SQL. They return boolean value as a result.
ALL
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:
SELECT column_name(s) FROM table_name WHERE column_name comparison_operator ALL (SELECT column_name FROM table_name WHERE condition(s));
Example:
Consider the following Products Table and OrderDetails Table,
Products Table
OrderDetails Table
Queries
- Find the name of the all the product.
SELECT ALL ProductName FROM Products WHERE TRUE;
Output:
- 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);
Output:
- 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);
Output:
ANY
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.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name comparison_operator ANY (SELECT column_name FROM table_name WHERE condition(s));
Queries
- 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);
Output:
- Finds any records in the OrderDetails table that Quantity = 9.
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 9);
Learn to Code SQL Example – SQL | ALL and ANY
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.