select order_number,sum(line_value) as order_value from order_line
group by order_number
having order_value > 100;
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'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.
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).
...
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.
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.