1

If I used 'Having' as 'Where' (i.e. without group by clause), Can you please tell me which one is faster ‘Where’ OR ‘Having (as where)’? Which one we have to prefer?

Scenario - I am using this condition in finding locations in certain distance using lat and longitude.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Abhi
  • 1,963
  • 7
  • 27
  • 33
  • 1
    Actually you can do your own benchmark. – Mark Jul 04 '13 at 09:43
  • 1
    What RDBMS are you actually using? `HAVING` and `WHERE` aren't generally interchangeable as `HAVING` applies to groups and `WHERE` on rows. – Martin Smith Jul 04 '13 at 09:45
  • If we remove 'group by' clause from having it works like a Where. That means both queries will give same result. So my question is which one is faster? – Abhi Jul 04 '13 at 09:49
  • Why would you want to use a `having` clause if you don't use `group by`. Makes no sense –  Jul 04 '13 at 09:50
  • 1
    I presume you are not on SQL Server then so have removed that tag. In SQL Server you would get `Column 'foo' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.` – Martin Smith Jul 04 '13 at 09:52
  • Yes, I am using MySQL. – Abhi Jul 04 '13 at 09:56
  • possible duplicate of [Which SQL statement is faster? (HAVING vs. WHERE...)](http://stackoverflow.com/questions/328636/which-sql-statement-is-faster-having-vs-where) – Prahalad Gaggar Jul 04 '13 at 10:01
  • @a_horse_with_no_name: Actually there are two scenario in finding near location using lat and long. In one we are calculating distance from source to destination location. In this case I just put 'Having dist > 200'. but there is one more way to get lat and long range, here if we pass it in 'where lat >= lat_calucated and lng <= lng_calucated. it gives same result. and that is the reason I am asking above question. – Abhi Jul 04 '13 at 10:04

3 Answers3

6

If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

You can use HAVING but recommended you should use with GROUP BY.

SQL Standard says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster.

Amit
  • 15,217
  • 8
  • 46
  • 68
  • "You can't use HAVING unless you also use GROUP BY." - I dont think so. We can use Having without 'group by'. But I think I got answer in your last sentence. – Abhi Jul 04 '13 at 09:52
  • @Abhi but recommended use with `Group By` – Amit Jul 04 '13 at 09:53
  • Actually there are two scenario in finding near location using lat and long. In one - we are calculating distance from source to destination location. In this case I just put 'Having dist > 200'. but there is one more way to get lat and long range, here if we pass it in 'where lat >= lat_calucated and lng <= lng_calucated. it gives same result. and that is the reason I am asking above question. Which one I have to prefer? – Abhi Jul 04 '13 at 10:06
  • @Abhi How could you get rows by distance from location just by specifying let>=lat_calc and lng<=lng_calc? when you search for location you always specify region (you can specify acceptable (calculated) ranges for lat and lng for searching in rectangle or calculating distance of two points and filtering it by having desired distance)...I'm afraid that getting same results is just coincidence. Isn't it? – JakubKnejzlik Aug 31 '14 at 22:52
  • @GrizzlyNetch Please check this link http://msdn.microsoft.com/en-us/magazine/jj133823.aspx SELECT userID FROM tblUsers WHERE latitude >= 47.0 AND latitude < 48.0 AND longitude >= -123.0 AND longitude < -122.0 - it will not return duplicate records. – Abhi Sep 02 '14 at 12:22
  • 1
    @Abhi "latitude >= 47.0 AND latitude < 48.0 AND longitude >= -123.0 AND longitude < -122.0" is fine (it's search by bounding box I've referenced as specifying ranges, similar to say "latitude BETWEEN 47 AND 48 AND longitude BETWEEN -123 AND -122" – although "BETWEEN 0 AND 1" is not exactly same as ">=0 AND <1"). The confusion for me was the incomplete condition "WHERE lat >= lat_calucated and lng <= lng_calucated" :) – JakubKnejzlik Sep 02 '14 at 12:32
1

Here is the difference between HAVING and WHERE: HAVING without GROUP BY You can not in general use it alternatively, but all about it is covered in this link

And here is the topic about their speed: Which SQL statement is faster? (HAVING vs. WHERE...)

Community
  • 1
  • 1
Fuv
  • 922
  • 2
  • 12
  • 25
0

The use of HAVING and WHERE are not interchangeable. They cover different topics, HAVING only seems equal to WHERE if there is not GROUP BY sentence

Miguel Prz
  • 13,718
  • 29
  • 42
  • http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause check this. If you dont use 'group by', 'Having' will work like 'Where'. – Abhi Jul 04 '13 at 09:54