Sql what is the difference between where and having

Though it appears that both clauses do the same thing, they do it in different ways. In fact, their functions complement each other.

To help keep things straight I like to think of the order of execution of SQL statements from top to bottom. Returns 48, as the count. You can use them to filter out groups such as. But their true power lies in their ability to compare and filter based on aggregate function results. If you can put condition from the where clause in the having clause then why even worry about the WHERE? Can I just use this query?

Applies to: SQL Server all supported versions. In that case:. For example, imagine that you are joining the titles and publishers tables to create a query showing the average book price for a set of publishers. You want to see the average price for only a specific set of publishers - perhaps only the publishers in the state of California. You can establish the first condition by including a WHERE clause, which discards any publishers that are not in California, before calculating average prices.

The second condition requires a HAVING clause, because the condition is based on the results of grouping and summarizing the data. It does not act the same way on indexed columns. Resource from Here. From here. While working on a project, this was also my question.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 13 years ago. Active 5 months ago. Viewed k times. Improve this question. Usama Abdulrehman 1, 3 3 gold badges 9 9 silver badges 20 20 bronze badges. ColinYounger ColinYounger 6, 5 5 gold badges 29 29 silver badges 33 33 bronze badges. The line you quote isn't the key bit at all. Add a comment.

Active Oldest Votes. Improve this answer. This should be the accepted answer. The distinction between "having" and "where" makes this immediately clear. First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples.

Let's see. Before moving on to the main topic, we need to know the execution order of the query in SQL Server. The SQL Server order of execution defines the order in which the clauses of the query are evaluated. By default, it sorts the data in ascending order.