SQL NOT Operator

The SQL NOT operator is used to reverse the boolean result of a condition in SQL. It helps in retrieving records that do not match a specific condition. It is mostly used to specify what should not be included in the results table.

Example: First, we create a demo SQL database and table, on which we will use the NOT Operator command.

Query:

SELECT CustomerName, LastName
FROM Customer
WHERE NOT LastName = 'Tanaka';

Output:

Syntax:

SELECT column1, colomn2, … 
FROM table_name WHERE NOT condition; 

Examples of SQL NOT Operator

Let's understand NOT in SQL with examples. First, we will create a demo SQL database and table, on which we will use the NOT command.

Example 1: Using SQL NOT to Exclude a Specific Value

The following SQL statement selects all fields from Customers table where the country is not UK.

Query:

SELECT * 
FROM Customers
WHERE NOT Country = 'UK';

Output:

In this example, the NOT operator filters out customers from the UK and returns all other customers.

Example 2: Using SQL NOT with IN Operator

The NOT operator can also be used with the IN condition to exclude multiple values from the result set.

Query:

SELECT * 
FROM Customers
WHERE NOT Country IN ('USA', 'UK');

Output:

Here, the NOT IN condition filters out customers from both the USA and UK and returns only customers from other countries.

Example 3: Using SQL NOT with LIKE Operator

We can also combine NOT with the LIKE operator to exclude records that match a certain pattern.

Query:

SELECT * 
FROM Customers
WHERE NOT CustomerName LIKE 'R%';

Output:

In this query, the NOT LIKE condition filters out customers whose name starts with the letter 'R', returning all others.

Example 4: Using SQL NOT with NULL Values

To exclude records where a column has a NULL value, combine NOT with the IS NULL condition.

Query:

SELECT * 
FROM Customers
WHERE NOT PostalCode IS NULL;

Output:

This query excludes customers who have a NULL value for PostalCode.

Example 5: Using NOT with AND Operator

We can combine NOT with the AND operator to create more complex conditions. This query retrieves customers who are not from the USA and are also not from the UK.

Query:

SELECT * 
FROM Customers
WHERE NOT Country = 'USA' AND NOT Country = 'UK';

Output:

Note:

• NOT operator returns opposite results or negative results. It negates boolean condition in the WHERE clause.
• It is used to exclude specific data from the result set.
• It can also be combined with other operators like- LIKE, BETWEEN, and IN.