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|