A performance question in select statement

#1 (**permalink**) 02-28-2008, 09:24 AM

In response to my last question, Captain Paralytic send a diferente
select statement to me. My own select was like this:

SELECT u.id, u.usuario, g.grupo
FROM usuarios u, grupos g, usuarios_grupos ug
WHERE u.id = ug.usuario_id AND g.id = ug.grupo_id

The select of Captain Paralytic was like this:

SELECT u.id, u.usuario, g.grupo
FROM usuarios_grupos ug
JOIN usuarios u ON ug.usuario_id = u.id
JOIN grupos g ON ug.grupo_id = g.id

They return exactly the same results, so my question now is: Is there
any performance issue that I'm missing, or the two statements are
pretty much the same? Is all about syntax only? Which aproach is
better??? Why???

#2 (**permalink**) 02-28-2008, 09:24 AM

On 15 Mar, 14:40, "rics" <ricardo.ce...@gmail.com> wrote:
> In response to my last question, Captain Paralytic send a diferente
> select statement to me. My own select was like this:
>
> SELECT u.id, u.usuario, g.grupo
> FROM usuarios u, grupos g, usuarios_grupos ug
> WHERE u.id = ug.usuario_id AND g.id = ug.grupo_id
>
> The select of Captain Paralytic was like this:
>
> SELECT u.id, u.usuario, g.grupo
> FROM usuarios_grupos ug
> JOIN usuarios u ON ug.usuario_id = u.id
> JOIN grupos g ON ug.grupo_id = g.id
>
> They return exactly the same results, so my question now is: Is there
> any performance issue that I'm missing, or the two statements are
> pretty much the same? Is all about syntax only? Which aproach is
> better??? Why???

If you do an explain on the 2 selects you will find that in this case
they are likely to be identical.

My proposed query was more obvious in its intentions. Also, my one can
easily be altered to use LEFT JOINs should the need arrive.

I used to write all joins as you had and asked a similar question to
this one on this very newsgroup. I have now spent so long using the
JOIN syntax that I find it hard to figure out at a glance what the
other syntax is doing.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode