Concept of Exists Condition in SQL Server

This article shows how to use the EXISTS condition in SQL Server. The EXISTS condition is used to check if the row exists or not in the table. EXISTS queries are very inefficient since the sub-query is re-run for every row in the outer query's table. In this article we define both conditions of exists. One is EXISTS and other is NOT EXISTS. Usually we create two table in SQL Server named T1 And T2. T1 table has two columns with the names id and name. The T2 table has also two columns with the names id and Salary. 

Creating Table in SQL Server Database
 
Now create a table named T1 with the following fields. The table looks as below:

img1.jpg

Now insert rows of data into the table. After that use a select statement on the table.

 

img2.jpg

Now create a table named T2 with the following fields. The table looks as below.

img3.jpg

Now insert rows of data into this table. After that use a select statement on the table.

 

img4.jpg

 

 

Exists Condition

The following query defines only those rows which exists in any table. 

SELECT  [Id],

    [Name]

  FROM [Rohatash].[dbo].[T1]

  go

  SELECT [Id]

      ,[Salary]

  FROM [Rohatash].[dbo].[T2]

  go

select id, salary from t2 where  exists ( select * from t1 where t1.id=t2.id)

OUTPUT

img7.jpg

We can also get the same result with the following query:

select id, salary from t2 where t2.id in ( select id from t1 )

Not Exists Condition

The following query define only those rows which does not exists in any table:

SELECT  [Id],

    [Name]

  FROM [Rohatash].[dbo].[T1]

  go

  SELECT [Id]

      ,[Salary]

  FROM [Rohatash].[dbo].[T2]

  go

  select id, salary from t2 where Not exists ( select * from t1 where t1.id=t2.id)

OUTPUT

img8.jpg

We can also get the same result with the following query:

select id, salary from t2 where t2.id Not in ( select id from t1 )

Up Next
    Ebook Download
    View all
    Learn
    View all