The SQL Not Operator Explained with Syntax Example

When querying data from a SQL database, you often need to filter the result set to only include rows matching certain criteria. The WHERE clause allows you to specify conditions that rows must satisfy to be included in the results. Usually, the conditions select rows where an expression evaluates to true, like WHERE status = ‘active‘ or WHERE age > 18.

But sometimes you need to do the opposite – exclude rows that match a condition and only keep the rest. That‘s where the NOT logical operator comes in handy. In this article, we‘ll take an in-depth look at the SQL NOT operator and demonstrate how to use it in various scenarios with code examples.

What Is the SQL NOT Operator?

The NOT operator is a logical operator used in boolean expressions to negate or reverse a condition. When you apply NOT to a boolean expression, it evaluates to the opposite value. So NOT(true) evaluates to false, and NOT(false) evaluates to true.

In SQL, the NOT operator is commonly used in the WHERE clause of SELECT statements to exclude rows that satisfy a certain condition. It allows you to select all rows where a condition is not true. This is useful when you want to filter out a subset of rows and keep everything else.

NOT Operator Syntax and Usage

The general syntax for using the NOT operator in a WHERE clause is:

WHERE NOT (condition)

The condition can be any valid boolean expression, such as a comparison using =, !=, >, <, etc. or even another logical expression using AND or OR. The NOT operator is applied to the entire condition inside the parentheses.

Here are a few examples to illustrate:

-- Select all rows where status is not ‘cancelled‘
SELECT * FROM orders
WHERE NOT (status = ‘cancelled‘);

-- Select rows where age is not greater than 18 
SELECT * FROM users
WHERE NOT (age > 18);

-- Select rows where answer is not yes or no
SELECT * FROM survey 
WHERE NOT (answer IN (‘yes‘, ‘no‘));

In each case, the NOT operator inverts the result of the condition, causing the WHERE clause to only include rows where the condition evaluates to false.

Code Examples Using NOT

Let‘s look at more detailed code examples of using the NOT operator in various scenarios.

Using NOT with Equality Comparison on Strings

Suppose we have a customers table with columns: id, name, state, status. To select all customers who are not from California (CA):

SELECT * FROM customers
WHERE NOT (state = ‘CA‘);

This query will return all rows where the state column is not equal to ‘CA‘. It excludes customers from California but includes customers from all other states.

Using NOT with Inequality Comparison on Numbers

Given an orders table with columns: id, customer_id, amount, items. To select orders with less than 5 items:

SELECT * FROM orders
WHERE NOT (items >= 5);

The query filters out orders with 5 or more items and only returns those with less than 5 items.

Using NOT with Dates

For a bookings table with columns: id, customer_id, checkin_date, checkout_date. To find all bookings where the check-in date is not in the year 2022:

SELECT * FROM bookings
WHERE NOT (checkin_date BETWEEN ‘2022-01-01‘ AND ‘2022-12-31‘);

This query selects all bookings with a check-in date that does not fall within the range of 2022-01-01 to 2022-12-31. It includes bookings from all years except 2022.

Combining NOT with AND and OR

You can use the NOT operator in combination with other logical operators like AND and OR to create more complex conditions. For example, to select students who are not freshmen or seniors:

SELECT * FROM students 
WHERE NOT (grade = ‘Freshman‘ OR grade = ‘Senior‘);

This query will only include students who are not in the Freshman or Senior classes, so Sophomores and Juniors.

To find customers who are not from either California or New York and have a status of ‘active‘:

SELECT * FROM customers
WHERE NOT (state IN (‘CA‘, ‘NY‘)) AND status = ‘active‘;  

Order of Logical Operator Precedence

When using the NOT operator with AND and OR in the same expression, it‘s important to understand the order of precedence. Logical operators are evaluated in this order:

1. NOT
2. AND
3. OR

So in the expression NOT a AND b OR c, the NOT is applied to a first, then the AND, then OR. To change the order, use parentheses to group operations appropriately.

