SQL In

The SQL IN Operator

You can include more than one value in a WHERE clause by using the IN operator.

Multiple OR conditions can be shortened to the IN operator.

Return all customers from ‘Germany’, ‘France’, or ‘UK’

				
					SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

				
					SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

You can return all entries that are NOT any of the values in the list by using the NOT keyword before the IN operator.

Return all customers that are NOT from ‘Germany’, ‘France’, or ‘UK’:

				
					SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

IN can also be used in the WHERE clause with a subquery.

You can retrieve every record from the primary query that appears in the subquery’s result by using a subquery.

Return all customers that have an order in the Orders table:

				
					SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

17 clients have not placed any orders, according to the 74 records that the above example’s result returned.

Let’s use the NOT IN operator to see if that is accurate.

Return all customers that have NOT placed any orders in the Orders table:

				
					SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);