0

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?

2
Contributors
4
Replies
5
Views
11 Years
Discussion Span
Last Post by campkev
0

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

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.