What is the difference between HAVING and WHERE in SQL?

sql where-clause having
ColinYounger picture ColinYounger · Nov 13, 2008 · Viewed 307.2k times · Source

What is the difference between HAVING and WHERE in an SQL SELECT statement?

EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link:

When GROUP BY is not used, HAVING behaves like a WHERE clause

The situation I had seen the WHERE in did not have GROUP BY and is where my confusion started. Of course, until you know this you can't specify it in the question.

Answer

wcm picture wcm · Nov 13, 2008

HAVING: is used to check conditions after the aggregation takes place.
WHERE: is used to check conditions before the aggregation takes place.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a table of all cities in MA and the number of addresses in each city.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you a table of cities in MA with more than 5 addresses and the number of addresses in each city.

Related questions

SQL - HAVING vs. WHERE

I have the following two tables: 1. Lecturers (LectID, Fname, Lname, degree). 2. Lecturers_Specialization (LectID, Expertise). I want to find the lecturer with the most Specialization. When I try this, it is not working: SELECT L.LectID, Fname, Lname FROM Lecturers …

Read More
SQL Count where clause

I have the the following SQL statement: SELECT [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId] FROM (Leagues l INNER JOIN Lineups lp ON l.LeagueId = lp.LeagueId) WHERE (lp.PositionId = 1) OR (lp.PositionId = 3) OR (lp.PositionId = 2) What …

Read More
SQL JOIN - WHERE clause vs. ON clause

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different. What is the difference and what should go in each? If I …

Read More