MySQL

The NOT IN operator in MySQL is part of the Data Query Language (DQL) and is used to filter query results by excluding rows that match any value in a specified list. This operator is helpful when you want to retrieve data that does not belong to a certain set of values. Similar to IN, it simplifies queries by eliminating the need for multiple OR conditions, making queries more concise and readable.

Here’s a detailed explanation of the NOT IN operator with examples and tips for new students:

1. Basic Syntax of NOT IN:

   • The NOT IN operator is used to exclude rows where the column value matches any value within the provided list.
   • Syntax:

   SELECT * 
   FROM employees 
   WHERE department_id NOT IN (1, 2, 3);

   • This query will return all employees who do not belong to departments with IDs 1, 2, or 3.

2. Using NOT IN with Subqueries:

   • You can use NOT IN with a subquery to exclude rows based on values from another table.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id NOT IN (SELECT department_id FROM departments WHERE branch_id = 1);

   • This query returns all employees who are not part of departments in branch 1.

3. Using NOT IN with Strings:

   • The NOT IN operator works with both numbers and strings.
   • Example:

   SELECT * 
   FROM employees 
   WHERE job_title NOT IN ('Manager', 'Engineer', 'Analyst');

   • This query returns all employees whose job title is not Manager, Engineer, or Analyst.

4. Handling NULL Values with NOT IN:

   • If any value in the list contains a NULL, NOT IN will return no results because NULL is not comparable. To avoid this, make sure your list does not include NULL values, or handle NULL separately.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id NOT IN (1, 2, 3) OR department_id IS NULL;

   • This query returns employees who are not in departments 1, 2, or 3, or who have a NULLdepartment_id.

5. Combining NOT IN with Other Conditions:

   • You can combine NOT IN with other operators such as AND, OR, and BETWEEN to create more complex queries.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id NOT IN (4, 5) 
   AND salary > 50000;

   • This query returns employees who are not in departments 4 or 5 and have a salary greater than 50,000.

6. Performance Considerations:

   • The performance of NOT IN can be affected when used with a large number of values or complex subqueries. Indexing columns used in NOT IN conditions can help improve query performance.
   • Example of an alternative using JOIN:

   SELECT e.* 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.branch_id != 1 OR d.department_id IS NULL;

   • This query may offer better performance when excluding specific departments based on a condition.

7. Using NOT IN with Multiple Columns:

   • MySQL does not allow the use of NOT IN directly with multiple columns, but you can achieve this by combining multiple conditions with AND.
   • Example:

   SELECT * 
   FROM employees 
   WHERE (department_id, job_title) NOT IN ((1, 'Manager'), (2, 'Analyst'));

   • This excludes employees who belong to department 1 with the title of Manager, or department 2 with the title of Analyst.

The NOT IN operator is a powerful tool for excluding specific values from your query results. It simplifies the exclusion of data sets and is useful in a variety of scenarios, such as filtering records that don't belong to a specific group or matching against a list of values from a subquery.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM org_employee
WHERE designation NOT IN ('Sales Manager', 'Financial Analyst', 'CFO');

SELECT *
FROM org_client
WHERE birth_year NOT IN (1990, 1980);

SELECT *
FROM act_order
WHERE desired_date NOT IN ('2023-12-09', '2023-12-10', '2023-12-18', '2023-12-25');

-- Oracle
-- WHERE desired_date NOT IN (to_date('09/12/23'), to_date('10/12/23'), to_date('18/12/23'), to_date('25/12/23'));

Example 1:

Let's explore the procedure of extracting information from a designated table using the NOT IN SQL operator with string data. This query retrieves all rows from the employees table where the designation column does not corresponds to any of the specified values ('Sales Manager', 'Financial Analyst', 'CFO').

Example 1 - Raw data from employee table

Example 1 - Query

SELECT * FROM org_employee WHERE designation NOT IN ('Sales Manager', 'Financial Analyst', 'CFO');

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Let's explore the procedure for extracting information from a designated table using the NOT IN SQL operator on integer data. The query fetches all rows from the clients table where the birth year column do not match any of the specified values (1990, 1980).

Example 2 - Raw data from client table

Example 2 - Query

SELECT * FROM org_client WHERE birth_year NOT IN (1990, 1980);

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output

Example 3:

Let's explore the procedure for extracting information from a designated table using the NOT IN SQL operator on date time data. This query retrieves all rows from the orders table where the desired ship date column does not corresponds to any of the specified values ('2023-12-09', '2023-12-10', '2023-12-18', '2023-12-25').

Example 3 - Raw data from order table

Example 3 - Query

SELECT * FROM act_order WHERE desired_date NOT IN ('2023-12-09', '2023-12-10', '2023-12-18', '2023-12-25');

Example 3 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 3 - Query Output