SELECT query..

pearl2 · #1 (**permalink**) 01-01-04, 23:52

Hi,

I was wondering if the following can be improved using NATURAL JOIN:

SELECT *
FROM logins, profiles, avatars, prefs, groups, tmpl
WHERE username='john'
AND logins.some_id = profiles.some_id
AND profiles.some_id=prefs.some_id
AND profiles.avatar_id=avatars.avatar_id
AND profiles.group_id=groups.group_id
AND prefs.tmpl_id=tmpl.tmpl_id

Thanks

roga · #2 (**permalink**) 01-02-04, 09:03

Assuming that 'some_id' exists in profiles and prefs, try this:

Code:

SELECT *
FROM logins
LEFT JOIN profiles USING (some_id)
LEFT JOIN prefs USING (some_id)
LEFT JOIN avatars ON profiles.avatar_id=avatars.avatar_id
LEFT JOIN groups ON profiles.group_id=groups.group_id
LEFT JOIN tmpl ON prefs.tmpl_id=tmpl.tmpl_id
WHERE username='john'

I'm not sure if this works too:

Code:

SELECT *
FROM logins
LEFT JOIN profiles USING (some_id)
LEFT JOIN prefs USING (some_id)
LEFT JOIN avatars USING (avatar_id)
LEFT JOIN groups USING(group_id)
LEFT JOIN tmpl USING(tmpl_id)
WHERE username='john'

pearl2 · #3 (**permalink**) 01-03-04, 22:58

Great thanks, roga!

Code:

SELECT *
FROM logins
LEFT JOIN profiles USING (some_id)
LEFT JOIN prefs USING (some_id)
LEFT JOIN avatars ON profiles.avatar_id=avatars.avatar_id
LEFT JOIN groups ON profiles.group_id=groups.group_id
LEFT JOIN tmpl ON prefs.tmpl_id=tmpl.tmpl_id
WHERE username='john'

The version about works but the other one doesn't.

I'm curious if there're any performance differences between the one you gave above and the one I've originally using AND, reproduced below:

Code:

SELECT *
FROM logins, profiles, avatars, prefs, groups, tmpl
WHERE username='john'
AND logins.some_id = profiles.some_id
AND profiles.some_id=prefs.some_id
AND profiles.avatar_id=avatars.avatar_id
AND profiles.group_id=groups.group_id
AND prefs.tmpl_id=tmpl.tmpl_id

Thanks in anticipation

r937 · #4 (**permalink**) 01-04-04, 02:34

Quote:

I'm curious if there're any performance differences between the one you gave above and the one I've originally using AND

performance differences? probably

but more significant is the difference in results

your original query used inner joins, whereas roga's used left joins!!

the results would be the same only if there happened to be no unmatched rows

also, while the mysql manual is not very clear about this, a NATURAL join in standard sql does not have a USING clause, rather, it joins tables based on all identically named columns

so the original query re-written with NATURAL joins would be

SELECT *
FROM logins
NATURAL JOIN profiles
NATURAL JOIN avatars
NATURAL JOIN prefs
NATURAL JOIN groups
NATURAL JOIN tmpl
WHERE username='john'

if you use the convention of naming an auto_increment column as "id" and a relationship column as "some_id" then both tables would have an "id" column and the NATURAL join would try to join based on the that, which would of course be incorrect

pearl2 · #5 (**permalink**) 01-04-04, 02:45

Thanks, r937!

Quote:

the results would be the same only if there happened to be no unmatched rows

So I should be using LEFT JOIN instead of AND?

Can you give an example or perhaps elaborate on when LEFT JOIN should be used?

r937 · #6 (**permalink**) 01-04-04, 02:53

a LEFT join should be used whenever you want all rows from the left table, together with matching rows from the right table, whereas with an INNER join, you get only matching rows

consider

Pets
1 dog
2 cat
3 bird
4 ferret

People
35 curly
38 larry
39 moe

PeoplePets
35 2
35 3
39 1

list all people, and their pets if any (LEFT join) --

curly cat
curly bird
larry NULL
moe dog

list all people with pets (INNER join) --

curly cat
curly bird
moe dog

pearl2 · #7 (**permalink**) 01-04-04, 04:53

Thanks for your example! I think I roughly get the idea

cheers

Internet Services

Web Development

Digital Marketing

Consumer Tech