CODEWITHSUNDEEP
sql-servert-sql
deadflowers
deadflowers

Reputation: 63

What is an example of a query that needs both WHERE and HAVING clauses?

I'm not asking what the difference between the two clauses are. I understand that the WHERE clause searches within rows in a table and that the HAVING clause searches within the items that are grouped in the select statement.

Example:

SELECT x, Count(*) FROM table WHERE x IS NOT NULL GROUP BY x HAVING Count(*) > 1 ORDER BY x

Returns the same as

SELECT x, Count(*) FROM table GROUP BY x HAVING x <> NULL AND Count(*) > 1 ORDER BY x

Can the HAVING clause return the same results without the WHERE clause every time such as in this example, or is there a time that a WHERE clause is going to be necessary? Or is this just a matter of efficiency?

Upvotes: 0

Views: 60

Answers (2)

David Hammond
David Hammond

Reputation: 3306

You can use the where clause to filter by any column in the table before the grouping is done. E.g. 

SELECT  x, COUNT()
FROM    table
WHERE   y IS NOT NULL
GROUP BY x HAVING COUNT() > 1
ORDER BY x

So the answer is absolutely yes, you need the where clause sometimes. And as Rahul points out, even in your examples I would prefer the one with the where clause.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

In your first query, WHERE x IS NOT NULL filtering out all rows where column x have null values and then performing the grouping.

Whereas, in second query you are first performing the grouping and then applying the same filter.

I would prefer the first one, filter out the rows not needed and then perform the grouping.

Upvotes: 1

Related Questions