IN keyword in SQL

Definition of IN keyword in SQL

The IN keyword is helpful in Structured Query Language (SQL). This logical SQL operator allows you to filter data using a specified list of values. It works for both single and multiple values. You can check if any value matches in a list. If you find a given value in the list, it will return true; otherwise, it will return false. You can create NOT IN by using the NOT with IN operator. It will check whether a given expression is present in a list. You can use IN Operator in the SQL database with SELECT, UPDATE, INSERT, and DELETE queries.

Table of Contents

• Definition of IN keyword in SQL
  • Basic Syntax and Usage
  • IN keyword in SQL – Example
  • Advantages of IN over Multiple OR Conditions
  • Common Use Cases
  • Alternatives to the IN Keyword
  • Performance Considerations
  • Best Practices

Key takeaways

• The IN operator allows you to specify multiple values within a WHERE clause.
• It filters query results against a list of values.
• It is a shorthand for multiple OR conditions.
• It assumes that the result of an operation with NULL is always equal to 0.
• It does not provide any sorting capabilities.
• You can check whether the value of a column matches a value in a specified list.
• It filters the result set to display records matching the parameters in the WHERE clause.

Basic Syntax and Usage

The IN operator is used with the WHERE clause. The basic syntax is:

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

It can have single and multiple values. Here, we have used various values in the basic syntax. value1, value2, value3, … are used to test against the expression. It returns TRUE if any value matches the list, or it returns FALSE.

Syntax (with parameter)

The basic syntax (with parameters) of the IN keyword in SQL is as follows:

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

Parameters:

• column1, column2,…: The columns from which you wish to extract data.
• table_name: The name of the table containing the data.
• column_name: The column you want to compare against.
• value1, value2, …: The values you want to match against.

IN keyword in SQL – Example:

To use the IN operator in your SQL query, you need to follow these steps:

1. First, create a database
2. Second, create a table
3. Insert data into a table
4. View data
5. View data using the IN operator

We have explained these steps as follows.

Step 1: Create a database in SQL

You need to create a database in SQL in step 1. We will make a database for the Engineering Institute.

CREATE DATABASE Engineering_Institute;

Output:

Step 2: Create a new table

Now, you have to create a new table. You can use the following syntax to insert a new table

CREATE TABLE table_name
(
Column1 data type (character_size1),
Column2 data type (character_size2),
Column3 data type (character_size3),
...
ColumnN data type (character_sizeN),
);

We have created the following Faculty table with these column names:

CREATE TABLE Faculty (
    ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100),
    Last_Name VARCHAR(100),
    Department_ID INT NOT NULL,
    Joining_Date DATE,
    City VARCHAR(80),
    Salary DECIMAL(10, 2)
);

Output:

Step 3: Insert data into a table

We will insert these six rows with these values in the Faculty table.

INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1001, 'Johnny', 'Robinson', 5001, '2020-03-15', 'New York', 250000);
INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1002, 'Simon', 'Lopez', 5002, '2019-11-20', 'Paris', 400000);
INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1004, 'Jeeny', 'Ortega', 5001, '2020-08-05', 'Berlin', 480000);
INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1005, 'Kiani', 'Khan', 5001, '2019-06-10', 'California', 450000);
INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1006, 'joey', 'Anderson', 5002, '2019-09-25', 'Mexico', 300000);
INSERT INTO Faculty (ID, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary) VALUES (1007, 'Christopher', 'Watson', 5003, '2021-04-30', 'Moscow', 380000);

Action Output:

Step 4: View data from the table

You can view inserted data from the Faculty table using the following simple SQL query.

SELECT * 
FROM Faculty;

This query will retrieve all rows and columns from the “Faculty” table. It will show information about each faculty member.

The output of this query is:

Step 5: View data using the IN operator

Now you can view the data in the Faculty column with some conditions using the IN operator. We can use numeric values with the IN operator.

SELECT ID AS Faculty_Id, First_Name AS Faculty_First_Name, Department_ID AS Faculty_Dept_Id, Joining_Date AS Faculty_Joining_Date, Salary AS Faculty_Salary 
FROM Faculty 
WHERE Salary IN (400000, 450000, 380000);

In this query, we select faculty IDs, first names, department IDs, joining dates, and salaries from the Faculty table. We select only those rows whose salaries are 40,0000, 45,0000, or 38,0000. We also renamed these columns in the output table.

