Here we will learn not operator in sql server, use not operator in sql server and how to use not with like, in, exists, between,etc.. operators in sql server with example.
The NOT operator in SQL is a negate operator that means it will show data for opposite of conditions what we mentioned in SQL statement. Generally we will use this NOT operator in EXISTS, BETWEEN, LIKE, IN, etc. We will check this sql not operator with example for that create “EmployeeDetails” table by using following script in your sql database
create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))
insert into EmployeeDetails
values(1,'suresh','software engineer',25000,'chennai'),
(2,'rohini','AEO',15000,'chennai'),
(3,'madhavsai','business analyst',50000,'nagpur'),
(4,'mahendra','CA',75000,'guntur'),
(5,'sateesh','Doctor',65000,'guntur')
select * from EmployeeDetails
Once we run above script our table “EmployeeDetails” will create and result will be like as shown below
Now run following examples to check NOT with LIKE, IN, EXISTS in sql server
The following SQL statement will return all employees with location not containing word 'en' anywhere in the location column because we used NOT keyword with LIKE operator and mentioned pattern like '%en%'.
SELECT * FROM EmployeeDetails WHERE Location NOT LIKE '%en%'
When we run above sql query we will get result of sql like operator with not keyword example will be like as shown below
Output
Following is the output of sql like operator with not keyword example
Generally in SQL statement if we use IN operator it will return data where column value in set of values. Suppose if we use NOT keyword with IN operator it will return data where column value not in set of values.
The following SQL statement will return all the records where the location not in mentioned values
SELECT * FROM EmployeeDetails WHERE Location NOT IN('chennai','guntur','bangalore')
Once we run above sql query we will get sql in operator with not keyword result will be like as shown below
Output
Following is the output of sql in operator with not keyword example
Generally in SQL statement if we use EXISTS operator it will return all the records when subquery return values. Suppose if we use NOT with EXISTS operator it will return all records when suquery not return any value.
The following SQL statement will not return any data because subquery will return records or rows
SELECT * FROM EmployeeDetails WHERE NOT EXISTS(SELECT * FROM EmployeeDetails WHERE empid =1)
When we run above sql query our sql not keyword with exists operator result will be like as shown below
Output
Following is the output of sql not keyword with exists example
