sql server - JOIN vs Multiple FROM Tables

Question

Is there ever a case where a join will not return data that a FROM multiple tables with the same conditions returns?

e.g.

SELECT *
FROM TableNames as Names
INNER JOIN TableNumbers as Numbers on Names.ID = Numbers.ID

VS

SELECT *
FROM TableNames as Names, TableNumbers as Numbers
WHERE Names.ID = Numbers.ID

Answer 1

An INNER JOIN (as in your first example) will always return the same data as your a cartesian join with a WHERE filter that uses the same join criteria (your second example).

However, note that this is not true for OUTER JOINs, where NULL values are filtered out in a cartesian join with a WHERE filter as join criteria.