CODEWITHSUNDEEP
sql
Alex Gordon
Alex Gordon

Reputation: 60751

SQL: using WHERE AND instead of HAVING

here's an example of a SQL statement where we use HAVING:

select column1 from table1
where condition1
having condition2;

isn't it the same exact thing if we do this:

select column1 from table1
where condition1 AND condition2;

what is the difference between these two?

Upvotes: 13

Views: 9987

Answers (9)

onedaywhen
onedaywhen

Reputation: 57023

As others have (mostly) correctly stated, in SQL the WHERE clause is evaluated before the SELECT clause, therefore the result of a set function is 'out of scope' in the WHERE clause.

For example, you CANNOT do this:

SELECT Subject, MAX(Mark) AS TopScore
  FROM Exam_Marks
 GROUP 
    BY Subject
 WHERE TopScore <= 70;

because the column correlation name TopScore is not in scope for the WHERE clause.

Of course we could use a subquery:

SELECT DT1.TopScore
  FROM (
        SELECT Subject, MAX(Mark) AS TopScore
          FROM Exam_Marks
         GROUP 
            BY Subject
       ) AS DT1
 WHERE DT1.TopScore <= 70;

The problem was, early implementations of SQL (starting with IBM's System R) lacked support for derived tables, hence the unintuitive HAVING was born.

You can read the whole sorry story in HAVING A Blunderful Time (or Wish You Were WHERE) by Hugh Darwen, from which I've borrowed the above examples.

Upvotes: 4

kacalapy
kacalapy

Reputation: 10134

to use having you need a group by clause. you will get an error without one

Upvotes: -1

dretzlaff17
dretzlaff17

Reputation: 1719

HAVING specifies a search condition for a group or an aggregate function used in a SELECT statement.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Having http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

Upvotes: 2

gbn
gbn

Reputation: 432271

In your example, it is the same because you have no GROUP BY

Otherwise, HAVING is applied after GROUP BY which is applied after WHERE...

Saying that, HAVING with a simple filter (x = 2) is exactly the same as WHERE because x = 2 only has meaning if you grouped on it. You normally use HAVING on an aggregate (COUNT(*) > 2 for example) that can only be applied after GROUP BY

Upvotes: 5

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

No, they are completely different.

Having conditions are for grouping aggregate functions. They are computed after the aggregated value was computed.

Example:

select id, count(1) 
  from table
 where COND1
 having count(1) > 1

Here, the having part is evaluated after the query computed the count(1) value for each group.

Upvotes: 4

Daniel Pryden
Daniel Pryden

Reputation: 60957

In your example, they should do the same thing. But WHERE gets processed before any GROUP BY, and so it doesn't have access to aggregated values (that is, the results of Min(), Max(), etc. functions). HAVING gets processed after GROUP BY and so can be used to constrain the result set to only those with aggregated values that match a certain predicate.

Upvotes: 26

Femaref
Femaref

Reputation: 61437

Having only works with a group by clause and limits records after they are grouped.

Upvotes: 1

Hank Gay
Hank Gay

Reputation: 71949

HAVING is for use with aggregates, e.g., HAVING SUM(column1) > 200, WHERE is just for the columns, e.g., WHERE column1 < 20.

Upvotes: 6

Bruno Costa
Bruno Costa

Reputation: 2720

No, because having is for aggregate functions or group by clause.

For example:

SELECT COUNT(ID)
FROM tablexpto
where name = 'a'
having count(ID) > 1

The first query would not run.

Upvotes: 3

Related Questions