mysql sql inner join question

penny336

hi all,

i want to ask what's diff between these two sql

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id and
table1.name like '%$name%'

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id and
table1.name like '%$name%'

Wien

Join syntax is very rich and you'll get everything you want at
Join

For your queries, the first one fetch all the fields in table1 that have a counter part in table2.
It's more restrictive than the second query that gets also the fields in table1 that has no match in table2, but with a NULL instead

Am I clear ? not sure...

Wien

your example doesn't really show the potential of LEFT JOIN
Let's say you have two tables User and Film and you want to fetch for each user all the films that a user has watched
If you don't use LEFT JOIN you will miss the users that hasn't watched any films...
With LEFT JOIN Query you will get
John film1
John film3
Ted film2
Ted film3
Ted film4
Alison NULL
Terry film1
...
...
you can't get 'alison' without LEFT JOIN
Must be clearer now

penny336

thx ur reply.

actually, i have a problem related to inner join.

there is 3 table..
say..
user(uid, name)
user_industry(uid, iid),
industry(iid, iname);
user_media(uid,mid)
media(mid, mname)
client can search by user name, and industry name, media name

some users may not belong to any industries or media

so that i need to use inner join?

if client just enter user name, media name
it seems that some hit records are missing if
use
"select * from user, media, industry,user_industry, user_media where user.uid = user_media.uid and user_media.mid = media.mid and user.uid = user_industry.uid and user_industy.iid= industry.iid"

for example,
user "alex" have some industry (ind1) but no media,
if client enter "alex" and "ind1 " will it hit???

i am not sure inner join can help or not..

any suggestion will be appreciate..

Alex

[deleted]

An INNER JOIN will only return a record if there is a match on the JOIN, so if there is a mathing record in the 'right' table.

Whereas a LEFT JOIN will allways return a result, but if there is no match in the right table, then the fields selected from that table will contain only NULL values.

So, joining customers to orders with a normal JOIN you will only see customers that have orders, but if you LEFT JOIN them you will see all customers, but some will have NULL values for the order-data.