"is there something wrong with joins that don't use the join keyword in sql or mysql?" Code Answer

filtering joins solely using where can be extremely inefficient in some common scenarios. for example:

select * from people p, companies c 
    where p.companyid = c.id and p.firstname = 'daniel'

most databases will execute this query quite literally, first taking the cartesian product of the people and companies tables and then filtering by those which have matching companyid and id fields. while the fully-unconstrained product does not exist anywhere but in memory and then only for a moment, its calculation does take some time.

a better approach is to group the constraints with the joins where relevant. this is not only subjectively easier to read but also far more efficient. thusly:

select * from people p join companies c on p.companyid = c.id
    where p.firstname = 'daniel'

it's a little longer, but the database is able to look at the on clause and use it to compute the fully-constrained join directly, rather than starting with everything and then limiting down. this is faster to compute (especially with large data sets and/or many-table joins) and requires less memory.

i change every query i see which uses the "comma join" syntax. in my opinion, the only purpose for its existence is conciseness. considering the performance impact, i don't think this is a compelling reason.

By yahh on June 1 2022