I have written a SQL query against the Stack Exchange Data Explorer that identifies answered questions with a positive score for which all the answers have a negative score (this was an answer to this Meta Stack Exchange post, though the code in that post is different from what I've posted below).

It's a fairly straightforward query; you can see it and run it on the Data Explorer (where you can also examine the schemata of the tables), and I have reproduced it below for your convenience.

  q.Id AS [Post Link], -- this alias makes the Data Explorer do magic link rendering
  MAX(q.Score) AS [Score],
  COUNT(a.Id) AS [AnswerCount],
  AVG(CAST(a.Score AS float)) AS [AvgAnswerScore]
  Posts AS q
  -- INNER JOIN => At least one answer
    ON q.Id = a.ParentId
  -- Question has a positive score
  q.Score >= 1
  -- Zero answers with a non-negative score
  SUM((CASE WHEN a.Score >= 0 THEN 1 ELSE 0 END)) = 0
  [Score] DESC,
  [AnswerCount] DESC

While I of course would love to hear anything you have to say about this code, I am particularly interested in the following:

  • Stylistic tips - coming from the perspective of a PEP8-adhering Python guy, the huge inhomogeneity in SQL coding styles makes me queasy.
  • MAX(q.Score) looks really stupid and is semantically wrong, since there's obviously only one q.Score for each q.Id since they come from the same table, and q.Id is the primary key of that table. But putting q.Score into the GROUP BY also seems weird to me, since that's not semantically what I'm doing either. Is there a better way to approach this?
  • How do the performance characteristics of the HAVING clause here differ from instead using a WHERE clause of the form below?

    q.Score >= 1
      SELECT 1
      FROM Posts AS a2
      WHERE a2.ParentId = q.Id AND a2.Score >= 0

    My sense is that HAVING SUM(...) = 0 is probably better, since, uh... there doesn't need to be a new subquery for each row of the outer query? But I don't know if that is true or even makes sense.

  • \$\begingroup\$ This isn't a review of the query so much, but your problem statement should be more clear. Should this return questions with answers that have no votes at all? Should this return questions with no answers? Should this return questions with answers with a positive score but having at least one downvote? \$\endgroup\$
    – nhgrif
    Commented Feb 14, 2015 at 5:19

1 Answer 1


Answering your questions in reverse order…

Your HAVING clause is clearer than than an anti-join condition in the WHERE clause. It is also more efficient — tick the "Include execution plan" checkbox to see how much more complicated the anti-join is. You could do better, though: MAX(a.Score) < 0 also means that all answers have a net negative score.

Given the choice between selecting MAX(q.Score) and grouping by q.Score, I'd prefer the latter. Both are annoying, but the SQL standard requires that you do one or the other, so that all columns are either mentioned in GROUP BY or are aggregated.

I can provide some style tips, but keep in mind that these are my personal recommendations. I don't believe that there is a standard SQL style guide — and certainly nothing as formalized as PEP 8.

  • Fill in the description to your Data Explorer query. Otherwise, there is a huge gap between the information contained in the title and the SQL. Detail exactly what the query does. Mention your motivation for writing the query. State what the sort order is.
  • Indent more. The FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses are all subsidiary to the SELECT, and therefore should be at the next indentation level.
  • Finishing the query with a semicolon is a good habit.
  • Move comments to the right to be less obtrusive.
  • I like to put commas before the selected columns. That convention makes it easier to add and remove columns.
  • Prefer comparing with 0 instead of comparing with any other constant.
SELECT q.Id AS [Post Link] -- Alias makes Data Explorer do magic link rendering
     , q.Score AS [Score]
     , COUNT(a.Id) AS [AnswerCount]
     , AVG(CAST(a.Score AS float)) AS [AvgAnswerScore]
    FROM Posts AS q
        INNER JOIN Posts AS a
            ON q.Id = a.ParentId
        q.Score > 0        -- Question has a positive score
        q.Id, q.Score
        MAX(a.Score) < 0   -- Best answer has a negative score
        [Score] DESC,
        [AnswerCount] DESC;

I'd like to point out: good job remembering to CAST(a.Score AS float) before taking the average. SQL Server is tricky, in that AVG() of ints is an int.

  • \$\begingroup\$ Thank you for the comments! Re: "prefer comparing with 0" - is there a SQL-specific reason for this, or is it just the usual maxim of "don't use magic numbers in your code"? \$\endgroup\$
    – senshin
    Commented Feb 20, 2015 at 18:18
  • \$\begingroup\$ CPU instruction sets have special instructions for comparisons with zero. (In practical terms, you aren't going to notice much of a performance difference, but it's a general aesthetic principle that 0 is a nice number to compare against.) \$\endgroup\$ Commented Feb 20, 2015 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.