SQL join question

Queries B & C are NOT the same.  Instead, A and C are equivalents.  Query B will give the worst performance because the LEFT function on the only column in the WHERE clause is going to preclude the use of any indexes.  It is probably that there will be an index on the ID columns, though, so both A and C are going to take advantage of that index and then check the LEFT(AuthorName, 1) constraint..

As to whether the first "half" of Query A will perform before anything else or before the constraint being applied, that all depends upon the query engine and, absent an indication as to which engine is involved (e.g. Oracle, mySQL, Teradata), one cannot make a definitive statement of regarding that aspect.  What one can say, though, is that it is probably that Query A will result in an index seek rather than a table (or, ven, an index) scan, which is what the Query B will cause.  

Also, one can say that Query A conforms to the newer "rules" for writing "well formed and well formatted" queries. ;-)

I read differently.  As far as I can tell, all three queries will perform the same.  The usage of SQL-99 compliant syntax (using JOIN syntax) is for readability purposes and to assist the query optimizer.  However, it is obvious that there are only two tables, so SQL Server [2008] will work out an equivalent plan.  The proof is in the pudding, paste the whole lot (all 3) in query analyzer and hit Ctrl-L and check the plans picture for picture.

I tend to agree with RiteshShah here.

Query A is way better and for the reason, he has explained in his very first comment of ID:24836935.

All the query will give you same result and may look same in execution plan because query analizer is very smart and will convert other two (B and C) to type of A and will display that in execution plan.

Kind Regards,
Pinal Dave

for more clarification, have a look at Pinal's blog at:

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

which will show you FROM clause will be executed first so if you are filtering data between FROM and WHERE, less data will be passed to WHERE and other clauses which will ultimately resulted in good performance.

Hi Guys,

Mind if I put my 2 cents in ?

Query A is the best on the infromation provided so far...

Query C being an equi-join will most likely translate into query A. Depends a bit on indexes and statistics. Regardless it is "yucky" syntax, and really best to declare your joins more formally to give the query optimizer every opportunity to resolve the data source requirements by more clearly indentifying those data sources in a formalised "FROM" statement.

Query B is applying a filter after the join so has potentially more work to do.

In reality, it is pretty hard to second guess the query optimizer, and it is getting very clever. As pinal Dave said above, they are all likely to use the same query plan in the end...

From Books on line we find the basic steps that SQL Server uses to process a single SELECT statement include the following:

1) The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.

2) A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.

3) The query optimizer analyzes different ways the source tables can be accessed. It then selects the series of steps that returns the results fastest while using fewer resources. The query tree is updated to record this exact series of steps. The final, optimized version of the query tree is called the execution plan.

4) The relational engine starts executing the execution plan. As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.

5) The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

And reading further :

The process of selecting one execution plan from potentially many possible plans is referred to as optimization. The query optimizer is one of the most important components of a SQL database system. While some overhead is used by the query optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the query optimizer picks an efficient execution plan.

The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

The SQL Server query optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. The SQL Server optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

The query optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. Distribution statistics are kept for columns and indexes. They indicate the selectivity of the values in a particular index or column.


So, you see, it is actually a bit difficult to decide. To the point that SQL may well decide that a table scan is faster because there is relatively little data to select from. Conversely,  if the index statistics and column statistics are not current, the query optimizer may falsely choose the wrong plan.

So, the only way to be sure of the best running queries, is to use very good syntax and structure, aim to minimise row selection, use columns that have indexes as predicates in joins and filters, and keep your statistics up to date...

The, finally, run the execution plan and see what it says - or even analyse the query in the database tuning advisor. But apply what you "actually" know about the data in terms of real business use when looking at the various recommendations.

But then, ritesh and other experts have pretty much all said Query "A" - slight differences of opinion about Query "C" and most agree that Query "B" is undesirable.

Just one suggestion, instead of doing LEFT(Author,1) = 'A' why not do Author like 'A%'