The output of this query is:

This is another example where the IN logical operator uses text.

SELECT ID AS Faculty_Id, First_Name AS Faculty_First_Name, Joining_Date AS Faculty_Joining_Date, City AS Faculty_City 
FROM Faculty 
WHERE City IN ('New York', 'California', 'Mexico');

In this query, we select only those rows with New York’, ‘California’, or ‘Mexico’ as city names.

The output of this query is:

(a) IN Operator with UPDATE query

You can also use the IN operator with the UPDATE query. For example,

UPDATE Faculty
SET Salary = 500000
WHERE First_Name IN ('Simon', 'Jeeny');

It will update the salary to 500000 where the first name of faculty is either Simonor Jeeny.

Now you can view these updated values:

SELECT * 
FROM Faculty
WHERE First_Name IN ('Simon', 'Jeeny');

Output:

(b) Using the NOT IN Operator

You can also use the NOT operator with the IN operator. The NOT IN operator in SQL negates the result of a subquery and list of values. It filters records that do not match. The syntax of NOT IN is as follows:

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

If the value is not equal to any of the values specified in the list. Then, the row is included in the result set. Otherwise, the row is excluded from the result set.

For example,

SELECT * 
FROM Faculty 
WHERE Salary NOT IN (380000, 420000, 450000, 350000);

This query will return faculty members whose salaries are not 380000, 420000, 450000, or 350000.

Output:

Another example,

SSELECT * 
FROM Faculty 
WHERE City NOT IN ('Mexico', 'New York', 'Paris');

This query will return faculty members not located in Mexico, New York, or Paris.

Output:

(c) IN Operator with Subqueries

You can use the IN and NOT IN operators in the subquery. The syntax of the subquery to do this:

SELECT Column1, Column2, Column3, ......, ColumnN 
FROM TableName 
WHERE ColumnName IN (Subquery);

For example, we want to find faculty members whose departments do not have New York, California, or Paris as city names. You can use this query:

SELECT ID AS Faculty_Id, First_Name, Last_Name, Department_ID, Joining_Date, City, Salary 
FROM Faculty 
WHERE Department_ID IN (
    SELECT Department_ID 
    FROM Faculty 
    WHERE City NOT IN ('New York', 'California', 'Paris')
);

The inner subquery selects all Department_IDs where the City is not New York, California, or Paris.

The Department IDs for Berlin, Mexico, and Moscow are 5001, 5002, and 5003, respectively. The outer query will only return those rows that have these department IDs.

Hence, the cities New York and Berlin will also be in the output table because both of these cities have department IDs of 5001, which are among the selected IDs (5001, 5002, 5003).

Similarly, the city of Paris will be in the output table as it has a department ID of 5002, which is part of the set (5001, 5002, 5003).

Overall, every row will be in the output table since the department IDs returned by the inner query cover every outer query row.

The output of the query is:

Consider another example to select all columns from the Faculty table where the first name is found in a subquery that filters Faculty based on a salary greater than 300000.

SELECT * FROM Faculty 
WHERE First_Name IN (SELECT First_Name 
                     FROM Faculty 
                     WHERE Salary > 300000);

Output:

If you use the NOT IN operation in place of the IN operator, then it will negate the output result.

SELECT * FROM Faculty 
WHERE First_Name NOT IN (SELECT First_Name 
                     FROM Faculty 
                     WHERE Salary > 300000);/code>

Output:

Advantages of IN over Multiple OR Conditions

There are various advantages of using the IN operator over multiple OR conditions.

1. It has more readability because it is concise and readable when using long list values.
2. It sometimes performs better than multiple OR conditions when using indexed columns.
3. It is easier to maintain and modify than long lists of OR conditions. So, it is also easy to update queries as requirements change.
4. It can reduce the chance of syntax errors and logical mistakes when writing multiple OR conditions.
5. It has more clarity, performance, and maintainability of SQL queries compared to using multiple OR conditions.

Common Use Cases

There are various use cases of the IN operator.

1. Filtering data based on multiple values.

You can filter rows based on a column matching any value from a specified list. For example, you can select customer orders and products in specific cities with IDs.

2. Subquery comparisons:

People prefer to use the IN operator with subqueries to compare the result sets of two queries. For example, you can find employees whose salaries are higher than the average salary in their department.

