SQL IN Operator

The SQL IN operator allows you to specify a list of fixed values at once in a WHERE clause.

The SQL IN operator is used to help reduce the need for multiple OR conditions in a SQL statement.

It works with values of all datatypes like numbers, text, and dates.

In a WHERE clause, integrate the capabilities of IN and LIKE.

A like and an IN statement cannot be used together. Oracle does, however, support various substitute clauses: Inside context indexes, the CONTAINS clause is used.

Example 1: The real-world sample below illustrates the principle using three search criteria. I need to understand the method for several dozen people. This yields 50 rows, but necessitates the use of numerous OR... LIKE operations:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'

What I would like to do is something like this, which doesn't work:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')

I could create a cursor and loop through it, but the syntax is more complicated than using numerous LIKE statements, and most SQL programmers despise the term "cursor" for testing purposes.

Example 2: compare more than one Patter using LIKE operator:

SELECT name
FROM employee
WHERE name LIKE IN ('sasho','shashi%','rags')

Example 3: one query by stringing together the individual LIKEs with ORs:

SELECT * FROM tablename WHERE column LIKE  'M510%' 
OR column LIKE 'M615%' OR column LIKE 'M515%' OR column LIKE 'M612%';

Just keep in mind that LIKE and per-row functions may not always scale effectively.

The SQL IN condition (also known as the IN operator) makes it simple to see if an expression satisfies any of the items in a list.

It's used in SELECT, INSERT, UPDATE, and DELETE statements to help eliminate the need for multiple OR conditions.

In the parenthesis, you must specify a subquery or a list of values, such as IN (value1, value2,...) or IN (value1, value2..).

The IN operator is useful for determining whether or not the provided value matches any other value in a set of multiple values.

The IN function makes it easier to avoid having to employ several OR conditions.

To evaluate a column or variable value with a set of multiple values, we'll usually use the IN operator with the WHERE clause.

Syntax for SQL IN Operator

SELECT column1, column2 FROM tablename WHERE column1 IN ('value1','value2','value3') 

If you look at the SQL IN operator syntax above, you'll notice that we're verifying whether column1 matches any value in the set of multiple values or not, and it'll return records that match column1 values.

The phrase "IN" replaces the (=) operator after the WHERE declaration inside the query statement, changing the meaning significantly.

The IN keyword has two uses in SQL: the Simple IN Keyword with Where Condition and the NOT IN Keyword.

Example 1: The following SQL query will return all the employees whose location in 'chennai', 'guntur', 'bangalore'.

SELECT * FROM EmployeeDetails WHERE Location IN('chennai','guntur','bangalore')

Example 2: To illustrate the usage of the IN command we are going to use the EMP table.

SELECT * FROM emp WHERE deptno IN (10,20,30); 

The output shows a list of all records for each of the employees we specified in the IN clause (10,20,30). This is a terrific technique to find all the records created by a few different departments, because we can view everything these employees have ordered so far.

Example 1:

SELECT Serial 
FROM Table 
WHERE upper(Serial) in ('D100M', 'D200M');

That won't however use an index on the serial column.

So if performance is a concern you would need to create an index on upper(serial).

Example 2: can cast the searched field to citext:

select company_name
from vendors 
where company_name::citext in ('Abibas', 'NyKey', 'PUMAr');

Using the In operator to get data from MySQL If the list is useful, I'm obtaining the information correctly. The IN clause in a SQL query is empty. Nothing is in an empty set, so the id column value is obviously not in one.

According to this reference, empty() are illegal as the IN right operand syntax is as follows:

<in predicate value>    ::=   <table subquery> | <left parent> <in value list> <right parent>

<in value list>    ::=   <value expression> { <comma> <value expression> } ...

This method always produces an empty result set. The subquery in the bracket generally generates an empty set, and an empty set has no values. Therefore, no value can be retrieved there. Generate an always true statement as in, for "not in" on an empty array. What occurs if the IN clause in a SQL query is left blank?

Example 1: This gives me 0 results as well:

SELECT id FROM User
WHERE id IN(NULL);

Example 2: For example,

SELECT * FROM featured_service_provider where user_id IN (1,2)

If I am passing empty from front end ,I am getting the query as,

SELECT * FROM featured_service_provider where user_id IN ()

