Yogesh Chauhan's Blog

SQL ALL Operator

in SQL/MySQL on October 5, 2019

SQL ALL Operator


SQL ALL operator used with WHERE or HAVING clause. It compares a value from WHERE or HAVING condition to ALL values from a subquery. It returns true if the result of subquery contains ALL selected rows. ALL must be preceded by one of the following 6 comparison operators

  1. Less than (<)
  2. Greater than (>)
  3. Less or equal to (<=)
  4. Greater or equal to (>=)
  5. Equal to (=)
  6. Not equal to <>

Let's take a look at the syntax:

SELECT column(s)
FROM table
WHERE column comparison_operator ALL
(SELECT column FROM table WHERE condition);

As you can see in the syntax above, ALL must be preceded one of the 6 comparison operators.

Let's look at the example.

NOTE: Open a DEMO link which is given at the end of the article to understand the example better.

SELECT ItemName FROM items
WHERE ItemID = ALL (SELECT ItemID FROM orderdetails WHERE Quantity > 10);

We have used 2 tables in the example. One is items and other one is orderdetails. As per the name items has items related details and orderdetails has the details of orders placed.

The SQL is going to execute the subquery inside ANY. It will check in the orderdetails that if there is an item which was ordered in more than 10 quantities in any order. If the subquery returns at ALL the rows, the results of the ANY will be set as TRUE and returns the ItemID.

In this case we will get 0 results even if we have order quantity more than 10 in some orders because it requires all the orders to be in more than 10 quantities.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS? #Aug 1 What is Zdog? #Aug 1 How to add before after image effect using pure CSS? #Jul 31 Add animation to your skills bar using CSS #Jul 31 Use SwiperJS to create mobile touch sliders fast
You might also like these
File System Integrity: How to Keep an Eye on Your Files and Folder Change?MiscellaneousOpen Source Security Tools for Defense SecurityMiscellaneousObject destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptHow to add menu to your WordPress theme?WordPressJavaScript Number MethodsJavaScriptHow to convert an HTML radio buttons into a toggle switch using CSS?CSS