Re: Filter in the Join vs Filter in the Where

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

One thing to note is that there can be a difference in results when choosing between a filter in the WHERE and a filter in an OUTER JOIN. (Basically, you are either (WHERE) filtering the rows to be returned, or (OUTER JOIN) filtering the rows to be joined.) You want to be sure that Bob is aware of this.

For one discussion: http://www.sqlservercentral.com/Forums/Topic449462-338-1.aspx

RLF

"Mike H" <mike8675309@xxxxxxxxx> wrote in message news:57423f8d-e354-4b41-9f7f-b785e14e8103@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 28, 6:27 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> Bob's theory being that since the order of operations for sql
> databases is:
> 1. FROM clause
> 2. WHERE clause
> 3. GROUP BY clause
> 4. HAVING clause
> 5. SELECT clause
> 6. ORDER BY clause

> sticking any filtering conditions into the FROM clause is going to
> lead to higher performance through less data needing to be processed
> as it is filtered before the where needs to deal with it. I've found
> others preaching this as well:http://www.bennadel.com/blog/70-SQL-
> Query-Order-of-Operations.htm

This logical order of query processing is not necessarily the same as the
physical order. Like Tom said, it is the execution plan that will tell the
real story. Semantically identical queries often yield the same execution
plan since the optimizer's job is to plan the declared query as efficiently
as possible. Even if you do get different plans do to happenstance (this
can tends to happen for more complex queries and the optimizer isn't
perfect), the plan could very well change between service packs or SQL
Server versions as the optimizer improves.


Thank you for the feedback. I've done some testing with Query analyzer
and have found that for the most part, execution plans end up being
the same regardless, even on the more complex queries. It is Bob's
insistence that the order of operations dictate that you are
essentially a fool for using the where clause that prompted my
question. He seems so fixed in his position that I thought that maybe
I'm missing something so basic, folks don't even talk about it.

.

Relevant Pages

  • Re: Report Form Design
    ... I place as much filtering in the where clause of the DoCmd.OpenReport method ... Dim strWhere as String ... My more sophisticated applications have report system tables for "reports", ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Filter in the Join vs Filter in the Where
    ... And since your query and Bob's query both return the same result, it is not suprising that the optimizer generates the same plan. ... requires filtering to be applied to various columns from these various ... I will more often than not use the Join On to join the common ... and place any filters in the Where clause. ...
    (microsoft.public.sqlserver.programming)
  • Filter in the Join vs Filter in the Where
    ... this is with SQL Server 2005 data. ... requires filtering to be applied to various columns from these various ... and place any filters in the Where clause. ... might write a query like this: ...
    (microsoft.public.sqlserver.programming)
  • RE: Using an option group to display a subform
    ... you can add the WHERE clause to your query for filtering the records ... Did you encounter any problem when ...
    (microsoft.public.access.forms)
  • Re: case statement in where
    ... :> quez wrote: ... I would run the select with no where clause (maybe restrict it based on ... clause is filtering out that row. ... query precisely so I can easy check the ...
    (comp.databases.oracle.server)