SQL IN Statement | Case Statement IN SQL With Example

Summary: In the SQL IN Statement article, you will learn how to use the SQL IN Statement or Operator to check whether a value matches any value in a list.
The SQL IN Statement or operator is a logical operator that allows you to test whether the specified value matched with any value in a list. The SQL IN Statement (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values. therefore, It is used to help reduce the need for multiple OR conditions such as in an INSERT, SELECT, UPDATE, or DELETE statement.

Note: The SQL IN Statement or operator allows you to specify multiple values in a WHERE clause.
Furthermore, the SQL IN Statement or operator is a shorthand for multiple OR conditions.

Therefore, the SQL Server IN operator filters the result set based on a list of discrete values. therefore, The list of discrete values can be simply be listed out or is provided by a separate SELECT statement (i.e. it is called a subquery).
Therefore, the SQL Server IN operator is always used with the WHERE clause.

General Syntax:

There is the given syntax for the SQL IN operator in a subquery construct is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

SELECT column_name(s)

FROM table_name

WHERE column_name IN (SELECT STATEMENT);

Note: The SQL IN operator cannot be used if the filtering criteria is a continuous range. suppose that if you want to look for any value that is between 0 and 1, then we cannot use the SQL IN operator because it is not possible to list for every possible value between the 0 and 1.

OR
The given Below syntax for the SQL IN operator when the possible values are listed out directly.

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

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, ...);

The number of values in the parenthesis can be one or more, with each value separated by a comma. the Values can be a string or numerical characters. furthermore, If there is only one value inside the parenthesis, this commend is equivalent to.

Example:

There are the given below table, we use for our example.
Table: SalesInformation

CREATE TABLE SalesInformation (
      ShopName VARCHAR(70),
      ProductName VARCHAR(70),
	  sales INT,
      Txn_Date DATE)

CREATE TABLE SalesInformation (

ShopName VARCHAR(70),

ProductName VARCHAR(70),

sales INT,

Txn_Date DATE)

ShopName	ProductName	Sales	Txn_Date
Australia	Refrigerators	1564	2015-02-01
Bridgetown	Washing Machines	1200	2015-02-04
Belgium	Air Conditioners	1150	2015-02-08
Cambodia	HDD recorders	1420	2015-02-11
Dominica	Fragrance Control	1650	2015-02-15
Germany	DVD Recorders.	1350	2015-02-17
Honduras	USA Coolers	1400	2015-02-21
Luxembourg	LED Bulbs	4500	2015-02-25

Therefore, to select all records for the Bridgetown and the Cambodia shop name in the SalesInformation table, we key in,

SELECT *
FROM SalesInformation
WHERE ShopName IN ('Bridgetown', 'Cambodia');

SELECT *

FROM SalesInformation

WHERE ShopName IN ('Bridgetown', 'Cambodia');

sql in statement

Sample Demo Table: EmployeeDetails

The given below Demo table below shows the complete "EmployeeDetails" table sample database:

EmpId	EmpName	EmpFather	Education	City	Country	Pincode
101	James Hudson	Liam Isaiah	Graduate degree	Barbados	Bridgetown	226015
102	William Josiah	Noah Charles	Master Degree	Belgium	Brussels	236014
103	Oliver Christian	Benjamin Hunter	Bachelors	Costa Rica	San Jose	226010
104	Lucas Eli	Elijah Connor	B.ED	Croatia	Zagreb	226012
105	Logan Aaron	Mason Ezra	Master Degree	Cyprus	Nicosia	226014
106	Ethan Adrian	Alexander Landon	Management	Dominica	Roseau	226016
107	Michael Nolan	Jacob Jonathan	Bachelors	France	Paris	226020
108	Henry Easton	Daniel Jeremiah	High School	Gabon	Libreville	225678
109	Sebastian Colton	Jackson Elias	Graduate Degree	Georgia	Tbilisi	227834
110	John Greyson	Owen Dominic	Management	Greece	Athens	236056
111	Luke Austin	Jack Adam	InterMediate	Guyana	Georgetown	236011
112	Grayson Jordan	Dylan Santiago	Graduate Degree	Haiti	Port au Prince	236013
113	Julian Evan	Gabriel Roman	Master Degree	Honduras	Tegucigalpa	236017
114	Anthony Xavier	Mateo Ezekiel	Graduate degree	Hungary	Budapest	236014
115	Lincoln Jace	Jaxon Jose	Management	Iceland	Jakarta	442310
116	Christopher Leonardo	Joshua Jameson	Management	Iraq	Baghdad	442313
117	Theodore Axel	Andrew Bryson	Bachelors	Italy	Rome	442315
118	Ryan parker	Caleb Everett	InterMediate	Kiribati	Tarawa Atoll	442317
119	Nathan Miles	Asher Kayden	Graduate Degree	Kyrgyzstan	Bishkek	446778
120	Leo Jason	Thomas sawyer	Management	Liberia	Monrovia	459867

