Is WORK faster than WHERE?

Question

Suppose I have two tables that are related (one has a foreign key):

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 to do something, but this is the best example I could come up with.

Now I want to get all the documents with more than 500 types. Two solutions that come to my mind:

 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 much better than the other? If so, why?

I know that my example is not perfect, and that some tuning might be required for queries, but I hope you understand the point;)!

EDIT: on request in the answers, this question was aimed at MSSQL, but I would be interested to know if it differs from other DB mechanisms (MySQL, etc.)

Answer 1

Theoretically, no, it should not be faster. The query optimizer should be able to create an identical execution plan. Nevertheless, some DB mechanisms can create better execution plans for one of them (this is unlikely to happen for such a simple query, but for quite complex ones). You should check both and see (on your database engine).

Answer 2

It is not possible to correctly answer this question without limiting the target database.

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

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

Really risky, as it’s easy to forget the join condition in the WHERE clause and end up with an unpleasant cross.

Answer 3

The performance of "JOIN" compared to "WHERE" ... it all depends on how well the database engine can optimize the query for you. It will take into account any indexes that may appear on the returned columns, and assume that the performance of the WHERE and JOIN clauses also comes down to the physical database file itself and its level of fragmentation, and even to the storage technology that you use to store the database files on.

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

The order of queries to the Microsoft Sql server

from an excellent series of books on Microsoft SQL Server, inside Microsoft SQL Server 2005: T-SQL Querying, which can be found here

(Step 8) SELECT (Step 9) DISTINCT (Step 11)
(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) From [CUBE | ROLLUP]
(Step 7) having_clause
(Step 10) ORDER BY order_by_list

Answer 4

In MySQL, at least both of them will be optimized for the same query.

Answer 5

This is the "standard" for using the INNER JOIN syntax, although it is practically equivalent. The main reason it should be used is clarity and portability, as it is consistent with the OUTER JOIN syntax.

Answer 6

When you use Sqlite: the where syntax is a bit larger, because Sqlite first translates the join syntax into the where syntax before executing the query.

Answer 7

Explicit joins are easier to maintain since the purpose of the request is much clearer. In addition, they are not susceptible to random cross-connects, so if you have a cross-connect in the request, then the maintainer knows that he should be there.

If you ever need to use external joins, you should know that the * = syntax is deprecated on SQL Server and will be removed soon. Also, it does not currently function as expected all the time and may not produce the correct results and therefore should NEVER be used. Mixing explicit external joins and combining sentences (implicit joining) makes the request much more difficult for the reader and his understanding.

Answer 8

If you're talking specifically about SQL Server, you definitely need to use the INNER JOIN syntax. Besides the fact that (a personal look at the warning!) Is easier to read and more clear in intent, with SQL Server 2005 there is no equivalent syntax for external connections. The syntax * = and = * is not supported by default in 2005 - you need to enable compatibility mode to support it. It will eventually be removed, possibly as soon as the next release (or maybe not!)

It means:

• If you need to change the request from an internal connection to an external connection, you need to either rewrite it (argh) or enable compatibility mode (yuk)
• Without the compatibility mode, you cannot be compatible with the way you implement different types of unions (internal and external), making services a nightmare (and, if they are combined in one request, intuitive).

Also note that, contrary to popular belief, the two are not equivalent. Some things are much more uncomfortable, and some are simply impossible. Kalen Delaney Inside SQL Server 2000, some examples are described; not sure what new versions do, because this join syntax is deprecated anyway.

Answer 9

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

Answer 10

I think that doesn't matter either. To make sure that you can check if the explanation plan for these two queries is identical. To look at the explanation plan in MySQL, you must put the keyword “explain” before the statement, for example:

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

I am sure that there is an equivalent in MSSQL.

By the way: It looks like this ratio is 1: 1, so I would just include the nbviews attribute directly in the Document table, so you can save the connection.