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.
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:
1 2 3 |
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT); |
OR
The given Below syntax for the SQL IN operator
when the possible values are listed out directly.
1 2 3 |
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
1 2 3 4 5 |
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,
1 2 3 |
SELECT * FROM SalesInformation WHERE ShopName IN ('Bridgetown', 'Cambodia'); |
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”:
1 2 |
SELECT * FROM EmployeeDetails WHERE City IN ('Belgium', 'Iceland', 'Italy'); |
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”:
1 2 |
SELECT * FROM EmployeeDetails WHERE City NOT IN ('Belgium', 'Iceland', 'Italy'); |
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
1 2 |
SELECT * FROM EmployeeDetails WHERE City IN (SELECT City FROM Venders); |
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.
1 2 3 |
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:
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:
1 2 3 |
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:
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!