Example: SQL Server IN Condition

The following SQL statement selects all EmployeeDetails that is City in “Belgium”, “Iceland” or “Italy”:

SELECT * FROM EmployeeDetails
WHERE City IN ('Belgium', 'Iceland', 'Italy');

1 2	SELECT * FROM EmployeeDetails WHERE City IN ('Belgium', 'Iceland', 'Italy');

sql in statement

Example: Using the SQL NOT IN Statement with Character Values

therefore, the given following SQL statement selects all EmployeeDetails that are NOT City in “Belgium”, “Iceland” or “Italy”:

SELECT * FROM EmployeeDetails
WHERE City NOT IN ('Belgium', 'Iceland', 'Italy');

1 2	SELECT * FROM EmployeeDetails WHERE City NOT IN ('Belgium', 'Iceland', 'Italy');

NOT IN Operator Output

Sample Demo Table: Vendors

VendorId	CompanyName	City	Country
1	XYZ Textiles	Costa Rica	San Jose
2	Ryan LLP	Dominica	Roseau
3	Nicotex LLP	Georgia	Tbilisi
4	XYZ Chemical	Iceland	Jakarta
5	Himalaya Safari LLP	Kiribati	Tarawa Atoll

therefore, The given following SQL statement selects all EmployeeDetails that is an employee the same City as the Vendors table

SELECT * FROM EmployeeDetails
WHERE City IN (SELECT City FROM Venders);

1 2	SELECT * FROM EmployeeDetails WHERE City IN (SELECT City FROM Venders);

sql In operator Select output

Example: Using the IN Condition with Character Values

Therefore, the SQL IN Statement can be used with any data type. furthermore, Let’s look at how to use the IN condition with character (string) values.

SELECT *
FROM EmployeeDetails
WHERE Country IN ('Bridgetown', 'Nicosia', 'Georgetown', 'Rome');

SELECT *

FROM EmployeeDetails

WHERE Country IN ('Bridgetown', 'Nicosia', 'Georgetown', 'Rome');

Therefore, there will be five (5) records the data selected. These are the screenshot of the results that you should see:
In Condition string output

Example: Using the IN Condition with Numeric Values

Furthermore, let’s see how to use the SQL IN Statement with Numeric Values. Therefore, in this example, we have a table called EmployeeDetails with the following data:

SELECT *
FROM EmployeeDetails
WHERE EmpId IN (101, 105, 110, 115, 120);

SELECT *

FROM EmployeeDetails

WHERE EmpId IN (101, 105, 110, 115, 120);

Therefore, there will be 5 records data selected. furthermore, These are the screenshot of the results that you should see:
SQL IN Statement with Numeric Values

Question: What does SQL IN return?
Answer: Therefore, In SQL Server the WHERE IN returns the values that match values in a list or subquery.
IN SQL, the WHERE IN is shorthand for multiple OR conditions.

SQL IN Statement: Conclusion

In this article, you have learned how to use the SQL Server IN Statement or operator to check whether the value matches any value in a list or returned by a subquery. I hope you will enjoy it!

SQL IN Statement | Case Statement IN SQL With Example

General Syntax:

Example:

Sample Demo Table: EmployeeDetails

Example: SQL Server IN Condition

Example: Using the SQL NOT IN Statement with Character Values

Sample Demo Table: Vendors

Example: Using the IN Condition with Character Values

Example: Using the IN Condition with Numeric Values

SQL IN Statement: Conclusion

Related Posts

About Author

admin