SQL NOT condition

Preamble

The SQL condition NOT (sometimes called the NOT operator) is used to override the condition in the WHERE sentence of the SELECT, INSERT, UPDATE or DELETE operator.

Syntax of NOT condition in SQL

NOT condition_id

Arguments or parameters

• To include an entry in the result set, the opposing condition must be chosen. condition – This is a condition for denial.

Use NOT with IN condition

Let’s begin by looking at how NOT can be used with the IN condition. The NOT IN condition is created when the NOT operator is combined with the IN condition. The expression will be checked to see if it is not on the list.

In this illustration, the product table contains the information below:

Type the SQL query in the box provided.

SELECT *
FROM prods
WHERE prod_name NOT IN ('Pear', 'Banana', 'Bread');

There will be four entries chosen. You should see the outcomes listed below.

The products table will be searched for all rows in this example where prod_name does not match Pear, Banana, or Bread. In some cases, it is more effective to list the values you do not want rather than the values you do.

The following SQL statement is equivalent to this.

SELECT *
FROM prods
WHERE prod_name <> 'Pear'
AND prod_name <> 'Banana'
AND prod_name <> 'Bread';

Using NOT with IS NULL condition

When you combine the NOT operator with the IS NULL condition, you create the IS NOT NULL condition which allows you to check a value other than NULL. This is a recommended comparison statement for use in SQL when checking values other than NULL.

Let’s look at an example which shows how to use the IS NOT NULL condition in a query.

Using the same products as in the previous example.

Enter the following SQL statement.

SELECT *
FROM prods
WHERE cat_id IS NOT NULL;

6 records will be selected. Here are the results that you should get.

In this example, all records from the products table will be returned where customer_id is not NULL.

Using NOT with LIKE condition

Next, let’s look at an example of using the NOT operator with the LIKE condition.
In this illustration, we have a table with the information below:

Let’s look at each entry in the Suppliers table where the letter “o” is absent from Supplier_name. Type the SQL query in the box provided.

SELECT *
FROM suppls
WHERE suppl_name NOT LIKE '%o%';

Four entries will be chosen. These are the results you want to achieve.

In this example, there are four entries in the suppliers table where suppl_name does not contain liters ‘o’.

Using NOT with BETWEEN condition

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

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

Enter the following SQL statement.

SELECT *
FROM customs
WHERE custom_id NOT BETWEEN 5000 AND 8000;

Two records will be chosen. Here are the results that you should get.

This will return all lines where customer_id is not in the range between 5000 and 8000 inclusive. This would be equivalent to the next SELECT operator.

SELECT *
FROM customs
WHERE custom_id < 5000
OR custom_id > 800;

Using NOT with EXISTS condition

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

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

also a table with the following information:

Enter the following SQL statement.

SELECT *
FROM customs
WHERE DOES NOT EXIST
(SELECT *
FROM ords
WHERE customs.custom_id = ords.custom_id);

Two entries will be chosen. The outcomes you should see are listed below.

Since the order table does not contain any records for this custom_id, all records from the customer’s table are returned in this example.

AND OR and NOT Operators in sql

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.