SQL - NOT Operator

---

Most of the times, there is a need to use two or more conditions to filter required records from a table; but sometimes satisfying either of a condition is enough for records filtering. There are also scenarios when you need to retrieve records that do not satisfy the conditions specified. SQL provides logical connectives for this purpose. They are listed below −

• AND − Operator

• OR − Operator

• NOT − Operator

With the help of these logical connectives, one can retrieve records that are required and also create exceptions for the records that are not needed to be retrieved.

SQL NOT operator

SQL NOT is a logical operator/connective used to negate a condition or Boolean expression in a WHERE clause. That is, TRUE becomes FALSE and vice versa.

The most common scenario where this operator can be used occurs when there is a specification of what NOT to include in the result table, instead of what to include.

For instance, in an Indian voting system, people younger than 18 years of age are NOT allowed to vote. Therefore, while retrieving the information of all people who are eligible to vote, using the NOT operator, we can create an exception to minors since it is the only specification.

Note − The NOT operator is always used in a WHERE clause so its scope within the clause is not always clear. Hence, a safer option to exactly execute the query is by enclosing the Boolean expression or a subquery by parentheses.

Syntax

Following is the syntax for SQL NOT operator −

NOT [CONDITION or BOOLEAN EXPRESSION]

Example

In the following example, let us first create a table to demonstrate the usage of NOT operator.

Using the query below, we are trying to create a table named Customers, which contains the personal details of customers including their name, age, address and salary etc. −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Using the following SELECT query, we will try to display the selected columns of table created above by applying constraints on the records using NOT operator −

SELECT * FROM CUSTOMERS
WHERE NOT (SALARY > 2000.00);

Output

On executing the query, the table displayed will only contain rows that have salary less than 2000.00 −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | Kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

SQL NOT Operator with LIKE

The LIKE operator uses wildcards to perform pattern matching on the records of a table before extracting the matched records.

However, to negate this operation (to extract the unmatched records instead), we can use the NOT operator along with LIKE in the form of NOT LIKE keyword.

Example

Using the following query, we are trying to display the filtered rows of the Customers table above −

SELECT * FROM CUSTOMERS
WHERE NAME NOT LIKE 'K%';

Output

On executing the query above, the table will be displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SQL NOT Operator with IN

The IN operator returns TRUE if the values in a table column belong to a range of numbers specified in the WHERE clause.

To negate this operation, we can use the NOT IN operator instead. With this, the Boolean expression returns TRUE if the records are not present in the given range.

Example

In the following example, we are trying to display the filtered rows of Customers table with the help of the query given below −

SELECT * FROM CUSTOMERS
WHERE AGE NOT IN (25, 26, 32);

Output

The result table is displayed as follows −

+----+---------+-----+---------+----------+
| ID | NAME    | AGE | ADDRESS | SALARY   |
+----+---------+-----+---------+----------+
|  3 | Kaushik |  23 | Kota    |  2000.00 |
|  5 | Hardik  |  27 | Bhopal  |  8500.00 |
|  6 | Komal   |  22 | MP      |  4500.00 |
|  7 | Muffy   |  24 | Indore  | 10000.00 |
+----+---------+-----+---------+----------+

SQL NOT Operator with IS NULL

The IS NULL operator is used to check whether the records in a table are NULL. If a NULL value is encountered, it returns TRUE; and FALSE otherwise.

Using NOT operator with the IS NULL operator, we can extract all the records that does not contain NULL values.

Example

Using the query given below, we are trying to filter the NULL records present in the Customers table (if any) −

SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Output

The result table is exactly as the original table as it contains no NULL values −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

However, if the table contains any NULL values, the rows containing it will be omitted in the resultant table.

SQL NOT Operator with BETWEEN

BETWEEN operator is used to establish a range as a condition. When used with WHERE clause, this operator acts like a Boolean expression. That is, if values of a table column fall in the specified range, TRUE is returned; and FALSE otherwise.

Using NOT BETWEEN operator with WHERE clause will return its negation. That is, if values of a table column fall in the specified range, FALSE is returned; and TRUE otherwise.

Example

With the given query below, let us try to display records in the Customers table whose salary does not fall between 1500.00 and 2500.00 −

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1500.00 AND 2500.00;

Output

The resultant table is as follows −

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

SQL NOT Operator with EXISTS

The EXISTS operator works similar to the IN operator; it compares the table records with the specified range in the WHERE clause. However, the IN operator cannot compare the NULL records with the range while EXISTS does.

The NOT EXISTS operator is used to negate this operation.

Example

In the following example, let us create another table Orders to help in demonstrating the usage of NOT operator with EXISTS operator −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2009-10-08 00:00:00', 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2009-10-08 00:00:00', 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2009-11-20 00:00:00', 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+

Following query is used to print the IDs of customers in CUSTOMERS table that do not exist in the ORDERS table −

SELECT * FROM CUSTOMERS
WHERE NOT EXISTS (SELECT CUSTOMER_ID FROM ORDERS WHERE ORDERS.CUSTOMER_ID = CUSTOMERS.ID);

Output

The output obtained after executing the query is as follows −

+----+--------+-----+-----------+----------+
| ID | NAME   | AGE | ADDRESS   | SALARY   |
+----+--------+-----+-----------+----------+
|  1 | Ramesh |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik |  27 | Bhopal    |  8500.00 |
|  6 | Komal  |  22 | MP        |  4500.00 |
|  7 | Muffy  |  24 | Indore    | 10000.00 |
+----+--------+-----+-----------+----------+