Databases and SQL: Incorrect use of column alias in HAVING clause in SQL example

[dekr1sh]

Checks

• This is not a duplicate of an existing issue (please have a look through our open issues list to make sure)
• I have thoroughly read and understand The Odin Project Contributing Guide
• Would you like to work on this issue?

Describe your suggestion

Suggestion: Correct SQL Example to Avoid Alias in HAVING Clause

Issue:
The SQL snippet on the page currently uses a column alias (posts_written) in the HAVING clause:

SELECT users.id, users.name, COUNT(posts.id) AS posts_written
  FROM users
  JOIN posts ON users.id = posts.user_id
  GROUP BY users.id
  HAVING posts_written >= 10;

Using a column alias in HAVING is not supported in standard SQL due to the query execution order. HAVING is processed before the SELECT statement assigns any aliases, so the alias posts_written does not yet exist at that point in the query execution. This can lead to an error in many SQL databases.

Proposed Solution:
To ensure compatibility across SQL databases and adhere to SQL standards, it would be better to use the full expression (COUNT(posts.id)) in the HAVING clause:

SELECT users.id, users.name, COUNT(posts.id) AS posts_written
  FROM users
  JOIN posts ON users.id = posts.user_id
  GROUP BY users.id
  HAVING COUNT(posts.id) >= 10;

Explanation:
This adjustment will ensure that the query is compatible with SQL databases that strictly follow standard SQL, improving the reliability of the example for learners.

Path

Node / JS

Lesson Url

https://www.theodinproject.com/lessons/node-path-databases-databases-and-sql

(Optional) Discord Name

de_kr1sh

(Optional) Additional Comments

Looking forward to contributing to this issue as it will be my first contribution to an open-source project.