Exists Operator in SQL Server with Example

  Total Views : 433
  Comments : 1
 
  Keywords : SQL exists operator with example, SQL not exists operator with example, Use sql exists operator with subquery example, How to use sql exists operator with where clause or subquery in example

Here we will learn exists operator in sql server, not exists operator in sql server and how to use sql exists operator in subqueries or where clause with examples.

SQL EXISTS Operator

The EXISTS operator in SQL is used to show result if subquery returns data. Generally we will use this EXISTS operator in WHERE clause to check whether subquery returing results or not. Syntax for EXISTS operator in SQL will be like as shown below 

Syntax of SQL EXISTS Operator

Following sql statement represents syntax of sql exists operator

 

SELECT column1, column2 FROM tablename WHERE EXISTS(Subquery)

If you observe above SQL EXISTS operator syntax we will get values where subquery returns any data or rows otherwise it will not return any data. We will check this operator with example for that create “EmployeeDetails” table by using following script in your 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

 

Newly created employeedetails table in sql server

Now run following examples to check EXISTS operator in SQL Server.

SQL EXISTS Operator Example

The following SQL statement will return all employees details where subquery returns any data

 

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

Once we run above sql query our sql exists operator result will be like as shown below

 

Output

 

Following is the output of sql exists operator example

 

SQL Exists Operator Example Output or Result

SQL Exists Operator Example 2

The following SQL statement will return all employees details where subquery returns any data

 

SELECT * FROM EmployeeDetails WHERE EXISTS(SELECT * FROM EmployeeDetails WHERE empid =100)

When we run above sql query our sql exists operator result will be like as shown below

 

Output

 

Following is the output of sql exists operator example

 

SQL Exists Operator Example Result or Output

SQL NOT EXISTS Operator

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.

SQL Not Exists Operator Example

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 exists operator result will be like as shown below

 

Output

 

Following is the output of sql not exists operator example

 

SQL Exists Operator Example Result or Output

SQL Server Not Exists Operator Example 2

The following SQL statement will return data because subquery will not return any record or rows

 

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

When we run above sql query our sql not exists operator result will be like as shown below

 

Output

 

Following is the output of sql not exists operator example

 

SQL Not Exists Operator Example Output or Result

 Comments (1)

  • dileep tutlane registered profile image
     
    dileep   Reply

    good work...

     Feb 18, 2016

Leave a comment

  • Default Tutlane User