Example 3: An SQL query syntax exception occurred when an empty collection was provided to a repository method that creates an IN expression for the WHERE clause (WHERE column IN ()). To get around this, I just return an empty collection in a DAO object, which prevents the syntactically illegal SQL code from running (assuming it's acceptable in a specific scenario).

SELECT user WHERE id IN ();

Will MySQL accept this correctly (that is, always false), and if not, how should my program handle this case while dynamically constructing the IN clause.

Example 1: This is a condensed version of what I've been trying to get to compile:

SELECT * FROM MyTable 
WHERE StatusID IN (
CASE WHEN @StatusID = 99 THEN (5, 11, 13)
ELSE (@StatusID) END )

Example 2: I'm writing a query with a case statement inside an in clause, but I'm not sure if multiple values can be specified. The script below is what I've come up with so far; note the (3,4) inside the case statement:

select *
from Delivery
where VendorId
in
(
case host_name()
when 'HOSTNAME1' then 1
when 'HOSTNAME2' then 2
when 'HOSTNAME2' then (3,4)
else 0
end
)

The IN operator can be used to search a list of date or datetime values for a match.

Example 1: This SQL Server example would return all employees whose date of employment is equal to 01.JAN.1990 or 07.SEP.1982:

SELECT employee_id, hire_date, salary
FROM employees
 WHERE hire_date IN ('1982-09-07', '1990-01-01')

Date values must be enclosed within single quotes (‘date value’).

Date values are format sensitive.

achieve by using the OR operator:

SELECT employee_id, hire_date, salary
FROM employees
WHERE hire_date = '1982-09-07'
OR    hire_date = '1990-01-01'

Example 2: This example compares dates using an IN operator in an IF condition.

Consider the case where the WideWorldImporters business paid a little bonus to employees who worked a holiday in the year 2013. It's possible that a stored procedure containing code like this will be written.

DECLARE @InvoiceDate DATE;
SELECT @InvoiceDate = InvoiceDate
FROM Sales.Invoices
WHERE InvoiceID = 18463;

--OR Version

IF @InvoiceDate = '25-Dec-2013' OR @InvoiceDate = '4-Jul-2013' OR @InvoiceDate = '28-Nov-2013'
BEGIN
  PRINT 'Pay a holiday bonus';
END;

--IN Version

IF @InvoiceDate IN('25-Dec-2013', '4-Jul-2013', '28-Nov-2013')
BEGIN
  PRINT 'Pay a holiday bonus';
END;

The most popular method for inputting date and datetime values is to use quotes and type in a string that can be converted to a date or datetime.

Example 3: I ought to select rows from a table where the date column value matches one of three different values. Using the IN clause here:

SELECT *
FROM Table_name
WHERE Req_Date IN ('2014-01-12','2014-02-14','2014-03-17')

Multiple columns 'In' clause in SQL Server I have a component that uses the keys provided to retrieve data from a database. To speed things up, I'd like my Java program to obtain all the data for all the keys in a single database query.

Example 1:

SELECT * FROM table
WHERE (column1, column2) IN ((1, 'A'), (2, 'B'), (4, 'B'))

Example 2:

SELECT *
FROM dbo.TestTable tt
WHERE tt.data1 IN ('x', 'y', 'z')
OR tt.data2 IN ('x', 'y', 'z')
OR tt.data3 IN ('x', 'y', 'z')

Note: Both the number of values in the IN operator and the number of dataXX columns are substantially bigger in my real circumstance.

Example 3: Look for the value with the data 'Griff'. There are three rows in the table above that include the word "Griff".

select *from tbl_test where 'Griff' IN (column1,coulmn2,coulmn3)

Output:

Using the basic 'IN' clause, the query will verify all the columns where the supplied information is available.

An in the statement is parsed in the same way as a field = val1 or field = val2 or field = val3 is parsed. Using a null will result in field=null, which will not work.

The IN operator works in a similar way. The result will be true if Cypher is aware that anything exists in a list. Even if both values are NULL, a row will not be returned if any NULL values exist. The result will be false, alternatively.

Acquire a value and compare it to every other value or collection of values in a list using IN =.

Example 1:

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

Example 2: Here is a table with examples:

The rules for using all, any, none, and single are identical. True or false is returned if the output can be calculated definitively. Otherwise, the result is null.

Example 3:

select 'true' from dual where null in (null);
select 'true' from dual where (null,null) in ((null,null));
select 'true' from dual where (1,null) in ((1,null));

Example 4:

If there really is a matching non-null value, IN returns TRUE:

[localhost:21000] > select 1 in (1,null,2,3);

Output:

[localhost:21000] > select 1 not in (1,null,2,3);

Example 5: The output is NULL if the searched value is not located in the comparison values and the comparison values include NULL:

[localhost:21000] > select 5 in (1,null,2,3);

Output:

[localhost:21000] > select 5 not in (1,null,2,3);

Output:

[localhost:21000] > select 1 in (null);

Output:

[localhost:21000] > select 1 not in (null);

Output:

Example 1: This SQL Server IN example would return all employees whose department number equals 50, 80, or 90:

SELECT *
FROM employees
WHERE  department_id IN (50, 80, 90)

Achieve by using the OR operator:

SELECT *
FROM employees
WHERE  department_id = 50 
OR     department_id = 80
OR     department_id = 90

Example 2: The following SQL SELECT instances are seeking account persons who have made exactly 6, 8, or 9 sales. A BETWEEN operator will not function since the fortunate number 7 is not included. It also includes a HAVING clause, which functions in the same way as the previous WHERE examples in this SQL query:

--IN Version

SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices 
GROUP BY AccountsPersonID 
HAVING COUNT(*) IN(6,8,9);

Another--OR Version

SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices 
GROUP BY AccountsPersonID 
HAVING COUNT(*) = 6
OR COUNT(*) = 8
OR COUNT(*) = 9;

Because the COUNT function returns a number, there are no quotations in the value list. This is also true for any column that has a numeric data type.

Example 3: How may the IN operator in a select statement be used to find a numeric value among a list of supplied values?

To really get data from all columns in the 'agents' database with the criteria that 'commission' for the agents is any of the following SQL statement can be used in cases 13, 14, and 12:

SQL Code:

SELECT  *
FROM  agents
WHERE  commission  IN  (.13,.14,.12);

Output:

A string column (char, nchar, varchar, nvarchar) can be compared to a list of strings, words, or a phrase using the IN operator. These values can also be verified inside a group of values separated by commas, and the rows holding these values can be retrieved.

The SQL statements below demonstrate two functionally comparable queries: one with many arguments and the other with the IN operator.

Example 1:

--IN Version

SELECT *
FROM [Application].People
WHERE PreferredName IN(N'Kerstin', N'Kayla');

--OR Version

SELECT *
FROM [Application].People
WHERE PreferredName = N'Kerstin'
OR PreferredName = N'Kayla';

Because the data types in the list elements are of the string form, quotations are required. SQL Server would check for two columns named "Kerstin" and "Kayla" without quotes but would not locate them.

The N before the quotes instructs SQL Server that the hard-coded value should be treated as Unicode. Because the data type for the field PreferredName is NVARCHAR rather than VARCHAR, this was done. Without the N's, SQL Server would convert the strings to Unicode implicitly, and the functionality would remain unchanged.

Example 2: This SQL Server example would return all employees whose last name is equal to Smith, Jones, or Stevenson:

SELECT last_name, first_name, salary
FROM employees
WHERE last_name IN ('Smith', 'Jones', 'Stevenson')

The values of the strings must be contained in single quotes.

String values in SQL Server are not case-sensitive by default.

You can achieve the same result by using the OR operator:

SELECT last_name, first_name, salary
FROM employees
WHERE last_name = 'Smith'
OR    last_name = 'Jones'
OR    last_name = 'Stevenson'

Example 3: Let's look at how the IN operator can be used with character values.

Consider the following table: Employees:

Let us run the following query against the above table:

SELECT *
FROM Employees
WHERE name IN ('James John', 'Mercy Bush', 'Kate Joel');

There are three names on our list. We're looking in the Employees table's name column to see if we can discover any of these names. The Kate Joel was compared to one of the table's records, and information about it was returned.

Example 4: We're looking for all the agents in the inventory database's agents table who operate in the states of "London," "Mumbai," or "Chennai."

SELECT *
FROM agents
WHERE working_area IN('London','Mumbai','Chennai');

Use the IN operator to create a subquery that returns records from a single column. In the SELECT column list, the subquery can't have more than one column.

Example 1: Only one column can be returned from your subquery. Imagine using a union query as your subquery if you only need the employee and management IDs:

emp.Emp2NO IN
(select ProjEmpID from project
union
select ProjEmpMGRID from project)

or rewriting to use two IN queries with separate subqueries for each.

Example 2: The IN operator can be used with the subquery to return records from a single column. In the SELECT column list, the subquery cannot have more than one column.

SQL Script: IN Operator

SELECT EmpId, FirstName, LastName, DeptId
FROM Employee
WHERE DeptId IN (SELECT DeptId from Department WHERE DeptId > 2);

In the above query, the sub-query SELECT DeptId from Department WHERE DeptId > 2 returns two DeptId, 3 and 4.

Output:

Example 3: I'm now collecting the results of that query, which gets JAM, JPN, and JOR from here, and pushing them into the IN list, which I'll use to fuel the comparison for my outer query.

SELECT CountryID
 ,CountryName
 ,IsoAlpha3Code
FROM  Application.Countries
WHERE IsoAlpha3Code
 IN (SELECT IsoAlpha3Code
 FROM Application.Countries
WHERE  IsoAlpha3Code Like 'J%')

The advantage of doing so over merely typing JAM, JPN, and JOR is that if we add a fourth country, such as JYP, I won't have to adjust my query. This is because this query is adaptable, capturing any new countries that begin with the letter J.

Whereas, if you wanted to add new countries starting with the letter J and I was hard-coding them in, I'd have to go back to my query and memorize to enter them in every time.

Example 4: Your subquery should return one column. Recognize using a union query as your subquery if you only need the employee and management ID's:

emp.Emp2NO IN
(select ProjEmpID from project
union
select ProjEmpMGRID from project)

or rewriting to use two IN queries with separate subqueries for each.

The IN operator specifies two or more expressions to use in a query search. If the value of the associated column equals one of the phrases indicated by the IN predicate, the condition yields TRUE. In a WHERE clause, the IN operator can be used to indicate multiple values.

It sometimes gets filtered data in the same way that the '=' operator does, with the exception that we can give several values for which the result set is returned.

Syntax:

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

IN: operator name

value1, value2,...: find the exact value that matches the specified values and associated data in the result set.

Example 1: Have all the columns for every employee whose number is 29346, 28559, or 25348:

USE sample;
SELECT emp_no, emp_fname, emp_lname
FROM employee
WHERE emp_no IN (29346, 28559, 25348);

Example 2: To fetch NAME and ADDRESS of students where Age is 18 or 20.

SELECT NAME,ADDRESS FROM Student WHERE Age IN (18,20);

Example 3:

SELECT * FROM students
WHERE class NOT IN (‘BSCS’, ‘BSIT’);

MySQL but this is one of those incidents where the statement fails to cross over into Oracle.

pretty much sums up my question and what I would like to do, but in Oracle

Example 1: find the legal equivalent

Select * from myTable m
where m.status not in ('Done%', 'Finished except%', 'In Progress%')

Example 1: I assume you want to know the performance difference between in and or the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

If the values are constant, IN sorts the list and then performs a binary search, according to the MySQL manual. OR, I think, assesses them one by one, in no specific order.

As a result, IN is faster in some situations.

The only way to figure out which is quicker is to profile both in your database with your particular information.

Both were tested on a MySQL database with 1000000 records. There is no discernible difference in performance when the column is indexed; both are practically instantaneous. I received these results when the column wasn't indexed :

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);

