DB2

SQL Subqueries with ANY or ALL operator

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

EXISTS vs NON EXISTS : Click Here IBM DB2 Manual :Click Here

Admin

Next SQL subqueries with operations & examples »

Previous « OUTER JOIN – step by step walkthrough with examples

Published by

Admin

Tags: SQL Subqueries with ALLSQL Subqueries with ANY

4 years ago

OUTER JOIN – step by step walkthrough with examples
The JOIN operation allows you to combine rows from two or more tables based on…
OUTFIL control statement in SORT JCL
The OUTFIL control statement describes the output file(s) and the processing to be done on…
Conditional Expressions use in COBOL
Conditional expressions identify conditions that are tested to enable the object program to select between…