3. Parameterized queries:

You can use the IN operator with parameter queries to dynamically generate the list of values to match against. For example, you can select records associated with a list of user-selected options in a web application.

4. Testing against a finite set:

You can use it with columns with a finite set of possible values. For example, status codes and categories filter records based on these predefined values.

Alternatives to the IN Keyword

There are various alternatives to the IN operator in SQL queries. However, these alternatives depend on use cases and database systems.

A subquery’s rows can be verified to exist using the EXISTS operator. IN operator checks whether a value matches any value in a list. Meanwhile, EXISTS checks whether the subquery returns any rows.

Based on a shared column, rows from two or more tables can be combined using JOIN operations (LEFT JOIN, OUTER JOIN, and INNER JOIN). IN operator filters rows based on matching values in a list. But, JOIN operations retrieve columns from multiple tables based on the specified join condition.

Performance Considerations

• Indexed columns used with the IN keyword can improve performance.
• Smaller lists typically result in negligible overhead.
• Even the distribution of values in the column provides for efficient query execution. Data skewness can affect index utilization and degrade performance.
• Modern database systems use query optimizers to generate efficient execution plans.
• Performance characteristics may vary across different database systems and versions.
• Optimization capabilities of the specific database system impact query performance.

Best Practices

When you use the IN logical operator in SQL queries, you should use it carefully. These are some best practices while working with IN operators.

1. Capitalize keywords

It would be best to capitalize all letters in SQL keywords like SELECT, JOIN, WHERE IN, etc.

2. Use single quotes for string variables

It would be best to enclose each string (text) variable in a single quotes SQL statement. Like, ‘Delhi’, ‘Mumbai’, ‘Rudra’ etc.

3. Use parentheses for integer variables

You should enclose a list of integer variables in a SQL statement in parentheses. For example,

SELECT * FROM Employees WHERE DepartmentID IN (101, 102, 103);

4. Avoid SQL injection

Do not use SQL injection when working with sensitive client data.

5. Avoid HAVING

Try to use WHERE instead of HAVING. WHERE statements are calculated before HAVING statements. So it will be easier to use.

6. Use comments

You can use SQL comments to ensure the code is readable and maintainable. Comments are also useful when debugging code. For example,

-- This query retrieves orders for a specific customer
SELECT * FROM Orders WHERE CustomerID = 'ALFKI';

7. Always format queries

Place each clause on its line to make the code easier to read and less prone to mistakes. For example,

SELECT 
    CustomerID,
    FirstName,
    LastName
FROM 
    Customers
WHERE 
    City = 'London';

8. Ensure consistent aliases

It would be best if you used aliases when merging tables with the same column names to avoid ambiguities in column names. For example,

SELECT
    o.OrderID,
    c.CustomerName
FROM
    Orders o
JOIN
    Customers c ON o.CustomerID = c.CustomerID;

Conclusion

The IN keyword in SQL is a logical operator. It is used for filtering data based on specified lists of values. The IN condition lists values in parentheses. You can also use multiple OR conditions instead of the IN logical operator in SQL. However, the IN operator provides simplicity, flexibility, and efficiency in querying databases. The IN condition is easier to read and more efficient than multiple OR conditions.

Frequently Asked Questions (FAQs)

Q1: Can the IN operator be used with NULL values?

Answer: Yes. The IN operator can be used with NULL values. But note that comparing with NULL values may have unexpected results. It is due to the three-valued logic of SQL. So, you should use it carefully.

Q2: Can the IN operator be used with aggregate functions?

Answer: No. The IN operator cannot be directly used with aggregate functions like SUM(), COUNT(), AVG(), etc. Aggregate functions operate on sets of values. At the same time, the IN operator is used for filtering individual values.

Q3: Can the IN operator be used with composite keys?

Answer: Yes. The IN operator can be used with composite keys, i.e., with multiple value columns. When using composite keys, each value in the list must correspond to a tuple (combination of values) in the composite key.

Q4: Does the IN operator support wildcard characters like % or _?

Answer: No. The IN operator does not support wildcard characters like % or _. It operates solely on a fixed list of values provided in the query.

Recommended Articles

We hope that this EDUCBA information on “IN keyword in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. IS SQL Microsoft?
2. Composite Key in SQL
3. SQL Operators
4. SQL Logical Operators