dots Created with Sketch.
Yogesh Chauhan's Blog

SQL ANY Operator

in SQL/MySQL on October 5, 2019

SQL ANY Operator

SQL ANY operator used with WHERE or HAVING clause. It compares a value from WHERE or HAVING condition to each value from a subquery. It returns true if the result of subquery contains one or more rows. ANY 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 ANY
(SELECT column FROM table WHERE condition);

As you can see in the syntax above, ANY 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 = ANY (SELECT ItemID FROM orderdetails WHERE Quantity = 25);

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 25 quantities in any order. If the subquery returns at least one row, the results of the ANY will be set as TRUE and returns the ItemID.

Now after that it's just a simple query as follows.


SELECT ItemName FROM items WHERE ItemID = 1;

At the end, the whole query will returns the ItemName form items table.

See another example but with greater than (>) operator.


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

The above example will returns the name of the items that was ordered in more than 10 quantities in any single order.

amazon

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

#Jul 22 Is there a CSS parent selector? #Jul 22 Difference between :where and :is in CSS #Jul 22 Does :is() pseudo selector hint at CSS preprocessing in the future? #Jul 22 Control Scrolling with CSS Scroll Snap #Jul 21 Control rendering using CSS content-visibility property #Jul 21 How to use @supports rule in CSS?
You might also like these
What is the difference between Loosely Typed Language and Strongly Typed Language?MiscellaneousHow to detect if browser supports WebP format on server side PHP script?PHPHow to compile and watch Sass using Gulp in WordPress?SCSSHow to get the full URL of current page in PHP?PHPHow to add a ribbon on top of a container using CSS?CSSHow to create a flip effect with CSS?CSS