T-SQL Tuesday – HAVING Puzzle answer

Earlier today you may have seen a blog post of mine about a puzzle involving HAVING. You should read that post before this one. It was part of Adam Machanic’s T-SQL Tuesday meme.

The question was about the query:

SELECT ‘No Rows’
WHERE 1=2
HAVING 1=1;

And here’s the explanation.

Start by making yourself a "dual table", like what you’d use in Oracle, and use this instead of having no FROM clause. Put a row in it.

CREATE TABLE dual (dummy bit);
INSERT dual VALUES (1);

–Now count the rows in it
SELECT COUNT(*)
FROM dual;

–Now count how many rows don’t match 1=2 (of course, the answer is zero)
SELECT COUNT(*)
FROM dual
WHERE 1=2;

–Naturally we’d get nothing back if we weren’t grouping
SELECT ‘Something’
FROM dual
WHERE 1=2;

–But HAVING forces the grouping functionality as well (like using COUNT(*))
SELECT ‘Something’
FROM dual
WHERE 1=2
HAVING 1=1;

–So in this query, we couldn’t put any of our real columns in, only aggregate functions and constants
SELECT *
–Errors
FROM dual
WHERE 1=2
HAVING 1=1;

–And leaving out the FROM clause implies that we’re asking all this of a secret internal table with a single row. All these queries work just the same without the FROM clause at all.

–Count the rows in our pretend table (one)
SELECT COUNT(*)

–Now count how many rows don’t match 1=2 (zero)
SELECT COUNT(*)
WHERE 1=2;

–Naturally we’d get nothing back if we weren’t grouping
SELECT ‘Something’
WHERE 1=2;

–But HAVING forces the grouping functionality as well
SELECT ‘Something’
WHERE 1=2
HAVING 1=1;

So the answer to the question posed is that you get a single row, containing the text provided. The fact that I used the text ‘No Rows’ was just a bit of fun.

Now, to remove the trivia a little…

When would you ever use HAVING without GROUP BY in a practical situation?

How about this:

Using sp_MSforeachdb, find the number of objects in non-system databases. It’s an undocumented system stored procedure which runs a query on each database, replacing a question mark in the query with the name of the database. It can be quite handy, just don’t look at how it’s implemented.

EXEC sp_MSforeachdb ‘SELECT ”?”, COUNT(*) FROM ?.sys.objects WHERE ”?” NOT IN (”master”,”tempdb”,”model”,”msdb”);’;

But this won’t do it. It will still return the entries for the system databases, but with zeroes (because none of the objects satisfied the WHERE clause). Replace WHERE with HAVING and it’s just fine — the rows get eliminated from the resultset.

EXEC sp_MSforeachdb ‘SELECT ”?”, COUNT(*) FROM ?.sys.objects HAVING ”?” NOT IN (”master”,”tempdb”,”model”,”msdb”);’;

Honestly, HAVING doesn’t require a GROUP BY clause. It doesn’t require anything. It filters based on groups, and if there are no groups yet, it makes some — like how using an aggregate will count the rows in an empty set and return one row representing that group.

It’s generally taught as "HAVING is for filtering based on aggregates", and that’s true, but only half the story. And I find that if I’m teaching people to write better queries, I want them to have a thorough understanding of what each construct is really doing.