Projecting Columns and Aliasing Pitfalls
Why you cannot reference a SELECT alias in WHERE, and how interviewers test alias scope.
Projecting Columns and Aliasing Pitfalls is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Projection: Choosing Your Columns
Picking which columns a query returns is called projection — that's what you do every time you write SELECT. Name them explicitly in real code; save SELECT * for quick exploring.
SELECT id, first_name, email
FROM employees;Why Interviewers Dislike SELECT *
Why do interviewers dislike SELECT * in app code? It's fragile (column order can shift), wastes bandwidth, hurts index use, and hides what you actually need. Name your columns. 👍
Aliasing a Column with AS
An alias renames a column in your output. Use AS for clarity — it really shines on computed columns, giving messy expressions like salary * 12 a clean, readable header.
SELECT first_name AS name,
salary * 12 AS annual_salary
FROM employees;Quoting Aliases with Spaces
If an alias has spaces or a reserved word, you must quote it. The portable, standard way is double quotes. Tip: just stick to snake_case names and skip quoting entirely.
SELECT salary AS "Annual Pay"
FROM employees;The Big Pitfall: Alias Scope in WHERE
Here's the most-tested trap: define an alias in SELECT, then filter on it in WHERE, and it fails. WHERE runs before SELECT, so that alias doesn't exist yet.
-- This raises an error
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;The Reason: Logical Execution Order
The fix is knowing the logical clause order: FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY. SELECT is near the end, so earlier clauses can't see its aliases.
Fix 1: Repeat the Expression
The most portable fix is simple: just repeat the expression in WHERE instead of the alias. It's a little verbose, but it works in every database, every time.
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;Fix 2: Wrap in a Subquery or CTE
If repeating a long expression feels ugly, compute it in a subquery or CTE first. There the alias becomes a real column, so the outer WHERE can filter on it.
SELECT *
FROM (
SELECT first_name, salary * 12 AS annual_salary
FROM employees
) t
WHERE annual_salary > 100000;Where Aliases ARE Allowed: ORDER BY
Good news: ORDER BY can use a SELECT alias, because it runs after SELECT. So WHERE, GROUP BY, and HAVING can't see aliases — but ORDER BY can.
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;Dialect Differences to Name-Drop
Worth name-dropping: MySQL loosely allows aliases in GROUP BY and HAVING, while Postgres and SQL Server stick to the standard. Reason from logical order, then note the exception.
Table Aliases vs Column Aliases
Don't mix them up. A table alias shortens a table name and is visible everywhere, even in WHERE. A column alias renames output and isn't — the exact opposite.
SELECT e.first_name, e.salary AS pay
FROM employees AS e
WHERE e.salary > 50000;Quick Check
Test the core pitfall.
Recap
Recap: prefer explicit columns over SELECT *, and alias with AS. A SELECT alias is invisible in WHERE/GROUP BY/HAVING but works in ORDER BY. Fix it by repeating the expression or using a CTE.
Frequently asked questions
Is the “Projecting Columns and Aliasing Pitfalls” lesson free?
Yes — the full text of “Projecting Columns and Aliasing Pitfalls” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Projecting Columns and Aliasing Pitfalls”?
Why you cannot reference a SELECT alias in WHERE, and how interviewers test alias scope. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Projecting Columns and Aliasing Pitfalls” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Projecting Columns and Aliasing Pitfalls
- Computed Columns and Expression Precedence
- DISTINCT vs GROUP BY for Uniqueness
- CASE Expressions in SELECT