WHERE with NOT operator in SQL

Introduction

The NOT operator helps you find data that does NOT match a condition. It lets you exclude certain rows from your results.

When you want to find all customers who are NOT from a specific city.

When you want to list products that are NOT in stock.

When you want to get employees who do NOT have a certain job title.

When you want to exclude certain dates from your report.

When you want to find records that do NOT meet a specific criteria.

Syntax

SQL

SELECT column1, column2
FROM table_name
WHERE NOT condition;

The NOT operator reverses the condition that follows it.

You can use NOT with any condition, like comparisons or IN lists.

Examples

Finds all employees who are NOT in the Sales department.

SQL

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

Finds products with a price NOT greater than 100 (price 100 or less).

SQL

SELECT ProductName FROM Products
WHERE NOT Price > 100;

Finds customers who are NOT from New York or Chicago.

SQL

SELECT CustomerName FROM Customers
WHERE CustomerName NOT IN ('New York', 'Chicago');

Sample Program

This creates a Students table, adds four students with grades, then selects students whose grade is NOT 'B'.

SQL

CREATE TABLE Students (
  ID INT,
  Name VARCHAR(50),
  Grade CHAR(1)
);

INSERT INTO Students (ID, Name, Grade) VALUES
(1, 'Alice', 'A'),
(2, 'Bob', 'B'),
(3, 'Charlie', 'C'),
(4, 'Diana', 'B');

SELECT Name, Grade FROM Students
WHERE NOT Grade = 'B';

Important Notes

NOT changes TRUE to FALSE and FALSE to TRUE for the condition it applies to.

You can combine NOT with AND or OR for more complex filters.

Be careful with NOT and NULL values; NULL means unknown, so NOT may not behave as expected.

Summary

The NOT operator excludes rows that match the condition.

Use NOT before any condition in the WHERE clause.

It helps you find data that does NOT meet certain criteria.