SQL IN Operator | How to Use SQL IN With Different Condition?

Summary: In this tutorial, you will learn, how to use the SQL IN operator, with different conditions and examples, such as the use of SQL in IN Condition with Numeric Values, SQL IN Condition with Character Values and SQL IN Condition with the NOT Operator, etc.

SQL IN Operator

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

Syntax:

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);

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, ...);

Sample Table: Employees

EmpId	EmpName	EmpFather	Education	City	Country	Pincode
101	James Hudson	Liam Isaiah	Graduate degree	France	Paris	226015
102	William Josiah	Noah Charles	Master Degree	Belgium	Brussels	236014
103	Oliver Christian	Benjamin Hunter	Bachelors	Buenos Aires	Argentina	226016
104	Lucas Eli	Elijah Connor	B.ED	México D.F.	Mexico	226012
105	Logan Aaron	Mason Ezra	Master Degree	Cyprus	Nicosia	226014
106	Ethan Adrian	Alexander Landon	Management	Buenos Aires	Argentina	226016
107	Michael Nolan	Jacob Jonathan	Bachelors	France	Paris	226015
108	Henry Easton	Daniel Jeremiah	High School	Gabon	Libreville	225678
109	Sebastian Colton	Jackson Elias	Graduate Degree	México D.F.	México	226012
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	Mannheim	Germany	442317
113	Julian Evan	Gabriel Roman	Master Degree	Honduras	Tegucigalpa	236017
114	Anthony Xavier	Mateo Ezekiel	Graduate degree	London	UK	236014
115	Lincoln Jace	Jaxon Jose	Management	Strasbourg	France	446778
116	Christopher Leonardo	Joshua Jameson	Management	London	UK	236014
117	Theodore Axel	Andrew Bryson	Bachelors	Italy	Rome	442315
118	Ryan parker	Caleb Everett	InterMediate	Mannheim	Germany	442317
119	Nathan Miles	Asher Kayden	Graduate Degree	Strasbourg	France	446778
120	Leo Jason	Thomas sawyer	Management	Liberia	Monrovia	459867

Example: SQL IN Operator

Question 1: To select all Employees that are located in “Argentina”, “Mexico” or “France”:

SELECT * FROM Employees
WHERE Country IN ('Argentina', 'Mexico', 'France');

1 2	SELECT * FROM Employees WHERE Country IN ('Argentina', 'Mexico', 'France');

SQL IN example1
Question 2: To select all Employees that are NOT located in “Argentina”, “Mexico”, “France”, “France”, “UK” OR “Germany”:

SELECT * FROM Employees
WHERE Country NOT IN ('Argentina', 'Mexico', 'France', 'UK', 'Germany');

1 2	SELECT * FROM Employees WHERE Country NOT IN ('Argentina', 'Mexico', 'France', 'UK', 'Germany');

SQL IN example2
Question 3: To select EmpName and City from Employees table that is NOT located ‘Argentina’, ‘Mexico’, ‘France’, ‘UK’, ‘Germany’in Country column and sorted Order by Empname.

SELECT
    EmpName,
    City
FROM
    Employees
WHERE
    Country NOT IN ('Argentina', 'Mexico', 'France', 'UK', 'Germany')
ORDER BY
    EmpName;

SELECT

EmpName,

City

FROM

Employees

WHERE

Country NOT IN ('Argentina', 'Mexico', 'France', 'UK', 'Germany')

ORDER BY

EmpName;

sql In operator

SQL: IN Condition

Note:
The SQL IN condition, its also know as, IN operator, it 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 in an INSERT, SELECT, UPDATE or DELETE statement.

Example: Using the SQL IN Condition with Character Values

The SQL IN condition can be used with any data type in SQL. Let’s see how to use the SQL IN condition with character (string) values.
In this example, we have used the Employees table with the following data:

SELECT *
FROM Employees
WHERE City IN ('France', 'México D.F.', 'Mannheim', 'London', 'Strasbourg');

SELECT *

FROM Employees

WHERE City IN ('France', 'México D.F.', 'Mannheim', 'London', 'Strasbourg');

SQL IN Condition with Character Values
There will be 10 records selected. therefore, you can see the above-given screenshot results:
This example would return all rows from the Employees table where the City is either France, México D.F., Mannheim, London, or Strasbourg Because the * is used in the selection.
All fields from the Employees table would appear in the result set.
Therefore, It is equivalent to the following above SQL statement:

SELECT *
FROM Employees
WHERE City = 'France'
OR City = 'México D.F.'
OR City = 'Mannheim'
OR City = 'London'
OR City = 'Strasbourg';

SELECT *

FROM Employees

WHERE City = 'France'

OR City = 'México D.F.'

OR City = 'Mannheim'

OR City = 'London'

OR City = 'Strasbourg';

Example – Using the SQL IN Condition with Numeric Values

Furthermore, let’s see how to use the IN condition with numeric values.
In this example, we have used the Employees table with the following data:

SELECT *
FROM Employees
WHERE EmpId IN (102, 105, 107, 111, 115);

SELECT *

FROM Employees

WHERE EmpId IN (102, 105, 107, 111, 115);

Condition with Numeric Values
Therefore, you can see in the above screenshot results, there will be 4 records selected.
Furthermore, This example would return all records from the Employees table where the EmpId is either 102, 105, 107, 111, and 115.
so, it is equivalent to the following SQL statement:

SELECT *
FROM Employees
WHERE EmpId = '102'
OR EmpId = '105'
OR EmpId = '107'
OR EmpId = '111'
OR EmpId = '115';

SELECT *

FROM Employees

WHERE EmpId = '102'

OR EmpId = '105'

OR EmpId = '107'

OR EmpId = '111'

OR EmpId = '115';

Example- Using the SQL IN Condition with the NOT Operator

Therefore let’s see how to use the IN condition with the NOT operator. furthermore, the NOT operator is used to negate a condition. so, when we used the NOT operator with the IN condition, we create a NOT IN condition.
Here I will Employees table with the following data:

SELECT *
FROM Employees
WHERE City NOT IN ('France', 'México D.F.', 'Mannheim', 'London', 'Strasbourg');

SELECT *

FROM Employees

WHERE City NOT IN ('France', 'México D.F.', 'Mannheim', 'London', 'Strasbourg');

IN Condition with the NOT Operator
This example would return all rows from the Employees table where the City is not France, México D.F., Mannheim, London, and Strasbourg. therefore, Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
Furthermore, the given below SQL statement is equivalent to the above SQL statement:

SELECT *
FROM Employees
WHERE City <> 'France'
AND City <> 'México D.F.'
AND City <> 'Mannheim'
AND City <> 'London'
AND City <> 'Strasbourg';

SELECT *

FROM Employees

WHERE City <> 'France'

AND City <> 'México D.F.'

AND City <> 'Mannheim'

AND City <> 'London'

AND City <> 'Strasbourg';

Note:
Therefore, you can see that the equivalent statement is written using AND conditions instead of OR conditions because the IN condition is negated.

Conclusion:

IN is a logical operator in SQL which allows you to specify a list of values that you’d like to include in the results. these operator used for Numeric value, string value and also used for AND operator.
I hope you will be enjoy this tutorial, if you have any query contact me, I will try to remove your query.

SQL IN Operator | How to Use SQL IN With Different Condition?

SQL IN Operator

Syntax:

Sample Table: Employees

Example: SQL IN Operator

SQL: IN Condition

Example: Using the SQL IN Condition with Character Values

Example – Using the SQL IN Condition with Numeric Values

Example- Using the SQL IN Condition with the NOT Operator

Conclusion:

Related Posts

About Author

admin