NOT in SQL
NOT operator is a fundamental logical operator used to negate a condition in WHERE clause or to negate the result of another logical operator. This operator plays a crucial role in querying databases where we need to reverse the result . You can use more than one NOT operator in single SQL query based on your business requirement. This Operator provides flexibility to reverse the logical state of a condition.
Syntax
SELECT column1, column2, ...... FROM table_name WHERE NOT condition;
- NOT operator is a fundamental logical operator. It returns TRUE if the condition is FALSE and FALSE if the condition is TRUE.
- NOT Operator can be use to combine multiple conditions to perform operation on database table.
- NOT operator is use in the scenarios where you need to negate a condition or exclude certain rows from the result set.
- Use parentheses to group conditions for clarity and to control the order of operations .
- Try to avoid unnecessary NOT operators to maintain query efficiency and readability.
- NOT operator can be used to handle NULL values effectively.
Demo Database
we have 2 tables Users (Id, Name , Mobile ,Weight, DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)
NOT Operator Example
NOT operator is used to negate a condition. If the condition is FALSE it returns TRUE otherwise it returns FALSE.
SQL Query to fetch users which Weight is less than or equal to 50. we can fetch these users by using <= operator or by using NOT with > operator.
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE NOT Weight > 50;
OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE Weight <= 50;NOT with LIKE
LIKE Operator is used to match wildcard pattern. NOT operator can be combined with LIKE operator to fetch records where a pattern does not match.
NOT column_name LIKE 'pattern%' => This is used to fetch records where column_name does not start with pattern
SQL query to retrieve users those mobile number not start with 98
SELECT Id, Name, Mobile, DOB, Weight FROM Users WHERE NOT Mobile LIKE "98%";
NOT with BETWEEN
BETWEEN operator is used to define a range. NOT operator can be combined with BETWEEN operator to fetch records where records is not present in that range.
SQL Query to retrieve users, those Id is not BETWEEN range of 30 to 60.
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE NOT Id BETWEEN 30 AND 60;
OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE Id NOT BETWEEN 30 AND 60;NOT with comparison operators (=, !=, <, >, <=, >=)
Comparison operators are fundamental tools used to compare values in expressions and conditions within SQL statements. NOT operator can be used with comparison operators. When you use NOT operator with comparison operator it will reverse comparison operator nature.
SQL query to retrieve users, those Id is less than 15. we can retrieve these users by using < operator or by using NOT with >= operator.
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE NOT ID >= 15;
OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE ID < 15;NOT with IN
IN operator is used to specify multiple values in a WHERE clause. NOT operator can be used with IN operator. It will retrieve data that is not in list value.
SQL query to fetch user , who is not permanent user of India and USA.
SELECT UserId ,AddressType, Country
FROM UserAddress
WHERE AddressType ="Permanent" AND Country NOT IN ("USA", "India");