CODEWITHSUNDEEP
sql
Baron
Baron

Reputation: 1

SQL How to include result not found in other table

I have 2 tables.

Table1 'Profile'

ID Name
01 John
02 Wayne
03 Eve

Table2 'Check_In'

PID CDate
01 2021-06-25
02 2021-06-25

If I use this query:

SELECT Profile.Name, Check_In.CDate 
FROM Profile
LEFT JOIN Check_In ON Check_In.PID = Profile.ID

I will get this result

Name CDate
John 2021-06-25
Wayne 2021-06-25
Eve null

This is the intended result, but because it will have check the whole Check_In table for each Profile record, the time used to generate the result is not favorable.

So I was looking into this query:

SELECT Profile.Name, Check_In.CDate 
FROM Check_In
LEFT JOIN Profile ON Check_In.PID = Profile.ID

But the result is

Name CDate
John 2021-06-25
Wayne 2021-06-25

Where Eve is missing (because Eve is a new personnel and her record has never existed in Check_In table), any method for me to include Eve in the result with null value for CDate as in the first scenario?

Upvotes: 0

Views: 145

Answers (2)

Sahil Duhan
Sahil Duhan

Reputation: 136

Yes, In case of left join it will take all the rows of the table that is on left side, so in the second query the left table is Check_In and it doesn't have Eve in it.

you can use the full outer join to get the Eve data in the result.

Use this query

SELECT Profile.Name, Check_In.CDate FROM Check_In FULL OUTER JOIN Profile ON Check_In.PID = Profile.ID

Upvotes: 1

marc_s
marc_s

Reputation: 754488

That's the reason - if you want to have all rows from Profile, then you need to use Profile In the FROM clause - and do a LEFT JOIN on Check_In - try this:

SELECT Profile.Name, Check_In.CDate 
FROM Profile
LEFT JOIN Check_In ON Check_In.PID = Profile.ID

The LEFT JOIN by definition returns all rows from the table to the left of the JOIN operator - and joins those rows from the right-hand table that exist (and returns NULL for those rows where the JOIN condition doesn't match the right-hand table).

Upvotes: 0

Related Questions