SQL Server: Tips and Tricks - 2 (2017)
How to use EXISTS Logical Operator in SQL Server
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)
|
|
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)
|
|
