If a WHERE clause and a HAVING clause are both used in a query, which filter will be physically applied first?

Where clause will be applied first to the query..... Once it filters the rows by using where clause, then only it will apply aggregate the rows and will apply filter on that aggregate values in having clause.

The similarities between these 2 clauses is both will be used to filter the rows.

The difference between these 2 clauses is Having clause is designed to filter the rows based on aggregates, since in where clause, we can't use the aggregates.

Apart from that 2 points i learned from your blogpost previously is,

1. Having Clause internally forces the group by clause, even if it is not present...
2. An aggregate query always returns one record(even on empty set);

Having Vs Where, One of my favorite Question.

Similarities

The HAVING clause sets conditions on the GROUP BY clause similar to the way that WHERE interacts with SELECT.

Just as WHERE limits the number of rows displayed by SELECT, HAVING limits the number of groups displayed by GROUP BY.

HAVING syntax is similar to the WHERE syntax, except that HAVING can contain aggregate functions.

Differences

The WHERE search condition is applied before grouping occurs, and the HAVING search condition is applied after.

The WHERE clause filters the rows that result from the operations specified in the FROM and JOIN clauses. The HAVING clause filters rows from the grouped result.

If we have having and where clauses in the same Query where condition is physically applied first and then the filter is applied to the group.

It is better to use where to filter and then use having clause for Group By . To understand and prove that

Consider the below example

Use Pubs

SELECT pub_id, SUM(sales) -- Quicker 
  FROM titles 
  WHERE pub_id IN (‘P02’, ‘P05’) 
  GROUP BY pub_id 
  HAVING SUM(sales) > 1000;  

SELECT pub_id, SUM(sales) --Slower 
  FROM titles 
  GROUP BY pub_id 
  HAVING SUM(sales) > 1000 AND pub_id IN (‘P02’, ‘P05’);

Obviously the first one is faster due to where condition getting filtered and then applying group by.

First we need to understand what exactly each clause does internally, to use them effectively. Thanks

WHERE will be applied earlier than HAVING.

The reason for the same is logical query processing. Following is the order of the logical query processing.

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY

If you do not use WHERE and use the conditions in HAVING SQL Server Engine is smart enough to re-write the execution plan internally and move the HAVING clause to WHERE. The reason for the same is that SQL Server Engine applies HAVING clause on GROUP BY clause only. If there is no GROUP BY Clause there is no point of using HAVING clause, so internally it will move HAVING clause to WHERE clause. If you are using aggregated clause in HAVING, it will force a group by and will filter the grouped results.

It is always good idea to filter out not necessary clause early in query before HAVING is applied, this is not only good for performance but some time necessary for accurate resultset as well.

Similarity:

Both filters out the rows.

Difference:

WHERE works at independent row level.

HAVING works at group level.

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

If both, WHERE and HAVING are used in a query, the WHERE clause will be applied first to reduce the number of result sets. The Having clause will be applied after the GROUP BY clause is used and will only include the reduced number of rows (reduced by the WHERE clause). Therefore, a criteria that will include rows in a HAVING clause that are eliminated by the WHERE clause will not be part of the final result set.

Similarities:

Both, WHERE and HAVING are used to reduce the number of returned rows by applying a filter.

Differences:

1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions. (http://www.devx.com/DevX/Tip/21295)

If the GROUP BY clause is not present in the query, the query processor will internally use a GROUP BY to aggregate the data. A WHERE clause filter usually is faster than a HAVING clause filter since it is applied earlier during data processing (see Itzik Ben-Gans question on October 2nd).

If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause. (http://www.codeproject.com/.../WhereVsHavingClause.aspx)

The WHERE and HAVING clauses have more in common than most realize. For example consider the following select statements:

select 1 where 1=1;
select 1 having 1=1;
select 1 where 1=1 having 1=1;

All three statements are valid and will execute successfully. Both WHERE and HAVING clauses specify a search condition, or filter, by which the result set is potentially reduced. The fact is, the two clauses serve the same purpose and behave identically when there is no GROUP BY clause.

However, when we introduce a GROUP BY clause, the game changes. Suddenly, we have a new rule by which we must play with regard to HAVING: only aggregate evaluations are valid (It should be noted that the introduction of the FROM clause forces the GROUP BY to be present when using HAVING; as such, the previous examples have limited application in practice). This means that we are now asking questions at a different level and differently logical processing step than WHERE. No longer can we simply think about filtering the rows out based on values; rather, we filter on things like the summation, average, maximum, etc. that occur after the data is grouped. This means we can answer questions like, "which students have an average total score above 85%," when the data is stored, for example, with scores for 20 assignments per student. Furthermore, this means that WHERE must be, and is, logically and physically applied before HAVING.

One other incidental difference with HAVING is that the text, image, and ntext data types are not valid for evaluation. This makes sense as aggregate functions and these data types don't play well together. Furthermore, the HAVING clause is only valid in a SELECT statement whereas WHERE. Again, this makes sense as aggregation of the other DML statements (specifically UPDATE & DELETE . . . INSERT become debatable) is not logically sound.

If a where and having clause both are used in a query for example i have taken a query from northwind database

USE Northwind;

SELECT C.CustomerID, COUNT(O.OrderID) AS NumOrders
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.CustomerID = O.CustomerID
WHERE C.City = 'London'
GROUP BY C.CustomerID
HAVING COUNT(O.OrderID) > 5
ORDER BY NumOrders;

The engine starts execution by performing the Index Seek .it will select the first row with the customer residing in London. and after that it will aggregate the rows and apply filter on the aggregate values using having clasue

The similarities between these 2 clauses is both will be used to filter the rows.

The difference between where and having clause is:

1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

Well now, lets see...

Both will filter information prior to delivering the result set.

The Where will be used before the Having (if they are both being used)

Both can be used with columns identified in a Group By

Having can directly refer to aggregates but Where cannot

Having on a group by item may well be used as a row predicate - as per a where

Having on an aggregate will filter after detail rows have been selected (ie has to be able to form the aggregate before you can use it)

Having can only refer to those columns that are used in a group by or aggregate function ie as part of the selected columns

Where refers to columns in the "from" datasource even if not selected

ummm, anything else ? probably tons...

you we get this answering if u read "Inside Microsoft® SQL Server™ 2005 T-SQL Querying By Itzik Ben-Gan."

ordering of Query processing

1. FROM:
2. ON
3. OUTER (join)
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Having is filter filter to result return by Group By.The HAVING filter is the first and only filter that applies to the grouped data.Unfortunately, explicit clauses such as GROUP BY or HAVING aren't the only factors that might force a SELECT list to become grouped. According to SQL's rules, just the presence of an aggregate function that binds to a particular list makes that SELECT list grouped, even if it has no GROUP BY or HAVING clauses.

Where Clause is worked on indiviual rows where as having Clause worked on aggregate.Similarties between both is both filter out results.

“Where” clause will be applied first as the query has to get the correct record set first before doing aggregates. The having clause is applied on aggregate and therefore, will be applied after the where clause.

The Where clause will work first. The Where clause will select the subset of data that the Having clause will work against. The Having clause generally is used to restrict, conditionally, the output of an aggregate function that is used in a Select call list. The Having clause is used because you cannot restrict the output of a aggregate function used in a select call list in a where clause.

You cannot do this:

Select aletter,bletter,sum(cletter) as sumcletter
From lettertable
Where sum(cletter) > 6

But you can do this:

Select aletter,bletter,sum(cletter) as sumcletter
From lettertable
Group by aletter,bletter
having sum(cletter) > 6