SQL Performance – Is a JOIN Faster Than a WHERE Clause?

joinperformancesqlwhere-clause

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…).

Best Answer

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).

Related Solutions

SQL – Condition Within JOIN or WHERE for Performance

The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process.

I recommend you write the queries in the most readable way possible.

Sometimes this includes making the INNER JOIN relatively "incomplete" and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.

For example, instead of:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Write:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

But it depends, of course.

SQL Performance – INNER JOIN vs WHERE Clause Conditions

The reason that you're seeing a difference is due to the execution plan that the planner is putting together, this is obviously different depending on the query (arguably, it should be optimising the 2 queries to be the same and this may be a bug). This means that the planner thinks it has to work in a particular way to get to the result in each statement.

When you do it within the JOIN, the planner will probably have to select from the table, filter by the "True" part, then join the result sets. I would imagine this is a large table, and therefore a lot of data to look through, and it can't use the indexes as efficiently.

I suspect that if you do it in a WHERE clause, the planner is choosing a route that is more efficient (ie. either index based, or pre filtered dataset).

You could probably make the join work as fast (if not faster) by adding an index on the two columns (not sure if included columns and multiple column indexes are supported on Postgres yet).

In short, the planner is the problem it is choosing 2 different routes to get to the result sets, and one of those is not as efficient as the other. It's impossible for us to know what the reasons are without the full table information and the EXPLAIN ANALYZE information.

If you want specifics on why your specific query is doing this, you'll need to provide more information. However the reason is the planner choosing different routes.

Additional Reading Material:

http://www.postgresql.org/docs/current/static/explicit-joins.html

Just skimmed, seems that the postgres planner doesn't re-order joins to optimise it. try changing the order of the joins in your statement to see if you then get the same performance... just a thought.

Best Answer

Related Solutions

SQL – Condition Within JOIN or WHERE for Performance

SQL Performance – INNER JOIN vs WHERE Clause Conditions

Related Question