Introduction

These last few days I have been studying SQL, reviewing concepts that I had already seen at university and learning new ones, one of them is the HAVING clause that, although I knew it existed, I never felt the need to use it and there were few exercises that I remember doing using this clause. Overall, as I delved deeper into the topic, I realized how useful it is to use it, minimizing queries and making them much more readable and intuitive.

But when I had already mastered and understood the use of HAVING, a question arose: Doesn’t this statement do the same thing as the WHERE clause? Searching the web I found some answers (in English) that guided me in this regard, but I did not find a clear and concise answer in Spanish (to date) that made clear the use and differences between both clauses.

For this reason, I have decided to write this article to make clear the fundamental difference between HAVING and WHERE, as well as an example that shows such difference.

Understanding HAVING and WHERE clauses

WHERE operates on individual records, while HAVING operates on a group of records.

The above is the main difference between these two clauses. With WHERE we can set a condition using individual records, those that meet this condition will be selected (deleted or updated); Now, with HAVING we can establish a condition on a group of records, something very important is that HAVING is usually accompanied by the GROUP BY clause. The latter is the case given that HAVING operates on the groups that “returns” GROUP BY.

So: WHERE on individual records and HAVING on groups of records, however there is nothing better to internalize and fully understand a concept than a good example, and that is precisely what we are going to do next.

Example

For this example I am using Oracle Database Express Edition 11g along with SQL Developer on Windows 10 64-bit. With the above clarified, let’s begin:

• We create a table called movies, which stores code, name, director, release date, genre, director and box office information for a cinema’s movies.

DROP TABLE movies;

-- We create the movies table

CREATE TABLE movies (
	code NUMBER(5) NOT NULL,
	name VARCHAR2(40) NOT NULL,
	director VARCHAR2(30) DEFAULT 'Unknown',
	release_date DATE,
	genus VARCHAR(20),
	collection NUMBER(20),
	PRIMARY KEY (code)
);

*Now let’s insert some records.

