JOIN VS EXISTS - WHAT TO USE WHEN

Let's say i have a table 'Person' that holds three basic fields and looks somewhat like the following: 

Person

There is another table called 'Pets' that holds the names of all presidential pets and what kind of animal it is.

Pets

Requirement:

Let's say that we want to find the details of all persons who share their name with that of a pet. There are two options here:

We can join both the above tables to get the details. The query will look somewhat like this:

select
ps.Name,
ps.Surname,
ps.State from
Person ps, 
Pets pt  where
ps.Name = pt.PetName

The result of the above query will look somewhat like this:

But is this what we want? Of course not ! The question then arises that why am i viewing four rows instead of two? This is because join creates sort of a cross product of the matching result set (hence all possible combinations of the two tables for the matching Name/Petname (Sunny) is shown. So what is the alternative ? Use EXISTS. The following code snippet illustrates this:

select
ps.Name,
ps.Surname,
ps.State from
Person ps where exists
(select 1 from Pets pt where pt.PetName = ps.Name)

The result set is shown below which is what we want to see.

Lesson:

Use EXISTS instead of JOIN when:

•You don't need to return data from the related table

•You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)

•You want to check existence