SQL Subqueries with IN or NOT IN check is similar to the ANY and SOME checks. Use the IN clause for multiple-record, single-column subqueries. After the subquery returns results introduced by IN or NOT IN, the outer query uses them to return the final result.
- If any row in the sub-query result matches, the answer is true.
- If the sub-query result is empty, the answer is false.
- If no row in the sub-query result matches, the answer is also false.
- If all of the values in the sub-query result are null, the answer is false.
Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks.
Syntax
{expression [NOT]IN (subquery)|expression [NOT]IN (expression)}
SQL Subqueries with IN Examples
STORE_SALES
STORE_KEY | ORDER_NUMBER | CUST_KEY | SALES_AMT
-----------+--------------+------------+-----------
166 | 36008 | 14818 | 150
9 | 188567 | 18222 | 35
45 | 202416 | 30333 | 10
113 | 66017 | 18705 | 200
199 | 111111 | 30999 | 300
166 | 121211 | 48999 | 500
198 | 75716 | 30231 | 199
27 | 150241 | 48353 | 599
148 | 182207 | 14111 | 50
24 | 250295 | 14999 | 799
121 | 251417 | 18999 | 999
121 | 199999 | 30999 | 700
CUST_TABLE
CUST_KEY | CUST_NAME | CUST_STATE
--------------+--------------------+----------------
18999 | James J. Goldberg | AL
30999 | Sarah N. McCabe | NJ
48999 | Mark L. Brown | MA
30231 | Sandy N. Fish | CA
48353 | Mark L. Man | CA
14111 | Cheryl X. Hills | TX
18222 | Kim J. Young | MA
30333 | Sunoj N. Samuel | CT
14818 | Sam X. Niel | CA
18705 | Jim J. Okimoto | CA
14999 | William X. Nielson | MA
Question: Write a query to find all the customers from state CA who has a sale amount greater than 100.
SELECT CUST_KEY, CUST_NAME, CUST_STATE FROM CUST_TABLE WHERE CUST_KEY IN (SELECT CUST_KEY FROM STORE_SALES WHERE SALES_AMT > 100) AND CUST_STATE = 'CA' ORDER BY CUST_KEY;
Result
CUST_KEY | CUST_NAME | CUST_STATE --------------+--------------------+---------------- 14818 | Sam X. Niel | CA 18705 | Jim J. Okimoto | CA 30231 | Sandy N. Fish | CA 48353 | Mark L. Man | CA
Question: Write a query in SQL to find the name, city, and the total sum of orders amount a salesman collects. Salesmen should belong to the cities where any of the customers belong.
SELECT salesman.name,
salesman.city,
subquery1.total_amt
FROM salesman,
(SELECT salesman_id,
SUM(orders.purch_amt) AS total_amt
FROM orders
GROUP BY salesman_id) subquery1
WHERE subquery1.salesman_id = salesman.salesman_id
AND salesman.city IN
(SELECT DISTINCT city FROM customer);
Result
name | city | total_amt ------------+----------+----------- Mc Lyon | Paris | 1983.43 Nail Knite | Paris | 1349.45 James Hoog | New York | 11271.46 Pit Alex | London | 270.65
Questions: Find all those customers whose grades are not as the grade, belongs to the city Paris.
SELECT * FROM customer WHERE grade NOT IN (SELECT grade FROM customer WHERE city=‘Paris');
Result
customer_id cust_name city grade salesman_id 3002 Nick Rimando New York 100 5001 3007 Brad Davis New York 200 5001 3005 Graham Zusi California 200 5002 3009 Geoff Cameron Berlin 100 5003 3003 Jozy Altidor Moscow 200 5007