SQL NOT OPERATOR

The SQL AND, OR, and NOT operators are used to filter records using one or more conditions. With the where or having clauses, the AND and OR operators are utilised. It's also possible to combine it with the NOT operators. The conjunctive operator is another name for the AND/OR operator.

The record is displayed only if the requirements separated by the AND operator are true. All of the conditions separated by the AND operator must be true in order for all of the records to be displayed; otherwise, they will not be displayed.

If one of the requirements separated by the OR operator returns true, the records are displayed.

If the condition is false, the NOT operator shows the record.

Example 1: Using all three AND, OR and NOT operators in a single SQL statement.

Table: EMPLOYEE

The following SQL statement will retrieve the information for employees above the age of 40 whose location is either "Theni" or NOT "Agra."

SELECT * FROM EMPLOYEE
WHERE AGE>40 AND (LOCATION='Chennai' OR NOT LOCATION='Agra');

Output:

Example 2: Let's suppose we want to select customers whose country is either UAE or SA, and age less than 26.

SELECT *
FROM Customers
WHERE (country = 'UAE' OR country = 'SA') AND age < 26;

SELECT *
FROM customers
WHERE NOT country = 'UAE' AND NOT last_name = 'Hari';

Here, the SQL command selects all customers whose country is not UAE and whose last_name is not Hari.

Example 3:

Select * from Employee 
where City=’Jalandhar’ 
AND (Emp_name=’Shivam’ OR Emp_name=’Karan’);

Output:

When the criteria in the WHERE clause are not met, the SQL NOT operator is used to filter the result set.

The logical operator, NOT, can be used to locate rows that do not satisfy a criteria.

The condition is reversed when NOT is used. The row is not returned if a condition is met.

Syntax:

SELECT column(s) FROM table_name WHERE NOT condition;

We utilise the NOT operator with the WHERE clause, as seen in the syntax given.

Example 1: Get the percentage of students whose gender is not male.

SELECT StudentPercent FROM Student WHERE NOT StudentGender = "M";

Output:

In the example above, we have used NOT operator to identify if the gender of the student is not male.

Example 2: If you want to find out the names of the students who do not play Baseball, the query would be like:

SELECT first_name, last_name, games
FROM student_details
WHERE NOT games = 'Football'

Output:

The NOT BETWEEN operator is used to compare all values against a range of values except those within that range. It returns all rows except the ones that were ignored.

The NOT BETWEEN operator is commonly used in conjunction with the WHERE clause in SELECT, INSERT, UPDATE, or DELETE statements.

When the NOT keyword is combined with the BETWEEN operator, data is returned in which the column value is not inside the provided range of values.

Syntax:

value NOT BETWEEN low AND high;
Or,
value < low OR value > high;

Example 1: For example,

SELECT item, amount
FROM Orders
WHERE amount NOT BETWEEN 300 AND 500;

Here, the SQL command selects all orders except the rows that have amounts between 300 and 500.

Example 2: In the following SQL query, we will see how to use NOT with BETWEEN operator in SQL. The following query will return all the employees details whose empname not in between 1 and 3 range.

SELECT * FROM EmployeeDetails WHERE empid NOT BETWEEN 1 and 3;

When we execute the above SQL not between operator example, we will get the result like as shown below.

Now we'll look at how to utilize the NOT keyword with the IN operator and how it returns records. When the NOT keyword is combined with the IN operator, any entries that do not appear in the list of values are returned.

Example 3: Here we will query for the payment whose amount is not between 3 USD and 5 USD, using the NOT BETWEEN operator in the “Finance” table of our sample database.

SELECT
    customer_id,
    payment_id,
    amount
FROM
    Finance
WHERE
    amount NOT BETWEEN 3
AND 5;

The not equal to operator (!=) compares two expressions that are not equal. That is, it determines whether or not one statement is equal to another.

If one or both operands are NULL, the result is NULL.

The not equal to operator (>) in SQL performs the same function.

When matching a string value, surround the string with quotations.

The not equal operator in SQL is used to determine whether two expressions are equal. If it's not equal, the condition will be true, and records that don't match will be returned. The only difference is that '<>' conforms to ISO standards, whereas '!=' does not.

