Joins

[Dani]

I'm confused between LEFT JOIN and INNER JOIN - the differences and when it's appropriate to use one over the other. I tried to wrap my mind around it but it seems to me that INNER JOIN statements can always be rewritten as LEFT JOINs?

[campkev]

left joins will return all rows in the initial table regardless of whether or not they have a corresponding entry in the joined table. for rows that do not have a corresponding entry, null will be returned for all selected fields

inner joins will return only the rows in the initial that have a corresponding entry in the joined table.

for example, take the following setup

table a (id1 int)

has the follwing entries

1
2

table b (id1 int)
has the following entries

1

select * from a
INNER join b on a.id1 = b.id1

will return one row
1 1

select * from a
LEFT join b on a.id1 = b.id1

will return two rows
1 1
2 NULL

[campkev]

now for when you might want to do that.

Lets say you have started weekly and a monthly newsletter for this site, with the members having the option to sign up for either, neither or both.

Now let's say you wanted to get a list of all the members who signed up for the weekly list, you would use an inner join

Select Members.* from Members
inner join WeeklyNewsletter on Members.MemberID = WeeklyNewsletter.MemberID

this would return something like
1 bob [e-mail] 1
2 jane [e-mail] 2
and you would know that bob and jane are the only two members signed up for your weekly newsletter

Now let's say you wanted to get a list of ALL members and any lists they might be belong to. You would use a left join

select * from Members
Left Join weeklynewsletter on members.memberid = weeklynewsletter.memberid
Left Join monthlynewsletter on members.memberid = monthlynewsletter.memberid

this would return something like
1 bob [e-mail] 1 NULL
2 jane [e-mail] 2 2
3 joe [e-mail] NULL 3
4 beth [e-mail] Null NULL

and you would know that you have 4 members
bob is only signed up for weekly
jane is signed up for both
joe is only signed up for monthly
beth is signed up for neither

[Dani]

Awesome, thanks :) That answered my question.

[campkev]

no problem. Let me know if you need any more help with them.