Comparison operators (=, >, < , etc.) are used only on subqueries that return one row. SQL Subqueries with ANY and ALL operators, you can make comparisons on subqueries that return multiple rows. ANY and ALL evaluate whether any or all of the values returned by a subquery match the left-hand expression. It is similar to SOME and IN operators & must follow a comparison operator.
SQL subqueries with ANY operator
Subqueries that use the ANY keyword return true when any value retrieved in the subquery matches the value of the left-hand expression.
Syntax SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
Table: customers
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
Table: orders
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10001 | 1 | 2021-01-01 | 100.00 | 1 |
| 10002 | 5 | 2021-06-01 | 150.00 | 10 |
| 10003 | 3 | 2021-07-01 | 1000.00 | 3 |
| 10004 | 4 | 2021-07-15 | 250.50 | 2 |
| 10005 | 3 | 2021-08-01 | 225.50 | 5 |
Below query will find all the rows from the Customers table where customer_id from customers table is greater than any customer_id from the orders table.
Query SELECT * FROM customers WHERE COALESCE ((customers.customer_id > ANY (SELECT customer_id FROM orders)));
Result
| customer_id | customer_nm | address | city | Zip | country |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
Non-correlated SQL subqueries with ANY – without aggregates
Below query will find all rows from the Customers table which are present in the orders table matched by customer_id.
Query SELECT * FROM customers WHERE customer_id = ANY (SELECT customer_id FROM orders) ORDER BY customer_id;
Result
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
Non-correlated SQL subqueries with ANY – with aggregates
Below query will find all matching rows by customer_id from the customers table which does not equal to the max customer_id row of orders table.
Query SELECT * FROM customers WHERE customer_id <> ANY (SELECT MAX(customer_id) FROM orders) ORDER BY customer_id;
Query SELECT * FROM customers GROUP BY customer_id HAVING customer_id <> ANY (SELECT MAX(customer_id) FROM orders) ORDER BY customer_id
This will result in the unique rows i.e. duplicate rows will be deleted. In our example, we don’t have duplicate rows so the result will be the same.
Result
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
Non-correlated SQL subqueries with ANY – with aggregates and a GROUP BY clause
Query SELECT * FROM customers WHERE customer_id <> ANY (SELECT MAX(customer_id) FROM orders GROUP BY order_id) ORDER BY customer_id;
Explanation
The above query will result in all the rows from the customers table as it will group the max customer_id from the orders table and then try to match with customers rows.
Result
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
SQL subqueries with ALL operator
A subquery that uses the ALL keyword returns true when all values retrieved by the subquery match the left-hand expression otherwise, it returns false.
Non-correlated SQL subqueries with ALL – without aggregates
SELECT * FROM customers WHERE customer_id >= ALL (SELECT customer_id FROM orders) ORDER BY customer_id;
Explanation
Results will have all the rows as the orders table has customer_id (1,3,4,5) and the customers table has customer_id (1,2,3,4,5).
Result
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
Non-correlated SQL subqueries with ALL – with aggregates
Query SELECT * FROM customers WHERE customer_id = ALL (SELECT MAX(customer_id) FROM orders) ORDER BY customer_id;
Explanation
The above query will result in the row from the customers table whose customer_id is EQUALS to the max customer_id (5) from the orders table.
| customer_id | customer_nm | address | city | Zip | country |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
SELECT customer_id FROM customers GROUP BY customer_id HAVING customer_id <> ALL (SELECT MAX(customer_id) FROM orders) ORDER BY customer_id;
Explanation
The above query will result in the row from the customers table whose customer_id is NOT EQUALS to the max customer_id (5) from the orders table.
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
Non-correlated SQL subqueries with ALL – with aggregates and a GROUP BY clause
Query SELECT * FROM customers WHERE customer_id <= ALL (SELECT MAX(customer_id) FROM orders GROUP BY order_id) ORDER BY customer_id;
Explanation
The above query will result in the row from the customers table whose customer_id is LESS THAN EQUALS to all customer_id after group by from orders table.
Result
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
Non-correlated SQL subqueries with ALL – with a GROUP BY clause
Query SELECT * FROM customers WHERE customer_id <> ALL (SELECT customer_id FROM orders GROUP BY customer_id) ORDER BY customer_id;
Explanation
The above query will result in the row from the customers table whose customer_id is NOT EQUALS to all customer_id present in the orders table.
Result
| customer_id | customer_nm | address | city | Zip | country |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
ANY and ALL Examples
Table: Orders ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Table: Customer customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
Table: salesman
salesman_id | name | city | commission
------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
Find all orders with an amount smaller than any amount for a customer in London.
Query SELECT * FROM orders WHERE purch_amt < ANY (SELECT purch_amt FROM orders a, customer b WHERE a.customer_id=b.customer_id AND b.city=‘London');
Explanation
Customers in “London” are “Brad Guzan” & “Julian Green”. Their purchase amount is 270.65 & 250.45 respectively. So the customers whose purchase amount is less than any of these are given below.
Result ord_no purch_amt ord_date customer_id salesman_id 70002 65.26 2012-10-05 3002 5001 70004 110.50 2012-08-17 3009 5003 70011 75.29 2012-08-17 3003 5007 70001 150.50 2012-10-05 3005 5002 70012 250.45 2012-06-27 3008 5002
Question: Find salesmen with all information who lives in the city where any of the customers lives.
Query
SELECT *
FROM salesman
WHERE city=ANY
(SELECT city
FROM customer);
Explanation
Customers live in “New York”, “California”, “London”, “Paris”, “Berlin” & “Moscow”.So the salesmen living in any of these cities are given below.
Result salesman_id name city commission 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14
Question: Display all the orders that had amounts that were greater than at least one of the orders on September 10th, 2012.
Query SELECT * FROM Orders WHERE purch_amt > ANY (SELECT purch_amt FROM orders WHERE ord_date='2012/09/10');
Explanation
The purchase amount of orders dated September 10th, 2012 are 270.65, 948.5, & 5760. So the orders which have amounts greater than any of these are given below
Result ord_no purch_amt ord_date customer_id salesman_id 70005 2400.60 2012-07-27 3007 5001 70008 5760.00 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.40 2012-10-10 3009 5003 70013 3045.60 2012-04-25 3002 5001 70007 948.50 2012-09-10 3005 5002
Question: Display only those customers whose grades are, in fact, higher than every customer in New York
Query SELECT * FROM customer WHERE grade > ALL (SELECT grade FROM customer WHERE city='New York’);
Explanation
The grade of customers who are living in “New York” are 100 & 200. So the grade which is greater than all of these grades from “New York” is given below
Result customer_id cust_name city grade salesman_id 3008 Julian Green London 300 5002 3004 Fabian Johnson Paris 300 5006
Question: Get all information for those customers whose grade is not as the grade of customer who belongs to the city “London”.
Query SELECT * SELECT * FROM customer WHERE grade <> ALL (SELECT grade FROM customer WHERE city='London' AND NOT grade IS NULL);
Explanation
Customers who belong to the city “London” & with NOT NULL grades are “Brad Guzan” & “Julian Green” with grades 100 & 300. The rest customers whose grades are not 100 & 300 will be in the result.
Result customer_id cust_name city grade salesman_id 3007 Brad Davis New York 200 5001 3005 Graham Zusi California 200 5002 3003 Jozy Altidor Moscow 200 5007