SQL is an essential tool for database management, and understanding its various operators can significantly improve your efficiency when working with data.
The SQL “NOT IN” and “IN” operators are powerful tools that allow you to filter data based on specific criteria. This article will dive deep into the usage, syntax, and practical examples of the SQL IN and NOT IN operators to help you become proficient in using SQL to extract specific data from the database.
Table of Contents
- An Introduction to the SQL IN Operator
- The SQL IN Operator Usage
- An Introduction to SQL NOT IN Operator
- SQL-NOT-IN-Operator-Syntax
- Comparison with Other SQL Operators
- Using NOT IN in Different Scenarios
- Use SQL NOT IN With Subqueries
- Combining SQL NOT IN with Other Operators
- Using SQL NOT IN with NULL Values
- Optimizing SQL NOT IN Performance
- Alternatives to SQL NOT IN
- Conclusion
- FAQs
Let’s start with the SQL IN operator.
An Introduction to the SQL IN Operator
The SQL IN operator is a powerful SQL logical operator that enables users to specify multiple values within the WHERE clause.
When used with the WHERE clause, the IN operator filters the result set to display records matching the parameters in the WHERE clause. It also allows for the inclusion of subqueries within the parentheses of the IN operator.
The versatility of the IN operator extends to various SQL queries, including INSERT, SELECT, UPDATE, and DELETE. Using the IN operator, SQL users can simplify their queries by replacing multiple OR conditions. This greatly streamlines query structure and lets users pack a significant punch in a small SQL query.
The SQL IN Operator Usage
The excellent thing about the SQL IN operator is the various ways you can apply it to supercharge your SQL queries. Let’s look at several ways you can use it.
SQL IN with Multiple Values
You can specify multiple values as part of the IN operator.
Here’s the sample syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
Example:
Below is a table named “Customers”.
| CustomerId | CustomerName | Country |
| 1 | Alice | Germany |
| 2 | Bob | Australia |
| 3 | Tom | USA |
| 4 | David | Japan |
The following SQL statement selects all customers that are located in “Germany”, “USA” or “Japan”:
SELECT * FROM Customers WHERE Country IN ('Germany', 'USA', 'Japan');
Use SQL IN with a subquery
SQL IN is often used to apply a SQL query to another query. In essence, you can have two queries in one statement.
Here’s the sample syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Example:
The following query selects all customers from the same countries as the Resellers. Notice the SELECT subquery in the IN operator:
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Resellers);
Nesting SQL IN
You can nest a second SQL IN operator in a SQL IN operator to maximize the impact of the operator.
Example:
The following statement selects all customers who belong to specific Reseller’s Countries – Germany, USA, or Japan.
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Resellers WHERE Country IN ('Germany', 'USA', 'Japan'));
An Introduction to SQL NOT IN Operator
The SQL NOT IN operator is used with the WHERE clause to exclude specific values from the result set.
It is particularly helpful when filtering out records matching a specified criteria list. The NOT IN clause can be seen as a condensed version of multiple OR conditions applied to the same column.
SQL NOT IN Operator Syntax
The basic syntax for the SQL NOT IN operator is as follows:
column_name NOT IN (expression1, expression2, ...);
This syntax can be used within a WHERE clause for various Data Manipulation Language (DML) statements such as SELECT, UPDATE, INSERT, and DELETE.
The column_name represents the column name on which you want to apply the condition, while the expressions inside the parentheses are the values you want to exclude.
Comparison with Other SQL Operators
Before diving into the practical applications of the SQL NOT IN operator, it’s essential to understand how it compares to other SQL operators that exclude data points during processing.
NOT EXISTS
This operator is used to check if a subquery returns any rows. If the subquery returns no rows, the NOT EXISTS condition is true. In essence, it is used to find non-matching records between tables.
LIKE IN
The LIKE operator uses the WHERE clause to search for a specified pattern within a column. The IN operator, on the other hand, is used to filter records based on multiple values in a column. When combined, LIKE IN allows you to match multiple patterns within a column.
NOT EQUAL TO
The NOT EQUAL TO operator (<> or !=) filters records where the column value does not match a specified value. Unlike the NOT IN operator, it is limited to filtering based on a single value, which can exclude multiple results.
Using NOT IN in Different Scenarios
You can use the NOT IN operator in various DML statements, such as SELECT, INSERT, UPDATE, and DELETE. We’ll now explore examples of how to use NOT IN with these statements.
SELECT Statement
Suppose we have a table named “employees” with the following records:
| EmployeeID | EmployeeName | Department |
| 1 | Alice | HR |
| 2 | Bob | IT |
| 3 | Carol | Sales |
| 4 | David | HR |
To retrieve the records of employees who are not in the HR or Sales departments, we can use the SQL NOT IN operator as follows:
SELECT * FROM employees WHERE Department NOT IN ('HR', 'Sales');
The result will display the records of employees in departments other than HR and Sales:
| EmployeeID | EmployeeName | Department |
| 2 | Bob | IT |
INSERT Statement
While you might not use the NOT IN operator directly within an INSERT statement, it can be used in a subquery to filter out specific data before inserting it into another table.
For example, if we have an inactive_employees table and want to insert only the records of active employees from the employees’ table, we could use the following query:
INSERT INTO active_employees (EmployeeID, EmployeeName, Department)
SELECT EmployeeID, EmployeeName, Department FROM employees
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM inactive_employees);
This query selects only the records of employees whose EmployeeID is not in the inactive_employees table and inserts them into the active_employees table.
UPDATE Statement
Suppose we want to update the records of employees not in the HR department and add a 10% salary increase. We can use the SQL NOT IN operator within the UPDATE statement as follows:
UPDATE employees SET salary = salary * 1.1 WHERE Department NOT IN ('HR');
This query updates the salary of all employees whose department is not HR.
DELETE Statement
If we want to delete all records of employees who are not in the IT department, we can use the SQL NOT IN operator within the DELETE statement as follows:
DELETE FROM employees WHERE Department NOT IN ('IT');
This query deletes all employees whose department is not IT.
Use SQL NOT IN With Subqueries
The SQL NOT IN operator can also be used with subqueries that are embedded within another query.
This allows you to filter records based on the results of another query. For example, we can find all employees who are not managers by using a subquery to retrieve all managers from a managers table and then using NOT IN to exclude them:
SELECT * FROM employees WHERE EmployeeID NOT IN (SELECT ManagerID FROM managers);
This query returns all employees who are not listed as managers in the managers table.
Combining SQL NOT IN with Other Operators
The SQL NOT IN operator can be combined with other operators to create more complex conditions.
For example, we want to find all employees who are not in the HR department and have a salary greater than $50,000. We can use the AND operator in combination with the NOT IN operator as follows:
SELECT * FROM employees WHERE Department NOT IN ('HR') AND salary > 50000;
This query returns all employees who meet both conditions: their department is not HR, and their salary exceeds $50,000.
Using SQL NOT IN with NULL Values
When using the SQL NOT IN operator with NULL values, it’s essential to understand that NULL is not considered a value in SQL.
Therefore, if the NOT IN list contains NULL, it will not affect the result. However, if the column being compared has a NULL value, the NOT IN condition will not evaluate to true.
For example, consider the following query:
SELECT * FROM employees WHERE Department NOT IN ('HR', NULL);
This query will still return all employees not in the HR department, regardless of the NULL value in the NOT IN list.
Optimizing SQL NOT IN Performance
The performance of SQL NOT IN can be affected by factors such as the size of the tables and the complexity of the conditions.
To optimize the performance of SQL NOT IN, consider the following tips:
- Use indexes on the columns involved in the NOT IN condition to speed up query execution.
- Limit the number of values in the NOT IN list to avoid excessive processing time.
- Consider using alternative operators, such as NOT EXISTS in SQL queries (or LEFT JOIN), which may perform better in some scenarios.
Alternatives to SQL NOT IN
While the SQL NOT IN operator is a powerful tool for filtering records based on multiple values, some alternative operators can achieve similar results. Some alternatives to consider include:
NOT EXISTS
As mentioned earlier, the NOT EXISTS operator can be used to find non-matching records between tables. It is often more efficient than NOT IN when working with large datasets.
LEFT JOIN
A LEFT JOIN can be used to filter records by combining two tables and then filtering out records that have a match in the second table. This can be an efficient alternative to NOT IN, especially when working with indexed columns.
Conclusion
By understanding the nuances of SQL IN and SQL NOT IN, you’ll gain greater control over your data filtering and querying capabilities.
Both operators have unique strengths and use cases, enabling you to write more efficient and targeted SQL queries. With the insights and examples shared in this blog post, you’ll be well-equipped to leverage SQL IN and SQL NOT IN effectively and improve your overall SQL proficiency.
Now that you’ve understood these interesting ideas, It’s time to consider a dependable home for your business apps. RedSwitches offer bare metal servers that ensure top-tier performance for your business operations.
FAQs
Q-1) How is the SQL NOT IN operator different from SQL IN?
The SQL NOT IN operator is the negation of the SQL IN operator. While SQL IN retrieves records that match the specified values, SQL NOT IN retrieves the ones that don’t.
Q-2) Can I use the SQL NOT IN operator with subqueries?
The SQL NOT IN operator can also be used with subqueries (like the SQL IN operator). You can also use it with NOT EXISTS in SQL queries. It allows you to exclude records that match the values retrieved by the subquery.
Q-3) Can I use SQL IN and SQL NOT IN with other SQL operators?
Yes, SQL IN and SQL NOT IN operators can be combined with other SQL operators such as AND, OR, and NOT to create more complex data retrieval and filtering conditions.
Q-4) Are there any performance considerations when using SQL NOT IN and SQL IN?
While SQL IN and SQL NOT IN operators are powerful, using them with large sets of values or subqueries can impact query performance. We recommend you optimize your queries, ensure proper indexing, and test performance when dealing with extensive data sets.
