How to use EXISTS Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 125

Exists returns TRUE if a subquery contains any rows. EXISTS is used when we want to test for the existence of rows specified by a subquery.

Let's create dbo.Customer and dbo.Country Table and then use EXISTS to return records for different scenarios.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)


Create table dbo.Country ( 
CId tinyint,
CountryName VARCHAR(50),
CountryShortName CHAR(2))
go
Insert into dbo.Country 
Values 
(1,'Pakistan','Pk'),
(2,'United States of America','US')

1) EXISTS will return TRUE if subquery contains any rows.

That is right, if our subquery will return any rows and we have used EXISTS, the outer query is going to return all rows.

Select * from dbo.Customer

WHERE Exists ( Select 1)

How to use EXISTS in SQL Server - SQL Server / TSQL Tutorial

Noticed that our subquery is static query ( Select 1). As subquery did return row and EXISTS returned TRUE so all the records from dbo.Customer table are displayed.

2) Use EXISTS and Join with SubQuery
The more real time example of EXISTS would be when we want to find all the records from dbo.Customer table which has matching CountryShortName from dbo.Country Table.

SELECT *
FROM dbo.Customer a
WHERE EXISTS
    (SELECT 1
     FROM dbo.Country b
     WHERE a.CountryShortName=b.CountryShortName)

Noticed that I have compared CountryShortName from dbo.Customer and dbo.Country. Each outer row is going to be compared with subquery results and if matches , then we get the row.

We can use the IN clause for same requirement.

SELECT *
FROM dbo.Customer a
WHERE a.CountryShortName IN
    (SELECT b.CountryShortName
     FROM dbo.Country b
     WHERE a.CountryShortName=b.CountryShortName)

How to use EXISTS in SQL Server to return matching records - SQL Server / TSQL Tutorial

How to use Exists and Not Exits in SQL Server