For example, NOT (a AND b) OR c will evaluate the AND first, then apply NOT to the result, then OR. But (NOT a) AND (b OR c) will apply NOT to a, then evaluate b OR c, then AND the two results.

When to Use the NOT Operator

The NOT operator is handy when you want to exclude a subset of rows that match a condition and keep all other rows. Some common use cases include:

• Finding non-matching values, e.g. customers who are not from a certain state
• Selecting rows that don‘t fall within a range, e.g. dates not between X and Y
• Eliminating rows satisfying unwanted conditions, e.g. orders that are not ‘complete‘
• Inverting complex conditions created with AND/OR for better readability

However, there are often other ways to achieve the same result without using NOT.

NOT vs Other Ways to Exclude Rows

In many cases, you can exclude rows by using alternative comparison operators instead of NOT. For example, these two queries are equivalent:

SELECT * FROM customers 
WHERE NOT (state = ‘CA‘);

SELECT * FROM customers
WHERE state != ‘CA‘;

Both queries will select customers who are not from California. The second option simply uses the inequality operator != rather than NOT with equality.

Similarly, you can use the NOT IN operator to exclude rows that match any value in a list:

SELECT * FROM customers
WHERE state NOT IN (‘CA‘, ‘NY‘);

This has the same effect as:

SELECT * FROM customers  
WHERE NOT (state = ‘CA‘ OR state = ‘NY‘);

In general, if there‘s a clearer way to express a condition without using NOT, it‘s better to use that for readability. But in cases where the condition is more complex, using NOT can make the query easier to understand.

Performance Considerations

When using the NOT operator, keep in mind that it can impact query performance, especially with large datasets. Applying NOT to a condition essentially means the database has to evaluate the condition for every row to determine if it should be excluded.

For conditions that only match a small percentage of rows, using NOT can be inefficient because the database does extra work to exclude a majority of rows. In these cases, it‘s often faster to flip the condition and select the smaller matching subset directly.

However, if a condition matches a large portion of the data, using NOT to exclude the smaller non-matching subset can be effective. The query optimizer may be able to use an index to quickly locate the non-matching rows.

As with any query, it‘s important to examine the execution plan and test different approaches to see which one gives the best performance for your specific scenario.

Practice Problems

To solidify your understanding of the NOT operator, try solving these practice problems:

1. From an employees table, select employees who are not in the ‘Sales‘ or ‘Marketing‘ departments.

2. Find all products that are not in the ‘Electronics‘ category and cost less than $100.

3. Select students whose last name does not start with the letter ‘S‘.

4. Find orders that were not placed in the year 2022 and have a status of ‘pending‘.

5. Select user accounts that were not created between ‘2023-01-01‘ and ‘2023-06-30‘ and are currently ‘active‘.

(See answers at end of article)

Conclusion

The SQL NOT operator is a useful tool for filtering out rows that match a certain condition. By applying NOT to a boolean expression in the WHERE clause, you can select all rows where the condition is not true.

In this article, we explained the syntax and usage of the NOT operator and provided several code examples demonstrating how to use it with different data types and in combination with other logical operators. We also discussed performance considerations, alternative ways to exclude rows, and gave some practice problems to reinforce the concepts.

Mastering the NOT operator will allow you to write more powerful and flexible queries to extract insights from your SQL databases. While it‘s not always the best approach, being able to wield NOT effectively is a valuable skill for any SQL developer or data analyst to have.

Answers to practice problems:

1. SELECT * FROM employees WHERE NOT (department IN (‘Sales‘, ‘Marketing‘));
2. SELECT * FROM products WHERE NOT (category = ‘Electronics‘) AND price < 100;
3. SELECT * FROM students WHERE NOT (last_name LIKE ‘S%‘);
4. SELECT * FROM orders WHERE NOT (order_date BETWEEN ‘2022-01-01‘ AND ‘2022-12-31‘) AND status = ‘pending‘;
5. SELECT * FROM users WHERE NOT (created_at BETWEEN ‘2023-01-01‘ AND ‘2023-06-30‘) AND status = ‘active‘;