The need to switch between column names, column aliases, column numbers, and repeated expressions to reference the same column in different parts of the same query is a serious flaw in SQL.

A fellow database expert reposted an interview question on LinkedIn. Is a column alias allowed in the having clause of a SQL select statement?

I created a similar example using population data, and tested it in PostgreSQL 18.

insert into county_city_population
   ( county,    city     , population)
values
   ('alameda', 'alameda' ,      80023)
  ,('alameda', 'berkeley',     127654)
-- ...
;
See the table definition 
create table county_city_population
  (
     county     county     constraint county_not_null     not null
    ,city       city       constraint city_not_null       not null
    ,constraint co_ci_pop_primary_key primary key (county, city)

    ,population population constraint population_not_null not null
  )
;
comment on table county_city_population is
  'california 2026-01-01 population by county and city, adapted from dof.ca.gov/forecasting/demographics/price-and-population-factors , PP26AttachmentA_B_C.xlsx , attachment b'
;

Considering the first two values in the first sample row, if you like adding type hints in Python, you'll love domain , a seldom-used part of the SQL standard. Find out more by reading the source code, at github.com/sqlxpert/sql-sin-column-refs-having-a-nice-day ! This is the last good thing I'll say about SQL in this article.

Our goal is to find California counties with at least 1 million people.

select
   county
  ,sum(population) as population_total
from
   county_city_population
group by
   county
having
  population_total >= 1000000
  -- trick question: is population_total allowed here?
order by
   population_total
;

In the LinkedIn thread, commentators theorized that the approach would work in MySQL but not in PostgreSQL. One person who is clearly very intelligent and thoughtful wrote that the direction of the "semantic dependency" makes using a column alias inappropriate in the having clause. He added that a reference in the order by clause would be different because that's "outside of the relational result set".

Our minds are locked into SQL to the point that we forget the Relational Model!

Aren't they the same thing? No, sadly, and referencing one column in as many as four different ways within the same select statement is a perfect example of the confusion and extra work that SQL foists on us.

This kind of question is a terrible guide to hiring because it tests memorization of computer language syntax, nothing more. By asking the candidate to rewrite the query without a having clause, we could find out how thoroughly the person understands SQL, SQL database management systems, and the Relational Model.

Someone with broad and up-to-date SQL knowledge might suggest a common table expression. Maybe the candidate favors a stepwise approach and an eminently readable query. Such an instinct would be especially valuable when working with less technical colleagues.

See the common table expression alternative 
with

   county_population as (
     select
        county
       ,sum(population) as population_total
     from
        county_city_population
     group by
        county
   )

select
   county
  ,population_total
from
   county_population
where
  population_total >= 1000000
order by
   population_total
;

Any SQL user might think to define a view...

See the view alternative 
create view county_population as
  select
     county
    ,sum(population) as population_total
  from
     county_city_population
  group by
     county
;

select
   county
  ,population_total
from
   county_population
where
  population_total >= 1000000
order by
   population_total
;

...or use a subquery, although the reading order becomes unnatural. You have to read the middle first to make sense of this form.

See the subquery alternative 
select
   county
  ,population_total
from
   (
     select
        county
       ,sum(population) as population_total
     from
        county_city_population
     group by
        county
   ) as county_population
where
  population_total >= 1000000
order by
   population_total
;

After identifying the alternatives, we could talk about whether their "performance" (or heaven help us, their semantics!) might differ. Some SQL DBMS products rewrite queries in a canonical (standardized) form that they know how to optimize. Some products support pushdown, which makes the goals of the query available at every stage, so that work can be done earlier if it will save time later.

A daring candidate would call bullshit on the whole business. Imagine a relational DBMS (as opposed to a SQL DBMS). Within a query statement, every step whose results we can reference in that same statement yields a relation, fundamentally, a set of tuples.

Sure, if our query statement calls for the tuples to be sorted, the final output of the statement will not be a set. (The elements of a set are unordered.) So what? We do not need to (and we cannot) reference the final output anywhere inside the query statement!

Therefore, back in the land of SQL, there's nothing special about the order by clause of a select statement. If we can define column aliases in the select list and then reference the columns by alias in the order by clause, there's no fundamental reason why those column aliases couldn't be made available for us to reference in the having clause, too.

Now, let's look inside all the tuples in a relation. There has to be some way to say which element we want. In SQL, the potential for nameless columns and for multiple columns with the same name, as well as the need to switch between...

  • column names,
  • column aliases,
  • column numbers, and
  • repeating an earlier expression

...to reference the same column in different clauses of the same select statement, is a serious flaw. This is what's been accomplished, more than 55 years after the advent of the Relational Model and more than 50 years after the appearance of SQL. 😒

I've already demonstrated the first two ways to reference columns in a SQL select statement. Here are the last two ways. Incidentally, the column number in the order by clause used to be required in this scenario!

See the numbered column horror 
select
   county                              -- column 1
  ,sum(population) as population_total -- column 2
from
   county_city_population
group by
   county -- column name
having
  sum(population) >= 1000000
  -- population_total column alias not permitted by postgresql 18
  -- must repeat sum(population) expression (bad!)
order by
   2 -- column number used to be required (worse!)
;

Switching to a CTE, a view, or a subquery to avoid the issue isn't ideal, either. It shouldn't be necessary, for such a simple query goal. A relational DBMS (as opposed to a SQL DBMS) would be able to translate our intentions into efficient instructions without needing to be told so explicitly which step to do first.

The Relational Model was meant to let us access data without worrying about how the data are organized in physical storage. The RM leaves as much work as possible to the computer. For all we know, a relational DBMS might tally county population as cities are added. It might keep the cities in order by population so that, given a different goal like finding counties with fewer than 100,000 people, it could immediately use large cities to rule out counties.

The point is that the best thing to do first depends on the data and can change with the data. SQL DBMS users, please keep this mind if you haven't yet set up continuous monitoring to detect changes in index utilization, and in query plans for frequent queries.

I wrote one big select statement that shows off all the sins of column reference syntax in SQL. In each clause, alternative column references are commented out, and I indicate which references will work and which ones won't, for PostgreSQL 18. Just seeing a DBMS invent arbitrary names for columns makes this fun. Try it out in your favorite SQL DBMS!

See all the sins of column reference syntax in one SQL query 
select
   county
  ,county              as county2
  ,avg(population)     -- meaningless; gets arbitrary column name
  ,sum(length(county)) -- meaningless; gets arbitrary column name
  ,sum(population)     -- gets duplicate arbitrary column name
  ,sum(population)     as population_total
  ,count(city)         as cities
from
   county_city_population

group by
   -- column name:
   county

   -- postgresql 18 permits column alias:
   -- county2

having
  -- postgresql 18 does not permit column alias
  -- (error: ... "population_total" does not exist):
  -- population_total >= 1000000

  -- must repeat expression from select list:
  sum(population) >= 1000000

order by
   -- numbered column (column order from select list):
   6  

   -- postgresql 18 permits column alias:
   -- population_total

   -- postgresql 18 permits referencing the arbitrary column name:
   -- avg

   -- no way to distinguish duplicate arbitrary column names
   -- (error: ... "sum" is ambiguous):
   -- sum
;

I hope you learned something new from this article, and I hope you'll be alert for compromises in SQL DBMS products that create extra work and deprive us of Relational Model benefits. Please feel free to leave a comment on the LinkedIn version.

To avoid distraction, I made two simplifications:

  • Wherever I refer to time, it could just as well be memory, or disk space.
  • In the Relational Model, a relation has two components. The set of tuples I refer to is the "body". There is also a set of attribute names, the "heading".