SQL - NOT Operator

SQL - NOT Operator

The NOT operator is a logical operator in SQL used to reverse the result of any boolean expression. If a condition is TRUE, NOT makes it FALSE, and if it is FALSE, NOT makes it TRUE. It’s essential for excluding specific data from a result set.

Using the SQL NOT Operator

The NOT operator is used within a WHERE clause to filter out rows that meet a certain condition. To avoid ambiguity, it is a good practice to wrap the condition being negated in parentheses.

First, let’s set up our sample CUSTOMERS table:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00),
(8, 'John Doe', NULL, 'Nowhere', NULL);

The following query retrieves all customers whose salary is not greater than 2000.00. This includes those with a salary less than or equal to 2000.00.

SELECT * FROM CUSTOMERS WHERE NOT (SALARY > 2000.00);

Output

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

Note: Rows where SALARY is NULL are not returned, because NULL > 2000.00 evaluates to UNKNOWN, and NOT (UNKNOWN) is also UNKNOWN.

NOT LIKE

The NOT LIKE operator is used to find records that do not match a specified string pattern. This is useful for excluding names, codes, or text that follows a certain format.

-- Find customers whose name does not start with 'K'
SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE 'K%';

NOT IN

The NOT IN operator retrieves rows where a column’s value is not present in a given list of values.

-- Find customers whose age is not 25 or 32
SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 32);

Be cautious: If the list in NOT IN contains a NULL value, the query might not return any rows, as the logic can be unintuitive. It’s often safer to use NOT EXISTS.

IS NOT NULL

The IS NOT NULL condition is used to select rows that have a non-null (i.e., a defined) value in a specific column.

-- Find customers who have a defined age
SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;

Output

This query excludes ‘John Doe’, whose age is NULL.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT BETWEEN

NOT BETWEEN selects values that are outside a given range (the range itself is inclusive).

-- Find customers whose salary is not between 2000 and 8000
SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 2000.00 AND 8000.00;

NOT EXISTS and Performance Considerations

NOT EXISTS checks for the non-existence of rows in a subquery. It is often a more performant and safer alternative to NOT IN, especially when dealing with subqueries that might return NULL values.

Example: Finding Customers Without Orders

Let’s find all customers who have never placed an order. We’ll need an ORDERS table.

CREATE TABLE ORDERS(
    OID INT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    AMOUNT DECIMAL(18, 2)
);

INSERT INTO ORDERS VALUES
(102, 3, 3000.00),
(100, 3, 1500.00),
(101, 2, 1560.00),
(103, 4, 2060.00);

The NOT EXISTS query is generally very efficient.

-- Using NOT EXISTS (Recommended)
SELECT ID, NAME FROM CUSTOMERS c
WHERE NOT EXISTS (
    SELECT 1 FROM ORDERS o
    WHERE o.CUSTOMER_ID = c.ID
);

Another common and readable pattern to achieve the same result is using a LEFT JOIN and checking for NULL on the right side of the join.

-- Using LEFT JOIN / IS NULL (Also a great option)
SELECT c.ID, c.NAME FROM CUSTOMERS c
LEFT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
WHERE o.OID IS NULL;

Output (for both queries)

Both queries will return the customers who do not have any matching records in the ORDERS table.

ID	NAME
1	Ramesh
5	Hardik
6	Komal
7	Muffy
8	John Doe