Basic about NOT Condition – using and example

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

Description

The SQL NOT situation (sometimes referred to as the NOT Operator) is used to negate a circumstance 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 circumstance should be met for the record to be covered in the result set.

DDL/DML for Examples

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

Get DDL/DML

Example – Using NOT with the IN Condition

Let’s start through 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 desk known as merchandise 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:

Try It

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

There will be 4 files selected. These are the effects that you need to 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 desk the place the product_name is no longer Pear, Banana or Bread. Sometimes, it is more efficient to listing the values that you do now not want, as antagonistic to the values that you do want.

It is equivalent to the following SQL statement:

Try It

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 mix the NOT operator with the IS NULL condition, you create an IS NOT NULL situation that allows you to check for a non-NULL value. This is the advocated comparison operator to use in SQL when testing for non-NULL values. Let’s look at an example that indicates 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:

Try It

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 data selected. These are the outcomes that you must 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 documents from the merchandise table the place the customer_id does not incorporate a NULL value.

Example – Using NOT with the LIKE Condition

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

In this example, we have a desk known as suppliers with the following data:

supplier_id supplier_name metropolis 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 documents in the suppliers desk the place the supplier_name does not contain the letter ‘o’. Enter the following SQL statement:

Try It

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

There will be 1 file selected. These are the consequences that you must see:

supplier_id supplier_name city state 400 Kimberly-Clark Irving Texas

In this example, there is only one document in the suppliers desk the place the supplier_name does no longer contain the letter ‘o’.

Example – Using NOT with the BETWEEN Condition

The NOT operator can additionally be combined with the BETWEEN circumstance to create a NOT BETWEEN condition. Let’s discover an example that indicates how to use the NOT BETWEEN circumstance in a query.

In this example, we have a desk known as clients with the following data:

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

Enter the following SQL statement:

Try It

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

There will be two data selected. These are the effects that you see:

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

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

Try It

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

Example – Using NOT with the EXISTS Condition

Finally, the NOT circumstance can be mixed with the EXISTS circumstance to create a NOT EXISTS condition. Let’s seem to be at an example that suggests how to use the NOT EXISTS situation in SQL.

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

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

And a table known as 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:

Try It

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

There will be 2 files selected. These are the results that you must see:

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

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