How to combine left join and where join (old school comma join)?

Question

This is no problem

select user.`username` , id_user_gender.text gender
from user
left join id_user_gender
on user.idGender = id_user_gender.id
where 1

This is also no problem

select user.`username` , user.idType
from user,id_user_type
where user.idType=id_user_type.id

But this is error: #1054 - Unknown column 'user.idGender' in 'on clause'

select user.`username` , id_user_gender.text gender, user.idType
from user,id_user_type
left join id_user_gender
on user.idGender = id_user_gender.id
where user.idType=id_user_type.id

How to use left join and where join in same query?

I also tried this, but not work

select uu.`username` , id_user_gender.text gender, uu.idType
from user as uu,id_user_type
left join id_user_gender
on uu.idGender = id_user_gender.id
where uu.idType=id_user_type.id

stop using the old ansi comma join syntax as it is harder to read and maintain.. — Raymond Nijland, Sep 26 '19 at 11:13
Don't mix implicit, comma separated joins with explicit `JOIN`'s. Use the modern, explicit `JOIN` syntax everywhere. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Sep 26 '19 at 11:14
And you get that error because explicit JOIN chains are evaluated before the implicit joins. The ON condition has no access to uu columns. — jarlh, Sep 26 '19 at 11:16
It's not so much 'old school' as bad practice - (although I accept it may have been the only mechanism for joining tables once-upon-a-time). Yes, I know I am guilty of using comma joins, but what's life without a smidge of blatant hypocrisy? — Strawberry, Sep 26 '19 at 11:18
' If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.' - https://dev.mysql.com/doc/refman/8.0/en/join.html — P.Salmon, Sep 26 '19 at 11:35
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. PS Read the manual for a tool you are using. — philipxy, Sep 26 '19 at 18:56

score 2 · Accepted Answer · answered Sep 26 '19 at 11:15

You don't. You put all JOIN logic in the FROM clause, where JOINs belong:

select u.`username`, iug.text gender, u.idType
from user u join
     id_user_type iut
     on u.idType = iut.id left join
     id_user_gender iug
     on u.idGender = iug.id;

Simple rule: Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

How to combine left join and where join (old school comma join)?

1 Answers1