SQL Subqueries with IN or NOT IN operator

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

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