HAVING 0 = COUNT

Question

HAVING 0 = COUNT(1)) AS a

What does this mean ?

SELECT outr.*
  FROM Orders outr
 WHERE
EXISTS (   SELECT *
                    FROM (   SELECT ol.Orders_ID
                               FROM Orderlines AS ol
                              WHERE Status NOT IN ( 5, 6 )
                              GROUP BY ol.Orders_ID
                             HAVING 0 = COUNT(1)) AS a
                   WHERE outr.Orders_ID = a.Orders_ID)

I believe your query will return nothing because it is filtered to only pull groups that have a count of 0. — SQLChao, May 21 '18 at 16:23
Its part of a bigger query, for simplicity and security I can only show this much. I was just intrigued to know what the HAVING 0 = count (1) serves. This was an input from a colleague. — Shoaib Maroof, May 21 '18 at 16:25
It could also be read `HAVING COUNT(1) = 0` if it makes more sense — SQLChao, May 21 '18 at 16:27
That makes more sense. I still don't see the purpose it serves. Is it looking for all orders that don't have a matching orderline ? — Shoaib Maroof, May 21 '18 at 16:29
The `having` would filter out groups similar to how `where` would filter out rows. The count(1) = 0 part will always be false. It's likely someone essentially disabled this script by changing/adding this having clause to prevent anything from returning. — Owen Johnson, May 21 '18 at 17:29
Thanks. I have removed it from the code. It was of no use to the query as a whole, in this case. — Shoaib Maroof, May 22 '18 at 15:56

score 1 · Accepted Answer · answered May 21 '18 at 16:24

The AS a is an alias for the derived table. Simplified:

SELECT *
FROM (SELECT Orders_ID
      FROM Orderlines
      GROUP BY Order_ID
      HAVING 0 = COUNT(1)
     ) AS a

The HAVING 0 = COUNT(1) means that the query will only return Orders_ID that have a count of 0. Note that COUNT(1) is the same as COUNT(*).

It doesn't make any sense because an Orders_ID will never exist AND have a count of 0.

So, the derived table will never return anything, and since it's in an EXISTS clause to your outer query, neither will the outer query.

HAVING 0 = COUNT

1 Answers1