Example 1: Let’s set up a sample table to explore SQL Not Equal operator:

CREATE TABLE dbo.Products
(ProductID         INT
 PRIMARY KEY IDENTITY(1, 1), 
 ProductName       VARCHAR(50), 
 ProductLaunchDate DATETIME2
);

Example 2: If we run following SQL statement for not equal operator it will return records where empid not equals to 1:

SELECT * FROM EmployeeDetails WHERE empid <> 1

If we run following SQL statement for not equal operator it will return a records where empid not equals to 1.

SELECT * FROM EmployeeDetails WHERE empid != 1

Example 3: For example, if we wanted to get information about all owners whose first name is not Homer, we could do the following:

SELECT *
FROM Owners
WHERE FirstName != 'Homer';

Output:

It is related to both <> and !=. Both test for values, but NULL does not—NULL is a placeholder for the existence of a value.

A column can have NULL values by default.

The NOT NULL constraint prevents NULL values from being stored in a column.

This requires a field to always have a value, which implies you can't add a value to this field while inserting or updating a record.

Note: IS NOT NULL is used to check if your value is not null, whereas <> 'YOUR VALUE' is used to check if your value is not null. I can't say whether my value corresponds to NULL or not, but I can state whether it is NULL or NOT NULL. If my value is not NULL, I can make a comparison.

Syntax:

SELECT * FROM MyTable WHERE MyColumn != NULL 
SELECT * FROM MyTable WHERE MyColumn <> NULL 

Example: Sql NOT NULL in creating a table

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

The SQL NOT EXISTS Operator will behave in a completely different way from the EXISTS Operator.

If the accompanying subquery produces no results, the NOT EXISTS operation returns true. If the inner subquery matches a single record, the NOT EXISTS operator returns false, and the subquery execution can be halted. In a SELECT, INSERT, UPDATE, or DELETE statement, the NOT EXISTS operator can be invoked.

If there are no rows, it returns TRUE; otherwise, it returns FALSE. Or, to put it another way, the SQL Server Not Exists operator will provide results that are diametrically opposed to those returned by the subquery.

Note: The NOT EXISTS Condition in MySQL is inefficient because the sub-query is re-run for each row in the outer query's table.

Syntax:

To check non existence of any record in a subquery, use the following syntax:

SELECT col1, col2,....
FROM tablename
WHERE NOT EXISTS (SELECT col1 FROM tablename WHERE condition); 

Parameters:

tablename: The table name from which you want to perform NOT EXISTS operator.

subquery: Usually a SELECT statement starts with SELECT * or column name. MySQL ignores the SELECT list from subquery.

Example 1:

SELECT A.accountName FROM tblbankdemataccountcharges A 
WHERE NOT EXISTS (SELECT B.banks FROM tblbankheadquartersandaddress B WHERE A.bankId = B.pk) LIMIT 5;

In the above example, the NOT EXISTS operator will return all records from the "tblbankdemataccountcharges" table where there is no record in the tblbankheadquartersandaddress table with the matching "bankId".

Example 2: To match all student records that have no associated student_grade with a value lower than 9, we can run the following SQL query:

SELECT id, 
first_name, last_name
FROM
    student
WHERE NOT EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

Output:

When running the query above, we can see that only the Alice record is matched:

The SQL NOT IN Operator has the exact opposite effect as SQL IN. The NOT IN operator compares a single item, often a column, to a comma-separated list of possible match values contained in parentheses. The value to the left of the NOT IN operator is then matched to the complete list, one by one, and an exact match to any one member of the list will result in the argument evaluating as false.

In SQL Server, the NOT IN Operator compares the provided expression or column name to the Values. If there is a match, the record will not be returned by the SELECT Statement.

NOT IN SQL operators are used with SELECT, UPDATE, and DELETE statements/queries to select, update, and delete only those records in a table that meet the WHERE clause and NOT IN operators' conditions. It filters records from a table according to the criterion.

Syntax:

Syntax for SQL NOT IN operators are given below.

SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 NOT IN (value1, value2, etc);

Where [condition] should be in the following format.

[column_name] [Operator] [Value];

Where,