1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 
OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;

1 row fetched in 0.0026 (1.7385 seconds)

As a result, the OR technique is around 30% slower in this example. The discrepancy grows as additional terms are added. Other databases and data may yield different results.

Example 2: The question was about comparing IN and OR functionality. Though I have answered this question many times before, let us answer it again; it never hurts to state the obvious.

The solution is that they are both equal. Yes, it is correct. Let's look at a simple example.

Fun following query on the sample database AdventureWorks –

SET STATISTICS IO ON
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID IN (934,1028,642,932,1026)
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID = 934 OR StoreID = 1028
OR StoreID = 642 OR StoreID = 932 OR StoreID = 1026
GO

Now let us go and check messages:

The SQL Server planning process has automatically transformed all the values from the IN operator to OR in the IN condition. Internally, the SQL Server optimizer engine maps all values supplied in the IN operator to the OR operator. The fact that SQL Server converts into OR automatically is one of the main reasons for their performance being equal.

The IN operator and the NOT IN operator are quite related.

When retrieving a column from a table or referencing a table, the NOT IN operator is utilized.

The IN operator is a shortcut for multiple OR conditions in which one or more values can be specified.

If the specified condition is false, the NOT operator is used to give the records.

The NOT IN clause, unlike the IN operator, returns true if the column or expression isn't found in the set.

