I usually do implied 'comma' joins like this:

select username,users.user_id,EVENT_ANNOUNCE_EMAIL from
users,user_settings WHERE users.user_id=user_settings.user_id AND
users.user_id=1;

which I believe is equivalent to this:

select username,users.user_id,EVENT_ANNOUNCE_EMAIL from users JOIN
user_settings ON users.user_id=user_settings.user_id WHERE
users.user_id=1;

EXPLAIN gives the same results in this case.

It seems to me it is not good practice to mix the join condition and
the where clause together like I have been, and I am wondering if a)
there is a functional difference, and b) if it is acceptable practice
and I am just retentive.

thanx

On 20 May, 18:12, christop...@dailycrossword.com wrote:
> I usually do implied 'comma' joins like this:
>
> select username,users.user_id,EVENT_ANNOUNCE_EMAIL from
> users,user_settings WHERE users.user_id=user_settings.user_id AND
> users.user_id=1;
>
> which I believe is equivalent to this:
>
> select username,users.user_id,EVENT_ANNOUNCE_EMAIL from users JOIN
> user_settings ON users.user_id=user_settings.user_id WHERE
> users.user_id=1;
>
> EXPLAIN gives the same results in this case.
>
> It seems to me it is not good practice to mix the join condition and
> the where clause together like I have been, and I am wondering if a)
> there is a functional difference, and b) if it is acceptable practice
> and I am just retentive.
>
> thanx

a) There is no functional difference, but it is neither as clear nor
as felxible as using the JOIN verb. Writing it as:
SELECT
`username`,
`users`.`user_id`,
`EVENT_ANNOUNCE_EMAIL`
FROM `users`
JOIN `user_settings` ON USING (`user_id`)
WHERE `users`.`user_id`=1;
tells one which fields do what. I tend to also qualify all the fields,
usually using aliases, so that it is also obviousl what field comes
from what table. Looking at your query, I have no idea which table
username or EVENT_ANNOUNCE_EMAIL is in. Note that I set teh query out
in such a way that I can immediately see what the query contains. I
don't have to go searching in a m*** of text.

b) Depends who is accepting it. I used to mix comma joins with others,
but now I favour clarity.