How To Use IN Operator in SQL

sql - in

In previous chapter i have explained How To Use CASE Statement In SQL

now i am gonna eplain you How To Use IN Operator in SQL

SQL IN is an operator used to pull data matching a list of values. A scenario where this proves useful would be if we wanted to retrieve customer data for two or more customers. We can use the IN operator to specify a list of customer names, and SQL will retrieve rows reflecting every customer in the list.

Inside the query statement itself, the word "IN" replaces the (=) operator after the WHERE declarative and slightly alters the meaning as well. Instead of listing a single value, you may list multiple values and SQL will retrieve the results for each value listed.

SQL In:

USE mydatabase;

SELECT *
FROM orders
WHERE customer IN ('Gerald Garner','A+Maintenance');

SQL Results:

id	customer	day_of_order	product	quantity
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5

The results provide a list of all customer orders made by each of the customer names we have listed inside the IN clause ('Gerald Garner','A+Maintenance'). This is a great way to query for all orders made by a handful of different customers as we can see everything these particular customers have ordered thus far.

The real power of this condition comes to life when used with a subquery that retrieves a list of values. Running any SELECT query returns results in list format. And as we mentioned just a few short moments ago, this list can then be passed as a list for the IN clause using a subquery.

Let's adjust the previous example to only retrieve only the products column, as opposed to retrieving all columns (*).

SQL In:

USE mydatabase;

SELECT product
FROM orders
WHERE customer IN ('Gerald Garner','A+Maintenance');

Results:

product

Hanging Files

19" LCD Screen

Our results represent a query run to achieve a list of products sold to two of our customers. Now let's convert this query to a subquery and use this query as an input list to check the inventory table to see if we have any of these items in stock.

SQL In:

USE mydatabase;

SELECT *
FROM inventory
WHERE product in 
     (SELECT product
     FROM orders
     WHERE customer IN ('Gerald Garner','A+Maintenance'));

SQL Results:

id	product	inventory	price
1	19" LCD Screen	25	179.99
5	Hanging Files	33	14.99

By specifying a sub query as our list of values we were able to take advantage of the relationship our tables have with each other and create a very dynamic query. This query saves us the time of scrolling through the entire inventory table and checking the stock of each item purchased by any of our recent customers.

sql - not in

SQL NOT IN, as you may have guessed, allows the developer to eliminate a list of specific values from the result set.

SQL Not In:

USE mydatabase;

SELECT *
FROM inventory
WHERE product NOT IN 
     (SELECT product
     FROM orders
     WHERE customer IN ('Gerald Garner','A+Maintenance'));

SQL Results:

id	product	quantity	price
2	HP Printer	9	89.99
3	Pen	78	0.99
4	Stapler	3	7.99
6	Laptop	16	499.99