SQL Not with Like, In, Between Operators

In SQL, NOT operator is a negate operator which 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, BETWEEN, LIKE, IN, etc., based on our requirements. We will check this SQL not operator with an example for that create “EmployeeDetails” table by using the 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 the above script, a new table called “EmployeeDetails” will create and return the following result.

 

Newly created employeedetails table in sql server

Now run the following examples to check NOT with LIKE, IN, EXISTS in the SQL server.

SQL NOT with LIKE Operator Example 

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

 

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

When we execute the above SQL like operator with not keyword example, we will get the following result.

 

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 data whose column value is within the set of values. Suppose if we use NOT keyword with IN operator, it will return the data whose column value is not in the set of values. 

 

The following SQL statement will return all the records whose location column values are not in the mentioned values.

 

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

When we execute the above SQL IN operator with not keyword example, we will get the following result. 

 

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 match with the subquery values. Suppose if we use NOT with EXISTS operator, it will return all the records whose values do not match with the subquery returned values.

 

The following SQL statement will return data from EmployeeDetails whose values do not exist in subquery returned values.

 

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

When we execute the above SQL not keyword with the EXISTS operator statement, we will get the result below.

 

SQL Exists Operator Example Result or Output

Previous
 
Next