MySQL "NOT IN" query

The NOT IN clause in MySQL is a useful way to filter query results by excluding rows that match certain values. It essentially lets you specify a list of values you want to exclude from your results.

• What is NOT IN used for?
  
• It helps you retrieve records where a specific column’s value is not among a set of given values.
  
• This is the opposite of the IN clause, which returns rows that match any value in the list.
  

How does NOT IN work?

Here’s a basic example:

SELECT * FROM employees
WHERE department NOT IN ('HR', 'Finance');

This query returns all employees who do not belong to the HR or Finance departments.

Important points about NOT IN:

• The list inside NOT IN can include multiple values separated by commas.
  
• If the list contains a NULL, the result might be unexpected because any comparison with NULL results in UNKNOWN in SQL, which can lead to no rows being returned.
  
• For that reason, be careful when your list or column values can have NULL.
  

When to use NOT IN:

• To exclude specific categories, IDs, or statuses from your query.
  
• When you want to filter out known unwanted values in a clean and readable way.
  
• As a simpler alternative to writing multiple AND conditions.
  

Summary:

• NOT IN filters out rows with column values matching any in a given list.
  
• It makes your queries easier to write and understand.
  
• Just watch out for NULL values in the list, as they can affect results.
  

Using NOT IN effectively can help you quickly exclude unwanted data and focus on the information you really need from your MySQL database.