[column_name] – Any one of the column names in the table.

[Operator] – Any one of the following (>, <, =, >=, <=, NOT, LIKE etc)

[Value] – User defined value.

Note: In the where clause, the IN and NOT IN operators can be used in any sequence and in any combination as needed.

Example 1: Get the percentage of students whose age is not in 12 or 13:

SELECT StudentPercent FROM Student WHERE StudentAge NOT IN ('12', '13');

Output:

StudentPercent
85
78

Example 2: The following SQL statement converts the 3 "LastEditedBy <>" arguments using a single NOT IN operator instead.

SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy NOT IN (11,17,13);

This query is functionally equivalent to the one above, but is shorter and easier to read.

Example 3: HOW TO USE IN & NOT IN OPERATORS IN A WHERE CLAUSE IN SELECT QUERIES:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age in (25, 29) AND City NOT IN (‘Chennai’, ‘Delhi’);

Example 4: The below NOT IN query will find all the Employees present in the Employees table whose [Yearly Income] is neither 45000 nor 50000:

SELECT [EmpID]
 ,[FirstName]
 ,[LastName]
 ,[Education]
 ,[Occupation]
 ,[YearlyIncome]
 ,[Sales]
 ,[HireDate]
  FROM [Employee]
  WHERE [YearlyIncome] NOT IN (45000, 50000)

The LIKE OPERATOR is the total opposite of the NOT LIKE OPERATOR.

It's used to match data through pattern matching approaches that explicitly exclude the given pattern from the query result set that matches the LIKE criterion.

Any string that does not match the pattern will return true.

returns either 1 (TRUE) or 0 (FALSE). The output is NULL if either expr or pat is NULL.

It is not necessary for the pattern to be a literal string.

Syntax:

expr NOT LIKE pat [ESCAPE 'escape_char']

Basically works upon these two patterns:

Percent ( % ) for matching any sequence of characters.

Underscore ( _ ) for matching any single character.

Example 1: You cannot combine like and in. The statement below would do the job though:

Select * from Table1 
where EmpPU NOT Like '%CSE%' 
AND EmpPU NOT Like '%ECE%' 
AND EmpPU NOT Like '%EEE%'

Example 2: Here we will make a query to find the customer in the “customer” table by looking at the “first_name” column to see if there is any value that doesn’t begin with “K” using the NOT LIKE operator in our sample database.

SELECT first_name,
        last_name
FROM
    customer
WHERE
    first_name NOT LIKE 'K%';

Example 3: This examples would return the exact opposite.

SELECT 'hello' NOT LIKE 'hello'    FALSE
SELECT 'hello' NOT LIKE 'h%'     FALSE
SELECT 'hello' NOT LIKE '_ell_'    FALSE
SELECT 'hello' NOT LIKE 'x'      TRUE

Note: It is just the negation of the LIKE operator.

NOT is a logical operator in SQL that you can use before any conditional statement to select rows where the statement is false.

Using NOT with < and > is typically unnecessary because the opposite comparative operator can be used alternatively.

The SQL NOT condition (also known as the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement's WHERE clause.

Syntax:

Parameter explanation

condition: It specifies the condition that you want to negate.

Example 1: Below is a selection from the “Customers” table in the Northwind sample database:

Example 1: The following SQL statement selects all fields from “Customers” where country is not “USA”

SELECT * FROM Customers WHERE NOT Country=’USA’;

Output:

Example 2: For example, this query will return an error:

SELECT * FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
 AND year_rank NOT > 3

When you combine the NOT operator with the IS NULL condition, you may test for a non-NULL value. When testing for non-NULL values in SQL, this is the comparison operator to use. Consider the following example, which demonstrates how to use the IS NOT NULL condition in a query.

Example 1: Using the same products as the previous example:

Enter the following SQL statement:

SELECT * FROM products
WHERE category_id IS NOT NULL;

Output:

This example will return all records from the products table where the cust_id does not contain a NULL value.

Example 2: NOT Operator with IS NULL condition

Retrieve all employees from the table "Employees" which follows the IS NOT NULL condition:

SELECT *  
FROM [javatpoint].[dbo].[Employees]  
WHERE name IS NOT NULL;