Are these 2 sql queries equivalent in all respects (e.g. estimated and actual execution plan)?

The optimizer could be affected by whether there is a usable index on any of a. Cat, b. Cat or c.Cat - and whether that index includes the relevant id or fid column too.

It might be smart enough to push the simple WHERE clause predicate down to the table level operations. It might also be affected by statistics for the tables.(If the value of the parameter happens to appear just once in C, it may be much more efficient to start processing C than to start processing A; or it may be more efficient to start with B; or it may still be more efficient to start with A. And it may or may not matter that the optimizer doesn't know the value of the parameter until the statement is executed, rather than being able to see it when it is prepared.

).

The optimizer could be affected by whether there is a usable index on any of a. Cat, b. Cat or c.Cat - and whether that index includes the relevant id or fid column too.

It might be smart enough to push the simple WHERE clause predicate down to the table level operations. It might also be affected by statistics for the tables.(If the value of the parameter happens to appear just once in C, it may be much more efficient to start processing C than to start processing A; or it may be more efficient to start with B; or it may still be more efficient to start with A. And it may or may not matter that the optimizer doesn't know the value of the parameter until the statement is executed, rather than being able to see it when it is prepared.

) So, as already intimated in a comment, there is no definitive statement that can be made without knowing a lot more about the system you are using - including the schemas and so on. The good news is that the result contents should be the same - there can be no guarantee about the execution plans. I note that most SQL systems would require a single keyword ON for each join: select * from A as a join B as be on b.

Id = a. Id AND b.Cat = @cat join C as c on c. Fid = b.

Fid AND c.Cat = @cat where a. Cat = @cat Xerion asked: Given that the execution plans may differ, which fashion of query is "better". Where better is defined as: More likely to be optimized to give better plan (or less likely to confuse SQL optimizer).

More consistent with SQL convention. It depends a bit on the DBMS and its optimizer; there is no substitute for empiricism in this area, but remember that what is empirically determined at one point in time may be erroneous at another point in time because: The data sets have changed size The data sets have different statistical distributions now The optimizer may have changed There may be different indexes available There are many ways to do the joins, and it does (still) depend on the indexes available. I would probably write: SELECT * FROM A JOIN B ON b.Id = a.

Id AND b. Cat = a.Cat JOIN C ON c. Fid = b.

Fid AND c.Cat = b. Cat WHERE a. Cat = @cat AND b.Cat = @cat AND c.

Cat = @cat The logic here is that there are likely to be indexes on A(id, cat) and B(id, cat), and on on B(fid, cat) and C(fid, cat), and the optimizer can therefore fully exploit those indexes. The WHERE clause contains two redundant terms, but lets the optimizer know what is required and tells it explicitly what it may not otherwise deduce for itself. If you are confident in the quality of the optimizer (and have checked the query plans it produces), then you might eliminate two of the three conditions in the WHERE clause.

If you place the parameter in the ON clauses, the optimizer might not make as good use of indexes as it can without that - again, you must experiment to find out how your optimizer behaves. Finally, as already intimated, the set of indexes on the tables is crucial, and ensuring that any statistics the DBMS needs are up to date is often important. Also remember that if the tables are initially small, the optimizer may choose a different query plan from when the tables grow big - so do your testing at sensible levels.

Unless you production tables will only contain a few tens of rows, don't do your performance testing for the query on tables that have only a few tens of rows.

Fair enough. Given that the execution plan may differ, which fashion of query is "better". Where better is defined as: 1) more likely to be optimized to give better plan (or less likely to confuse SQL optimizer).2) more consistent with SQL convention.

Sorry if this question seems trivial, I'm fairly new to SQL. – Xerion Jan 15 '10 at 19:46.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.