For example: IN (10, 20, 30)

If the specified condition is false, the NOT operator is used to retrieve the records.

For example: NOT >= 20

Syntax:

The NOT operator negates the IN operator:

value NOT IN (value1, value2, value2)

If the value does not match any of the values in the list, the NOT IN operator returns one. If not, it returns 0.

Example 1: If you're looking for names of customers who haven't made any purchases,

A customer table will maintain track of all customers, while a transaction table will keep records of any transactions between the store and the customer.

Customers SQL table contains the following:

Transaction table contains:

NOT IN query:

Select first_name, last_name, cust_id from customer where cust_id 
NOT IN ( Select cust_id from transactions)

Output:

Thus:These are the 2 names of customers who have not done any transactions

Example 2: I'm using the employee's table to demonstrate how to use the NOT IN operator. This table stores information about employees such as names, ages, and so on.

The NOT IN operation returns all records from the table except those with the names Jimmy, Shaun, and Ben. Examine the entire dataset after applying the NOT IN operator:

Query:

SELECT * FROM store_db.dbo.sto_employees
 WHERE emp_name NOT IN ('Jimmy', 'Shaun', 'Ben');

Example 3: Let us take the first example and negate the result.

Query:

SELECT * FROM developers WHERE salary NOT IN ($120000, $140000, $109000) ORDER BY salary DESC;

The query should output the rows where the salary is any value other than the ones in the set in this situation.

Example 4: The following example uses the NOT IN operator to check if the number 1 is NOT IN the list (1,2,3):

SELECT 1 NOT IN (1,2,3);

Output:

It returns 0 (false) because 1 is NOT IN the list is false.