INSERT INTO movies VALUES(1, 'The Hunger Games', DEFAULT, '08/01/2014', 'Science Fiction', 1200000);
INSERT INTO movies VALUES(2, 'Harry Potter and the Goblet of Fire', DEFAULT, '04/10/2012', 'Science Fiction', 6005400);
INSERT INTO movies VALUES(3, 'The Chronicles of Narnia', DEFAULT, '10/22/2008', 'Science Fiction', 5600098900);
INSERT INTO movies VALUES(4, 'Schindler's List', 'Steven Spielberg', '03/22/1999', 'Drama', 456000120);
INSERT INTO movies VALUES(5, 'The Passion of the Christ', 'Steven Spielberg', '08/19/2010', 'Drama', 456000120);
INSERT INTO movies VALUES(6, 'Another Spielberg', 'Steven Spielberg', '07/11/2014', 'Drama', 456000120);
INSERT INTO movies VALUES(7, 'Life is Beautiful', 'Roberto Benigni', '10/23/1998', 'Drama', 1256000000);
INSERT INTO movies VALUES(8, 'The Possible Lives of Mr. Nobody', 'Jaco Van Dormael', '06/11/2009', 'Science Fiction', 340009023);
INSERT INTO movies VALUES(10, 'Finding Nemo', 'Andrew Stanton', '06/02/2007', 'Children', 780003400);
INSERT INTO movies VALUES(11, 'Toy Story', 'Andrew Stanton', '12/22/2004', 'Children', 679000300);
INSERT INTO movies VALUES(12, 'Toy Story 2', 'Andrew Stanton', '06/11/2007', 'Children', 5500300030);
INSERT INTO movies VALUES(14, 'Toy Story 3', 'Andrew Stanton', '06/11/2012', 'Children', 880776000);
INSERT INTO movies VALUES(15, 'Cars', 'Andrew Stanton', '05/14/2005', 'Children', 459000200);
INSERT INTO movies VALUES(16, 'Spirited Away', 'Hayao Miyazaki', '12/22/2004', 'Children', 456700000);
INSERT INTO movies VALUES(17, 'My Neighbor Totoro', 'Hayao Miyazaki', '06/20/1992', 'Children', 5500300210);
INSERT INTO movies VALUES(18, 'The Wind Rises', 'Hayao Miyazaki', '01/11/2013', 'Children', 990776000);
INSERT INTO movies VALUES(19, 'Nausica of the Valley of the Wind', 'Hayao Miyazaki', '10/22/1989', 'Children', 669000200);

• Let’s make some queries that involve the use of HAVING and WHERE

/*1. An easy one: Obtain the total collection, grouped by gender and 
director*/

SELECT genre, director, SUM(receipt) AS TOTAL FROM movies
	GROUP BY genre, director;

/*2. Now we want to obtain the total collection, grouped by genre and director, taking into account only those films that raised more than 80 pesos*/
  
SELECT genre, director, SUM(receipt) AS TOTAL FROM movies
  GROUP BY genre, director
  HAVING SUM(collection) > 80;

/*3. Now we want to obtain the total collection, grouped by genre and director, without taking into account the films whose director is
unknown and taking into account only those films that grossed more than 80 pesos.*/

SELECT genre, director, SUM(receipt) AS TOTAL FROM movies
  WHERE director <> 'Unknown' AND director IS NOT NULL
  GROUP BY genre, director
  HAVING SUM(collection) > 80;

• The confusion that WHERE does the same thing as HAVING comes from the following:

/*We want to obtain the proceeds from films grouped by genre and director but only from those whose genre is drama.*/

-- With where...
SELECT genre, director, SUM(receipt) AS TOTAL FROM movies
  WHERE genre LIKE '%Drama%'
  GROUP BY genre, director;

--With having...
SELECT genre, director, SUM(receipt) AS TOTAL FROM movies
  GROUP BY genre, director
  HAVING genre LIKE '%Drama%';

The two previous queries return the same records, but behave completely differently. In the first, we select genre, director and the sum of the collection as long as the genre is ‘Drama’ (WHERE) and later we group them by genre and director (GROUP BY).

In the second we select the genre, director and add up the collection, regardless of whether the genre is ‘Drama’ or not, then we group them by genre and director (GROUP BY). Finally, we select only the records whose genre is ‘Drama’ (HAVING). Also, if you paid attention, the result of the query made with HAVING takes twice as long as the query made with WHERE (0.008 sec and 0.004 sec respectively).

You may be wondering when to use HAVING or WHERE? From my point of view, we should use HAVING only when the use of group functions (AVG, SUM, COUNT, MAX, MIN) is involved, because with WHERE we cannot make conditions that involve these functions. For example, if you try this, you will get an error:

/*Obtain the average collection of the films, grouped by director, taking into account only those averages less than 40 and with known author*/

SELECT director, AVG(receipt) AS AVERAGE FROM movies
  WHERE AVG(recaudo) < 40 AND director NOT LIKE '%Unknown%'  
  GROUP BY director; 

The previous query generates an error since we are using group functions with a WHERE clause, which only operates on individual records, better try this:

/*Obtain the average collection of the films, grouped by director, taking into account only those averages less than 40 and with known author*/

SELECT director, AVG(receipt) AS AVERAGE FROM movies
  GROUP BY director
  HAVING AVG(collection) < 40 AND director NOT LIKE '%Unknown%'; 

I then recommend using HAVING when group functions are involved. If you have a simple condition that involves comparing individual fields then use WHERE (e.g. that the name is equal to a string, that the collection of a record is less than a value, etc.)

and of course, if you have other recommendations you can leave your comment below, it is always good to share what you know.

Conclusion

I hope that from this article you have learned that WHERE and HAVING behave differently even though sometimes they seem to do exactly the same thing. If you have any suggestions to make or want to contribute something else, you can leave them below in the comments. See you in a next entry.

print("See you soon")

Translated using GPT 5.3 Codex