SQL Standard

SQL Having vs Where

In some instances, you may encounter cases where you need to filter records from a database using the where clause. This allows you to specify a specific condition and only apply the set actions to rows that match the conditions.

Another typical use case is when you need to apply a filter to a group of records as one unit. That is where the having clause comes in. It is similar to the where clause but can be applied to an aggregate result.

What is the Difference?

There is only one difference between the where and having clauses:

  1. The where clause is applied to individual rows in a table.
  2. On the other hand, the having clause is applied to a group of rows that meet the specified condition. You can also use the having clause in an aggregate function.

Where Clause Example

Consider the employees table containing sample data as provided in the statements below:

CREATE TABLE employees (
    id serial PRIMARY KEY,
    full_name VARCHAR(255),
    email VARCHAR(255),
    department VARCHAR(100),
    start_date DATE,
    active bool,
    category VARCHAR(50)
);
INSERT INTO employees (full_name, email, department, start_date, active, category)
VALUES
    ('Meghan Edwards', '[email protected]', 'Game Development', '2016-06-22', TRUE, 'DEV-G1'),
    ('Sam Davis', '[email protected]', 'Game Development', '2014-01-04', FALSE, 'DEV-G1'),
    ('Taylor Miller', '[email protected]', 'Game Development', '2019-10-06', TRUE, 'DEV-G1'),
    ('Wilson Smitty', '[email protected]', 'Database Developer', '2016-12-23', TRUE, 'DEV-DB1'),
    ('Barbara Moore', '[email protected]', 'Database Developer', '2015-12-23', TRUE, 'DEV-DB1'),
    ('Raj Chan', '[email protected]', 'Database Developer', '2017-12-23', FALSE, 'DEV-DB1'),
    ('Susan Brown', '[email protected]', 'DevOps Engineer', '2011-12-23', TRUE, 'DEV-OPS1'),
    ('Marial Anderson', '[email protected]', 'DevOps Engineer', '2015-12-23', TRUE, 'DEV-OPS1'),
    ('Nancy Thomas', '[email protected]', 'DevOps Engineer', '2014-12-23', FALSE, 'DEV-OPS1');

We can use the select with the where clause to fetch the rows where the category equals ‘DEV-OPS-1’.

An example expression is as shown:

SELECT *
FROM EMPLOYEES
WHERE CATEGORY = 'DEV-OPS1';

The query above is applied on row per row basis and returns the result set as shown:

Having Clause Example

As stated, the having clause is applied on aggregate. For example, consider the table with sample data as shown in the query below:

CREATE TABLE youtube_stats(
    id serial,
    channel_id VARCHAR(50),
    video_id VARCHAR(50),
    views_count INT
);
INSERT INTO youtube_stats(channel_id, video_id, views_count)
VALUES
('chan1', 'vid1', 100000),
('chan1', 'vid2', 500000),
('chan2', 'vid1', 400000),
('chan2', 'vid2', 1000000),
('chan3', 'vid1', 800000),
('chan4', 'vid1', 262529);

We can use the having clause aggregate groups as shown:

SELECT ID,
    CHANNEL_ID,
    VIDEO_ID,
    SUM(VIEWS_COUNT) AS TOTAL_VIEWS
FROM YOUTUBE_STATS
GROUP BY CHANNEL_ID
HAVING TOTAL_VIEWS >= 100000

The above query should return only the elements where the sum is greater than or equal to the above condition.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list