I have been brushing up on my SQL skills and have seen SQL statements like this:
SELECT name, pet
FROM people, pets
WHERE people.id = pet_owner.id
Usually I would write
FROM people INNER JOIN pets ON people.id = pet_owner.id
Both work, but I am wondering if the first way creates a cross product in memory and then applies the filter. I know for certain that the second statement can be optimised. I have no idea whether the first statement is. Would love to hear more about the difference between the two statements.
The first query is really a CROSS JOIN that uses shorthand, "comma" syntax instead of spelling out CROSS JOIN.
SELECT name, pet
is a shorthand way, using a comma, of writing
FROM people CROSS JOIN pets
How that compares to the equivalent INNER JOIN form using an ON is discussed in the "Cross Joins with WHERE Clauses" section of the JOIN Statements topic.
In a nutshell, use the INNER JOIN because that can optimize better than the process of creating a Cartesian product and then applying the WHERE condition, as you surmised.
To add to what Dimitri said: there is a semantic difference between ON and WHERE, they generate different results on outer joins. For the specific example that you posted, it does not matter what you write, the semantics are the same and the queries are simple enough to have us generate the exact same internal code. But once the queries start growing, and, god forbid, some joins stop being inner / cross and start becoming outer, putting the condition into WHERE will just return different results than if you put it into ON - you should use either ON or WHERE depending on when specifically you want the filtering to happen.