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?

Re: Joins 80 80

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

Re: Joins 80 80

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 bob@somewhere.com 1
2 jane jane@somewhere.com 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 bob@somewhere.com 1 NULL
2 jane jane@somewhere.com 2 2
3 joe joe@Somewhere.com NULL 3
4 beth beth@somewhere.com 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

Re: Joins 80 80

Awesome, thanks :) That answered my question.

Re: Joins 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.