The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers
«
»

SQL: WHERE versus HAVING

Posted by jpluimers on 2025/07/08

While writing Some SQLite things I recently learned a while ago I bumped into SQLite extending the SQL syntax allowing SELECT column aliases in WHERE and ON conditions of JOIN clauses whereas standard SQL only allows it in the HAVING clause (some other DMBS allow this as well).

It reminded I never wrote a blog post about WHERE versus HAVING, which can be phrased into just two points:

  • WHERE is applied before the SELECT is assembled
  • HAVING is applied after the SELECT is assembled

This also means WHERE has a lot more influence on performance than HAVING, so using HAVING just so you can use aliases there instead of in the WHERE clauses is not a smart idea unless you verify in the query plan and query optimisation output of our DBMS this has no influence (some DBMS even allow you to see the results of query rewriting which you can use to verify if two queries really are equivalent from the DBMS perspective).

Since HAVING can be used without GROUP BY, many people work around the WHERE alias limitation. Given the above effects on performance, this is not a wise idea, despite the same end-results. See the example below that uses the [Wayback/Archive] MySQL :: Sakila Sample Database: customer table.

Luckily there has been enough other material explaining WHERE versus HAVING, so I can reference it from here, for instance (note note limited to MySQL/MariaDB) [Wayback/Archive] mysql – WHERE vs HAVING – Stack Overflow (thanks [Wayback/Archive] baloo and [Wayback/Archive] Fishdrowned):

Q

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL?
And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?

A

…

You will get exactly the same results, you can see the HAVING clause can work without GROUP BY clause.
Here’s the difference:
SELECT `value` v FROM `table` WHERE `v`>5;
The above query will raise error: Error – Unknown column ‘v’ in ‘where clause’
SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows
WHERE clause allows a condition to use any table column, but it cannot use aliases or aggregate functions. HAVING clause allows a condition to use a selected (!) column, alias or an aggregate function.
This is because WHERE clause filters data before select, but HAVING clause filters resulting data after select.
So put the conditions in WHERE clause will be more efficient if you have many many rows in a table.
Try EXPLAIN to see the key difference

I got there via [Wayback/Archive] sql – Using column alias in WHERE clause of MySQL query produces an error – Stack Overflow (thanks [Wayback/Archive] James, [Wayback/Archive] victor hugo, [Wayback/Archive] DTs, [Wayback/Archive] Millie Smith, and [Wayback/Archive] Pacerier):

Q

The query I’m running is as follows, however I’m getting this error:
#1054 - Unknown column 'guaranteed_postcode' in 'IN/ALL/ANY subquery'
SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE `guaranteed_postcode` NOT IN #this is where the fake col is being used
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)
My question is: why am I unable to use a fake column in the where clause of the same DB query?

A

…

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.

C

This may or may not be important in your case, but HAVING executes slower than WHERE

C

The reason having works is because the column values have to be computed by the time you get to the having. This isn’t the case with where, as stated above.

C

@MillieSmith, That’s true only for aggregate predicates in having. As we know, having can have non-aggregate predicates, just like where. In such a case, it isn’t hard for the optimizer to convert that having predicate to an expanded where predicate. As such, citation needed. You can find it here: stackoverflow.com/a/18710763/632951

HAVING without GROUP BY?

Being taught SQL the classic way, it took several years for me to realise that HAVING can be used without GROUP BY.

In the current day and age of learning computer languages by searching for quick results, I an sort of glad that the “classic” way at the time of writing is still explained at HAVING (SQL) – Wikipedia

A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions.

HAVING and WHERE are often confused by beginners, but they serve different purposes. WHERE is taken into account at an earlier stage of a query execution, filtering the rows read from the tables. If a query contains GROUP BY, rows from the tables are grouped and aggregated. After the aggregating operation, HAVING is applied, filtering out the rows that don’t match the specified conditions. Therefore, WHERE applies to data read from tables, and HAVING should only apply to aggregated data, which isn’t known in the initial stage of a query.

Anyway, these two have a different query plan where the HAVING uses more resources as no candidate keys or indices is used: [Wayback/Archive] MySQL 8.0 | dbfiddle:

explain
select *
from   customer
where  store_id = 1
  and  active = 0
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE customer null ref idx_fk_store_id idx_fk_store_id 1 const 326 10.00 Using where
explain
select *
from   customer
having store_id = 1
   and active = 0
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE customer null ALL null null null null 599 100.00 null

But they give equivalent results as you can see in [Wayback/Archive] MySQL 8.0 | dbfiddle:

select *
from   customer
where  store_id = 1
  and  active = 0
customer_id store_id first_name last_name email address_id active create_date last_update
124 1 SHEILA WELLS SHEILA.WELLS@sakilacustomer.org 128 0 2006-02-14 22:04:36 2006-02-15 04:57:20
271 1 PENNY NEAL PENNY.NEAL@sakilacustomer.org 276 0 2006-02-14 22:04:36 2006-02-15 04:57:20
368 1 HARRY ARCE HARRY.ARCE@sakilacustomer.org 373 0 2006-02-14 22:04:37 2006-02-15 04:57:20
406 1 NATHAN RUNYON NATHAN.RUNYON@sakilacustomer.org 411 0 2006-02-14 22:04:37 2006-02-15 04:57:20
482 1 MAURICE CRAWLEY MAURICE.CRAWLEY@sakilacustomer.org 487 0 2006-02-14 22:04:37 2006-02-15 04:57:20
534 1 CHRISTIAN JUNG CHRISTIAN.JUNG@sakilacustomer.org 540 0 2006-02-14 22:04:37 2006-02-15 04:57:20
558 1 JIMMIE EGGLESTON JIMMIE.EGGLESTON@sakilacustomer.org 564 0 2006-02-14 22:04:37 2006-02-15 04:57:20
592 1 TERRANCE ROUSH TERRANCE.ROUSH@sakilacustomer.org 598 0 2006-02-14 22:04:37 2006-02-15 04:57:20
from   customer
having store_id = 1
   and active = 0
customer_id store_id first_name last_name email address_id active create_date last_update
124 1 SHEILA WELLS SHEILA.WELLS@sakilacustomer.org 128 0 2006-02-14 22:04:36 2006-02-15 04:57:20
271 1 PENNY NEAL PENNY.NEAL@sakilacustomer.org 276 0 2006-02-14 22:04:36 2006-02-15 04:57:20
368 1 HARRY ARCE HARRY.ARCE@sakilacustomer.org 373 0 2006-02-14 22:04:37 2006-02-15 04:57:20
406 1 NATHAN RUNYON NATHAN.RUNYON@sakilacustomer.org 411 0 2006-02-14 22:04:37 2006-02-15 04:57:20
482 1 MAURICE CRAWLEY MAURICE.CRAWLEY@sakilacustomer.org 487 0 2006-02-14 22:04:37 2006-02-15 04:57:20
534 1 CHRISTIAN JUNG CHRISTIAN.JUNG@sakilacustomer.org 540 0 2006-02-14 22:04:37 2006-02-15 04:57:20
558 1 JIMMIE EGGLESTON JIMMIE.EGGLESTON@sakilacustomer.org 564 0 2006-02-14 22:04:37 2006-02-15 04:57:20
592 1 TERRANCE ROUSH TERRANCE.ROUSH@sakilacustomer.org 598 0 2006-02-14 22:04:37 2006-02-15 04:57:20

--jeroen

This entry was posted on 2025/07/08 at 12:00 and is filed under Conference Topics, Conferences, Database Development, Development, Event, MariaDB, MySQL, SQL, SQLite. Tagged: , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«
»