50 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 one of the conditions would be enough. 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.

The 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.

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 creating 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 VALUES 
(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, 'Hyderabad', 4500.00 ),
(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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The SQL query below retrieves all rows from the 'CUSTOMERS' table where the 'SALARY' column is not greater than 2000.00 −

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

Output

Following is the output of the above query −

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 retrieving all rows from the 'CUSTOMERS' table where the 'NAME' column does not start with the letter 'K' −

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

The following SQL query selects all rows from the 'CUSTOMERS' table where the 'AGE' column does not have values 25, 26, or 32 −

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	Hyderabad	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

This SQL query retrieves all rows from the 'CUSTOMERS' table where the 'AGE' column is not null, i.e. it contains valid age values −

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	Hyderabad	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, we are displaying 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	Hyderabad	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 VALUES 
(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);

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Search This Blog

Programming Tutorials