83535

Why do you have 'where' when there is 'having' [duplicate]

Question:

This question already has an answer here:

<ul><li> <a href="/questions/9253244/sql-having-vs-where" dir="ltr" rel="nofollow">SQL - having VS where</a> <span class="question-originals-answer-count"> 7 answers </span> </li> </ul>

I know this is much discussed, but none of my research could convince me the difference between 'where' and 'having' clauses in MySQL. From what I understand we can achieve everything that can be done with 'where' clause using 'having' . For eg. select * from users having username='admin'. Then why do you need 'where' clause? Does using where make any performance differences?

Answer1:

The WHERE clause filters data from the source before aggregates, whereas HAVING clause filters data after the GROUP BY has been applied. Generally this means any non-aggregate filter can appear in either place, but if you have a column that is not referenced in your query, you can only filter it in a WHERE clause.

For example, if you have the following table:

| ID | VALUE | -------------- | 1 | 15 | | 2 | 15 | | 3 | 20 | | 4 | 20 | | 5 | 25 | | 6 | 30 | | 7 | 40 |

Suppose you wanted to apply the following query:

select value, count(value) from Table1 group by value

But you only wanted to include rows where ID > 2. If you put that in a HAVING clause, you will get an <a href="http://www.sqlfiddle.com/#!2/f6741/17" rel="nofollow"><strong>error</strong></a>, because the ID column is not available post aggregate as it is not in the SELECT clause. In that case, you would be required to use a WHERE clause instead:

select value, count(value) from Table1 where id > 2 group by value

Demo: <a href="http://www.sqlfiddle.com/#!2/f6741/16" rel="nofollow">http://www.sqlfiddle.com/#!2/f6741/16</a>

Answer2:

The difference between HAVING from WHERE clause is that HAVING supports aggregated columns while WHERE doesn't because it is only applicable for individual rows., EG

SELECT ID FROM tableName GROUP BY ID HAVING COUNT(ID) > 1 --- <<== HERE <hr />

From the MySQL docs,

<em>"You may use Alias's if you use HAVING instead of WHERE this is one of the defined differences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren't expecting great speed."</em>

Answer3:

Where evaluates on the single row level, whereas having is used for group by expressions.

Answer4:

With the HAVING clause, you can specify a condition to filter <strong>groups</strong> as opposed to filtering <strong>individual</strong> rows, which happens in the WHERE phase. Only groups for which the logical expression in the HAVING clause evaluates to TRUE are returned by the HAVING phase . Groups for which the logical expression evaluates to FALSE or UNKNOWN are filtered out.

When GROUP BY is not used, HAVING behaves like a WHERE clause . Regarding performance comparison please see this <a href="http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/" rel="nofollow">article</a>

Recommend

  • How to get the device Model in objective c?
  • Jquery ajax complete not getting custom reponse header?
  • Lazy loading in datatable JSF
  • mysql setting a negative integer value to a primary auto increment field which is int
  • Retrieving saved checkboxes' name and values from database
  • Java: How to refer to subclass's static variable in abstract class?
  • Using Facebook Graph API with ASP.NET
  • Newtonsoft.json serializing and deserializing base/inheirited where classes are from shared projects
  • Using Autofac with AJAX-enabled WCF Service
  • Storing the Cursor for App Engine Pagination
  • iPhone SDK Add Image to the Body of an Email?
  • Python tk scrollbar becomes inactive once text is outside the screen
  • Handle query parameters recursively using htaccess
  • Get all categories and items in category
  • Nested AJAX Calls using .done
  • Python sum values in tuple in a list in a dictionary?
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • what do lines starting with double-slash '//' mean in a .npmrc?
  • Slick: How can I combine a SQL LIKE statement with a SQL IN statement
  • Signed Java web start application with Glassfish 4.1 and Java7
  • playing mp3 from nsbundle
  • How to define a custom accuracy in Keras to ignore samples with a particular gold label?
  • What does “T extends Junk” mean in a generic class in Java?
  • Set SelectedIndex of ListView in FlipView_SelectionChanged event
  • How to redirect into different page by user type in php and mysql
  • Thumbnails for mxml components in Flex
  • How to check if a database and tables exist in sql server in a vb .net project?
  • Rotating Towards Path in OpenGL
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • Problems to understand DXGI DirectX 11 Desktop Duplication to get a Buffer or Array
  • Calculate time from document
  • 'url' requires a non-empty first argument. The syntax changed in Django 1.5, see the docs
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • how to get the location(lat/lng) on google maps v3 from the location(x,y)
  • How to change user identity when git pushing via ssh?
  • VSTS work items list through REST API
  • Functions by reference or by variable, which to use when?
  • ssh remote server login script
  • convert json to excel in java