SQL: NOT Condition

This SQL tutorial explains how to use the SQL NOT condition with syntax and examples.

Description

The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the NOT condition in SQL is:

NOT condition

Parameters or Arguments

condition

This is the condition to negate. The opposite of the condition must be met for the record to be included in the result set.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using NOT with the IN Condition

Let's start by looking at how to use NOT with the IN condition. When we use the NOT operator with the IN condition, we create a NOT IN condition. This will test to see if an expression is not in a list.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread');

There will be 4 records selected. These are the results that you should see:

product_id	product_name	category_id
3	Orange	50
4	Apple	50
6	Sliced Ham	25
7	Kleenex	NULL

This example would return all rows from the products table where the product_name is not Pear, Banana or Bread. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

It is equivalent to the following SQL statement:

SELECT *
FROM products
WHERE product_name <> 'Pear'
AND product_name <> 'Banana'
AND product_name <> 'Bread';

Example - Using NOT with the IS NULL Condition

When you combine the NOT operator with the IS NULL condition, you create an IS NOT NULL condition that allows you to test for a non-NULL value. This is the recommended comparison operator to use in SQL when testing for non-NULL values. Let's look at an example that shows how to use the IS NOT NULL condition in a query.

Using the same products as the previous example:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25

This example will return all records from the products table where the customer_id does not contain a NULL value.

Example - Using NOT with the LIKE Condition

Next, let's look at an example of how to use the NOT operator with the LIKE condition.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Let's look for all records in the suppliers table where the supplier_name does not contain the letter 'o'. Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name NOT LIKE '%o%';

There will be 1 record selected. These are the results that you should see:

supplier_id	supplier_name	city	state
400	Kimberly-Clark	Irving	Texas

In this example, there is only one record in the suppliers table where the supplier_name does not contain the letter 'o'.

Example - Using NOT with the BETWEEN Condition

The NOT operator can also be combined with the BETWEEN condition to create a NOT BETWEEN condition. Let's explore an example that shows how to use the NOT BETWEEN condition in a query.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 5000 AND 8000;

There will be 2 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
9000	Johnson	Derek	google.com

This would return all rows where the customer_id was NOT between 5000 and 8000, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 5000
OR customer_id > 8000;

Example - Using NOT with the EXISTS Condition

Finally, the NOT condition can be combined with the EXISTS condition to create a NOT EXISTS condition. Let's look at an example that shows how to use the NOT EXISTS condition in SQL.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

And a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

Enter the following SQL statement:

SELECT *
FROM customers
WHERE NOT EXISTS
  (SELECT * 
   FROM orders
   WHERE customers.customer_id = orders.customer_id);

There will be 2 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
6000	Ferguson	Samantha	bigactivities.com
9000	Johnson	Derek	google.com

This example would return all records from the customers table where there are no records in the orders table for the given customer_id.