4 ms·

For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm st

For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.

ScottWhigham 13y ago
It's sad but this difference, although completely fundamental to SQL, is seen as "advanced" by most devs that I know. They would have no clue as to how to answer...
- bgilroy26 13y ago
  Order of execution is a great second stop on the road to SQL, after learning what all of the words mean.
- michaelt 13y ago
  Consider these two queries (untested so they might have typos): select order_number,sum(line_value) as order_value from order_line group by order_number having order_value > 100; And: select order_number,order_value from ( select order_number,sum(line_value) as order_value from order_line group by order_number ) where order_value > 100; I'd expect them to have the same explain plan and runtime characteristics. Isn't HAVING just syntactic sugar for the fact queries can be arbitrarily nested? Syntactic sugar isn't fundamental IMHO.
  - ScottWhigham 13y ago
    I don't know all of the syntax from all languages, but I do know that in Sybase and Microsoft SQL Server, the first would generate a syntax error. The HAVING and WHERE clauses cannot reference column aliases. You could rewrite it to be: ... having sum(line_value) > 100; Would the performance be the same? That's up to the individual DB. To say, "Yes, they would" implies that all DBMS vendors implement query plans the same way. I think that, fundamentally, they should perform the same way but again: it's not up to you or I; it's up to the people who wrote the query execution engine.
  - daigoba66 13y ago
    i believe you are correct
  - jrochkind1 13y ago
    I believe you are correct, except possibly the other way around -- nested queries are just syntactic sugar for joins, group by, and having. :) I'm actually not sure what I said is true (you can possibly do more with nested queries than you could do with just joins and group by/having?), but I DO think group by/having came before nested queries in rdbms implementations.
    - redidas 13y ago
      I can help out with this! (Finally a hacker news discussion in my line of expertise!) While you could think of nested subqueries as syntactic sugar, you might also want to think of them as a view you specify on the fly, or a "derived table" of information. Each RDBMS optimizes a bit differently, but depending on your system subqueries may have query plan implications as well. Sometimes they'll make your query faster, other times slower. It all depends on the RDBMS, table indexes, and the operations you are doing inside the nested query. Personally, I'd recommend using HAVING instead of using WHERE with a nested SUM. The query optimizer may create the same execution plan in the end, but the HAVING is a bit more explicit in what you are doing. For those familiar with SQL, HAVING indicates you are filtering your query on an aggregate value, where as a WHERE indicates you are filtering records out of consideration before they are aggregated (as someone else as pointed out in another comment).
archon 13y ago
This prompted me to go look it up, since I didn't know. HAVING is WHERE for aggregate functions (SUM, etc). Funny thing is, I've used HAVING a lot in the past, but couldn't have explained the difference succinctly without cheating and looking it up.
- willvarfar 13y ago
  Conceptually WHERE is filtering the table rows that get considered and HAVING is filtering the result rows - functions and all - that get returned. That's how my mental model of it all works.
- deleted 13y ago
  [deleted]
- zorbo 13y ago
  I deal with pretty complex queries daily, and I completely forgot what HAVING did exactly. I guess it's because I long ago realized that if I had to use HAVING in a query, I'm doing something wrong. That's almost always a Reporting/BusIntel tool's job, not mine.
  - 6cxs2hd6 13y ago
    That's a great point. Part of learning SQL is learning what parts of it _not_ to use. Things like HAVING and ORDER BY are useful for ad hoc queries at a SQL command-line, but are either not useful or a waste when there's another level in the system like a BI tool.
jfb 13y ago
I like asking for a 3VL truth table for the basic logical operators. This is a good way to get people talking about something that is conceptually fairly straightforward but that they might not have ever really puzzled out before.

ScottWhigham 13y ago

It's sad but this difference, although completely fundamental to SQL, is seen as "advanced" by most devs that I know. They would have no clue as to how to answer...

bgilroy26 13y ago
Order of execution is a great second stop on the road to SQL, after learning what all of the words mean.
michaelt 13y ago
Consider these two queries (untested so they might have typos): select order_number,sum(line_value) as order_value from order_line group by order_number having order_value > 100; And: select order_number,order_value from ( select order_number,sum(line_value) as order_value from order_line group by order_number ) where order_value > 100; I'd expect them to have the same explain plan and runtime characteristics. Isn't HAVING just syntactic sugar for the fact queries can be arbitrarily nested? Syntactic sugar isn't fundamental IMHO.
- ScottWhigham 13y ago
  I don't know all of the syntax from all languages, but I do know that in Sybase and Microsoft SQL Server, the first would generate a syntax error. The HAVING and WHERE clauses cannot reference column aliases. You could rewrite it to be: ... having sum(line_value) > 100; Would the performance be the same? That's up to the individual DB. To say, "Yes, they would" implies that all DBMS vendors implement query plans the same way. I think that, fundamentally, they should perform the same way but again: it's not up to you or I; it's up to the people who wrote the query execution engine.
- daigoba66 13y ago
  i believe you are correct
- jrochkind1 13y ago
  I believe you are correct, except possibly the other way around -- nested queries are just syntactic sugar for joins, group by, and having. :) I'm actually not sure what I said is true (you can possibly do more with nested queries than you could do with just joins and group by/having?), but I DO think group by/having came before nested queries in rdbms implementations.
  - redidas 13y ago
    I can help out with this! (Finally a hacker news discussion in my line of expertise!) While you could think of nested subqueries as syntactic sugar, you might also want to think of them as a view you specify on the fly, or a "derived table" of information. Each RDBMS optimizes a bit differently, but depending on your system subqueries may have query plan implications as well. Sometimes they'll make your query faster, other times slower. It all depends on the RDBMS, table indexes, and the operations you are doing inside the nested query. Personally, I'd recommend using HAVING instead of using WHERE with a nested SUM. The query optimizer may create the same execution plan in the end, but the HAVING is a bit more explicit in what you are doing. For those familiar with SQL, HAVING indicates you are filtering your query on an aggregate value, where as a WHERE indicates you are filtering records out of consideration before they are aggregated (as someone else as pointed out in another comment).

archon 13y ago

This prompted me to go look it up, since I didn't know. HAVING is WHERE for aggregate functions (SUM, etc). Funny thing is, I've used HAVING a lot in the past, but couldn't have explained the difference succinctly without cheating and looking it up.

willvarfar 13y ago
Conceptually WHERE is filtering the table rows that get considered and HAVING is filtering the result rows - functions and all - that get returned. That's how my mental model of it all works.
deleted 13y ago
[deleted]
zorbo 13y ago
I deal with pretty complex queries daily, and I completely forgot what HAVING did exactly. I guess it's because I long ago realized that if I had to use HAVING in a query, I'm doing something wrong. That's almost always a Reporting/BusIntel tool's job, not mine.
- 6cxs2hd6 13y ago
  That's a great point. Part of learning SQL is learning what parts of it _not_ to use. Things like HAVING and ORDER BY are useful for ad hoc queries at a SQL command-line, but are either not useful or a waste when there's another level in the system like a BI tool.

jfb 13y ago

I like asking for a 3VL truth table for the basic logical operators. This is a good way to get people talking about something that is conceptually fairly straightforward but that they might not have ever really puzzled out before.