SQL NOT Operator

 The NOT operator in SQL is used to negate a condition or expression. It essentially reverses the result of a condition. If the condition is TRUE, using NOT will make it FALSE; if the condition is FALSE, using NOT will make it TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example 1: Using NOT with a simple condition

Imagine you have a table Employees and you want to find all employees who are not in the "Sales" department:

SELECT * 
FROM Employees
WHERE NOT Department = 'Sales';

This query will return all rows where the Department is not "Sales".

Example 2: Using NOT with IN to filter out multiple values

You can also use NOT with the IN operator to exclude multiple values from a list:

SELECT * 
FROM Employees
WHERE Department NOT IN ('Sales', 'Marketing', 'HR');

This query will return all employees who are not in the "Sales", "Marketing", or "HR" departments.

Example 3: Using NOT with BETWEEN

You can negate a BETWEEN condition as well:

SELECT * 
FROM Employees
WHERE Salary NOT BETWEEN 50000 AND 100000;

This query will return all employees whose salary is not between 50,000 and 100,000.

Example 4: Using NOT with LIKE

You can use NOT LIKE to exclude patterns:

SELECT * 
FROM Employees
WHERE Name NOT LIKE 'A%';

This will return all employees whose names do not start with the letter 'A'.

Example 5: Using NOT with EXISTS

You can also use NOT EXISTS to filter out rows based on the existence of a subquery:

SELECT * 
FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM Projects p WHERE p.EmployeeID = e.EmployeeID);

This query will return employees who are not assigned to any project (assuming the Projects table contains the EmployeeID).

Summary

• The NOT operator negates a condition.
• It's commonly used with = (equality), IN, BETWEEN, LIKE, and EXISTS to filter out values or conditions.