Where vs Having SQL

Question

I'm new to SQL, and I wanted to know what is the different between Where clause and Having clause. For me, it is kinda same and both are used to filter the record. Thank you for give me info.

Answer 1

WHERE is used to filter records before a GROUP BY acts on them (or in queries that don't have a group by)

HAVING is used to filter rows after a GROUP BY has acted on them. You can only specify columns in a HAVING clause if they have been grouped, or if they are part of an aggregate (means they are passed to some function like SUM(column), AVG(column), COUNT(column) etc)

Accounts employees who have had more than one pay rise:

--yes
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts'
GROUP BY emp_name 
HAVING count(*) > 1

--yes - equivalent if HAVING wasn't a thing
SELECT emp_name 
FROM (
  --do this first, count the records
  SELECT emp_name, COUNT(*) as ct 
  FROM pay_rise_log 
  WHERE dept = 'accounts' 
  GROUP BY emp_name 
) a
WHERE a.ct > 1 --and now filter to more than one pay rise

--no, you can't use a count(*) (done during a group by) before the group by is performed
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts' AND count(*) > 1 
GROUP BY emp_name

--no
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts'
GROUP BY emp_name
HAVING count(*) > 1 AND gender = 'male' --no, the gender column was not grouped and is not presented inside an aggregate function

--works, but is unusual. gender should be in the where clause
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts'
GROUP BY emp_name, gender
HAVING count(*) > 1 AND gender = 'male'

--works, but gender should be part of the WHERE clause to avoid needlessly counting females
--also has a potential bug if genders alphabetically after 'male' are present
--though it would count people who have been male even if they aren't currently, which may be a bonus?!
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts'
GROUP BY emp_name
HAVING count(*) > 1 AND MAX(gender) = 'male'

MySQL is a bit of a pain from a learning perspective here because in some configurations you can omit GROUP BY and it will do it implicitly for you, so you might not realise that a GROUP BY is being performed

In response to your comment, you can use a WHERE before a group by; you use it to choose which records you want to group. If there are 100,000 employees and only 100 in the accounts dept then it doesnt make sense to group and count ALL of them, only to throw 99% of data away:

--yes
SELECT emp_name 
FROM pay_rise_log 
WHERE dept = 'accounts' --pick 100 employees
GROUP BY emp_name 
HAVING count(*) > 1 --pick only those with 2 or more pay rises

--no (and potentially wrong too)
SELECT emp_name 
FROM pay_rise_log --pick 100,000 employees
GROUP BY emp_name 
HAVING count(*) > 1 and MAX(dept) = 'accounts' --pick only accounts staff who had more than 1 pay rise

Answer 2

For your information, apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. The example:

update CUSTOMER set CUST_NAME="Johnny" WHERE CUST_ID=1; //This line of code worked
update CUSTOMER set CUST_NAME="Johnny" HAVING CUST_ID=1; //Incorrect Syntax

WHERE clause is used for filtering rows and it applies toeach and every row, while HAVING clause is used to filter groups of rows in SQL.

While the WHERE and HAVING clause can be used together in a SELECT query with the aggregate function.

SELECT CUST_ID, CUST_NAME, CUST_GENDER
FROM CUSTOMER
WHERE CUST_GENDER='MALE'
GROUP BY CUST_ID
HAVING CUST_ID=8;

In this situation, WHERE clause will apply first on individual rows and only rows which pass the condition is included for creating groups. Once the group is created, HAVING clause is used to filter groups based upon condition specified.