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 |
”;