6

I have got the table MYTABLE with 2 columns: A and B

I have got the following pieces of the code:

SELECT MYTABLE.A FROM MYTABLE 
    HAVING SUM(MYTABLE.B) > 100
    GROUP BY MYTABLE.A

and

SELECT MYTABLE.A FROM MYTABLE 
    GROUP BY MYTABLE.A
    HAVING SUM(MYTABLE.B) > 100

Is it the same? Is it possible that these 2 codes will return diffrent sets of results?

Thank you in advance

user3006279
  • 115
  • 2
  • 8

5 Answers5

9

As documented, there is no difference. People are just used to seeing HAVING after GROUP BY.

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF20040

Specify GROUP BY and HAVING after the where_clause and hierarchical_query_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.

http://sqlfiddle.com/#!4/66e33/1

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
2

I originally wrote:

I am not sure your 1st query is valid. As far as I know, HAVING should always come after GROUP BY.

I was corrected by David Aldridge, the Oracle docs state that the order does not matter. Although I don't recommend using HAVING before GROUP for readability reasons (and to prevent confusion with a WHERE clause), it is technically correct. So that makes the answer to your question 'yes, it's the same'.

M-Peror
  • 712
  • 8
  • 16
  • http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF20040 – David Aldridge Dec 09 '13 at 15:08
  • Thanks for the correction, I didn't know that. Although lexically it seems like a silly order. :) Will edit my answer. – M-Peror Dec 10 '13 at 10:16
  • It does seem strange but mostly I think that's unfamiliarity. If you think about it, it would really make sense for the FROM clause to come first - that's how I usually start writing a complex SQL statement, anyway. – David Aldridge Dec 10 '13 at 10:40
  • Agreed, it does make a lot more sense to put the FROM first. Slightly off-topic: I heard that this was the actual reason MS made the FROM-clause come first when they designed LINQ (much inspired by SQL of course). They sort of had to, because otherwise it was nearly impossible for them to make things like code completion work in their IDE. – M-Peror Dec 10 '13 at 10:53
  • Also, changing things makes it seem like you're improving them -- why else change them? – David Aldridge Dec 10 '13 at 10:56
0

You can't have a HAVING before a GROUP BY, the HAVING is like the "WHERE" but for the GROUP BY condition.

Miguel G. Flores
  • 802
  • 7
  • 21
0

The clauses are evaluated in order. You can have a HAVING clause following immediately the FROM clause. In this case, the HAVING clause will apply to the entire rows of the result set. The select list may only contain, in this case, one/more/all of the aggregation functions contained in the HAVING clause.

So, your first query is not valid because of the above. A valid query would be 

SELECT SUM(MYTABLE.B) AS s FROM MYTABLE 
HAVING SUM(MYTABLE.B) > 100

The above query will return one or no row, depending on whether the condition SUM(MYTABLE.B) > 100 is verified or not.

Still, there is one more reason for which your first query is not valid. The GROUP BY clause may refer only to columns in the data set to which it applies. So going on with my valid query above, you can write the following valid query (though it will be useless and nonsense, as it is applied to either one or no rows):

SELECT SUM(s)
FROM 
(
SELECT SUM(MYTABLE.B) s
FROM MYTABLE 
HAVING SUM(MYTABLE.B) > 100
) q
GROUP BY s

So, just to answer: no, they're not the same. One of them is not even valid.

Andrei Nicusan
  • 4,555
  • 1
  • 23
  • 36
  • I don't know why you think this. It is documented that the two clauses can appear in either order, and the code works. http://sqlfiddle.com/#!4/66e33/1 – David Aldridge Dec 09 '13 at 15:05
  • Well, in fact it looks like it's vendor-dependent. So we can't talk here about standard SQL. My answer was tested (and holds true for) on SQL Server 2008 R2. Indeed, Oracle supports the syntax in your fiddle. – Andrei Nicusan Dec 09 '13 at 15:35
0

both WHERE and HAVING allow for the imposition of conditions in the query. Difference: We use WHERE for the records returned by select from the table, We use HAVING for groups returned by group by select query

Bartlomiej Lorenowicz
  • 1
  • 1