Alex Rivera | Logout

Is a JOIN faster than a WHERE?

Asked 2009-07-15T07:31:29.223
74

Suppose I have two tables that are linked (one has a foreign key to the other):

CREATE TABLE Document (
  Id INT PRIMARY KEY,
  Name VARCHAR 255
)

CREATE TABLE DocumentStats (
  Id INT PRIMARY KEY,
  DocumentId INT, -- this is a foreign key to table Document
  NbViews INT
)

I know, this is not the smartest way of doing things, but this is the best example I could come up with.

Now, I want to get all documents that have more than 500 views. The two solutions that come to my mind are:

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

or:

SELECT *
FROM Document
INNER JOIN DocumentStats ON Document.Id = DocumentStats.Id
WHERE DocumentStats.NbViews > 500

Are both queries equivalent, or is there one way that is far better than the other? If so, why?

EDIT: as requested in the answers, this question was aimed at SQL Server, but I would be interested in knowing if it is different for other database engines (MySQL, etc...).

sql performance join where-clause
Edit
Report

3 Answers

13

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.

answered 2009-07-15T07:38:04.263
4

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

answered 2009-07-15T07:34:33.897
1

In MSSQL, both queries are compiled to the same execution plan, so there's no difference. It's more about readability - I think the JOIN is easier to read, so I use that.

answered 2009-07-15T07:36:15.500

Your Answer