SQL Not with Like, In, Between Operators

In sql, NOT operator is a negate operator that means it will return a result as just opposite for the defined conditions in sql statements.

 

In sql, we can use a NOT operator with EXISTS, BETWEENLIKEIN, etc. based on our requirements. 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 execute above script, a new table called “EmployeeDetails” will be created and result will be like as shown below

 

Newly created employeedetails table in sql server

Now run following examples to check NOT with LIKE, IN, EXISTS in sql server

SQL NOT with LIKE Operator Example 

The following sql not operator with like statement will return all the employees whose location not containing a word 'en' anywhere in the location column because we used a NOT keyword with LIKE operator and mentioned a pattern like '%en%'.

 

SELECT * FROM EmployeeDetails WHERE Location NOT LIKE '%en%'

When we execute above sql like operator with not keyword example, we will get the result like as shown below.

 

SQL Server Like Operator with Not Keyword Example

SQL NOT with IN Operator Example

Generally, if we use IN operator in sql it will return a data whose column value within the set of values. Suppose if we use NOT keyword with IN operator, then it will return the data whose column value not in the set of values. 

 

Following sql statement will return all the records whose location column values not in the mentioned values.

 

SELECT * FROM EmployeeDetails WHERE Location NOT IN('chennai','guntur','bangalore')

When we execute above sql IN operator with not keyword example, we will get the result like as shown below. 

 

SQL Server IN Operator with Not Keyword Example Result or Output

SQL NOT with EXISTS Operator Example

In sql, if we use EXISTS operator it will return all the records whose values matches with the subquery values. Suppose if we use NOT with EXISTS operator, then it will return all the records whose values not matching with the suquery returned values.

 

Following sql statement will return a data from EmployeeDetails whose values not exist in subquery returned values.

 

SELECT * FROM EmployeeDetails WHERE NOT EXISTS(SELECT * FROM EmployeeDetails WHERE empid =1)

When we execute above sql not keyword with EXISTS operator statement, we will get the result like as shown below.

 

SQL Exists Operator Example Result or Output

 
 
 

Topics Covered