Is a JOIN faster than a WHERE?

There is no way to correctly answer this without limiting to a target database.

For MS-SQL both queries result in the same execution plans, but keep in mind:

SELECT *
FROM Document, DocumentStats
WHERE DocumentStats.Id = Document.Id
  AND DocumentStats.NbViews > 500

Is really risky since it is easy to forget the join condition in the WHERE clause and end up with a nasty cross join.


Performance of "JOIN" versus "WHERE"... everything hinges on how well the database engine is able to optimize the query for you. It will take into account any indexes you might have on the columns being returned and consider that performance of WHERE and JOIN clauses also come down to the physical database file itself and its fragmentation level and even the storage technology you use to store the database files on.

SQL server executes queries in the following order (this should give you an idea of the functions of the WHERE and JOIN clauses)

Microsoft SQL Server query process order

the following is taken from the excellent series of books about Microsoft SQL Server, Inside Microsoft SQL Server 2005: T-SQL Querying which can be found here

(Step 8) SELECT (Step 9) DISTINCT (Step 11) <top_specification> <select_list>
(Step 1) FROM left_table
(Step 3) join_type JOIN right_table
(Step 2) ON join_condition
(Step 4) WHERE where_condition
(Step 5) GROUP BY group_by_list
(Step 6) WITH [CUBE|ROLLUP]
(Step 7) HAVING having_clause
(Step 10) ORDER BY order_by_list


In MySQL at least, they will both be optimized to the same query.


Theoretically, no, it shouldn't be any faster. The query optimizer should be able to generate an identical execution plan. However, some database engines can produce better execution plans for one of them (not likely to happen for such a simple query but for complex enough ones). You should test both and see (on your database engine).