Aliases

Hook: Aliases are like name tags at a conference: they do not change who the person is, but they make the conversation much easier.

Question: What are aliases in SQL?

Answer: An alias is a temporary name you give to a column or a table inside one query. You use it to make output easier to read, shorten long table names, or name a computed value like salary * 12. It does not rename the real column in the database, and it only exists for that statement.

Interview-Ready Answer: I use aliases to give a column or table a temporary name inside a query. For example, I might write salary * 12 AS annual_salary or employees AS e to make the SQL shorter and clearer. The key detail is that aliases only exist for that query result; they do not change the table schema, and in most databases I cannot use a SELECT alias in the WHERE clause because WHERE is evaluated earlier.

Detailed Explanation: Aliases are temporary labels, not real data changes. Think of them as query-time nicknames: the database keeps the original table and column names, but it shows a friendlier name in the result set or lets you reference a table with a shorter label inside the query.

How aliases work under the hood

1. The parser reads the query and records any alias names in the current query block. A query block is one SELECT statement or one subquery level.
2. The database resolves table aliases first, because they are needed in FROM, JOIN, and ON. That is why employees AS e lets you write e.salary later.
3. WHERE runs before the SELECT list is produced. So a column alias like annual_salary usually does not exist yet when filtering happens.
4. The engine computes the SELECT expressions and attaches column aliases to the output metadata. Metadata means the descriptive info about the result shape, such as column names.
5. ORDER BY runs after SELECT, so it can usually see the final output names. That is why ORDER BY annual_salary works in most databases.
6. The database returns the final rows with those temporary labels. Once the query ends, the aliases disappear.

Column alias vs table alias

The big mental model is this: a column alias changes the label on the result, while a table alias changes the short name you use to point at a source inside the query. A table alias is especially useful for self-joins, where the same table appears twice and you need two different names for it.

When and why to use aliases

• Use a column alias when the expression is ugly, such as price * quantity, and you want a clean report name like line_total.
• Use a table alias when table names are long, such as customer_order_history, or when joining multiple tables would otherwise repeat long prefixes everywhere.
• Use aliases in derived tables and CTEs so the outer query can refer to the computed result as a real column name.
• Use quoted aliases only when needed for spaces or reserved words. Standard SQL uses double quotes for quoted identifiers, but specific engines vary: SQL Server often uses brackets and MySQL often uses backticks.

Visibility rules that interviewers love

• WHERE: usually cannot see a column alias, because filtering happens before the select list exists.
• ORDER BY: usually can see a column alias, because sorting happens after the select list is created.
• GROUP BY and HAVING: support varies by dialect, so the safest portable habit is to repeat the expression or use a subquery/CTE.
• Sibling expressions in the same SELECT list usually cannot reuse a fresh alias from an earlier expression in that same list.

Performance and limits

Aliases are almost free. They do not add a table scan, index lookup, or join. Name resolution is just metadata work, so the overhead is tiny compared with real work like scanning rows, joining tables, or sorting results. If a query sorts 1 million rows, the sort still costs roughly O(n log n); the alias does not change that. In other words, aliases improve readability, not speed.

Two important edge cases matter in interviews. First, a derived table in the FROM clause usually must have an alias, because the engine needs a name for that temporary result. Second, do not assume every database treats alias visibility the same way. Some dialects are permissive, but portable SQL is stricter, and interviewers usually want the standard mental model.

Memory picture: aliases are sticky notes on the result, not new names on the filing cabinet. The data in the cabinet stays the same; only the label on the paper you are reading changes.

Real-World Example: In a checkout service, a nightly revenue job calculates net_revenue from order totals and refunds. The engineer writes a filter like WHERE net_revenue > 0 in the same query layer, expecting the alias to behave like a real column. In production, the job fails with an error such as column net_revenue does not exist, the dashboard stops refreshing, and finance sees stale numbers during the morning stand-up.

What happened? The alias only existed after the SELECT list was built, but WHERE ran earlier. The fix is to compute the value in a subquery or CTE, then filter in the outer query. Once the team did that, the report became stable and the logs stopped filling with repeated query errors.

That is why aliases matter beyond style: a small misunderstanding can break a production report, hide revenue drops, or make a support team chase a bug that is really just query timing.

Follow-up & Tricky Questions:

• Can I use a SELECT alias in WHERE? Usually no. WHERE runs before the SELECT list is produced, so the alias does not exist yet; use a subquery or CTE if you need to filter on the computed value.
• Can I use an alias in ORDER BY? Yes, in most databases. ORDER BY runs after SELECT, so the final output name is available for sorting.
• What is the difference between a table alias and a column alias? A table alias renames the source inside the query, while a column alias renames the output heading for one expression. The table alias helps joins; the column alias helps readability.
• Do aliases change the table permanently? No. They are only for the query result and vanish when the statement finishes.
• Do aliases affect performance? Not in any meaningful way. They are metadata labels, so the expensive work is still the scan, join, filter, or sort.
• When should I quote an alias? Only when you need spaces, punctuation, or a reserved word. Quoted aliases are legal, but they are annoying to type and should be used sparingly.
• Can a derived table in FROM be anonymous? In standard SQL, no. A subquery in FROM normally needs an alias so the outer query has a name to reference.
• Can one SELECT alias be reused by another expression in the same SELECT list? Usually no. If you need reuse, repeat the expression or wrap it in a subquery/CTE.

Tricky / Gotcha Questions:

• If I write salary * 12 AS annual_salary, can I filter with HAVING annual_salary > 100000? Sometimes a dialect will allow it, but it is not the safest portable habit. The clean approach is to use a subquery or CTE, especially if no GROUP BY is involved.
• Does AS change behavior? No. AS is mostly for readability; the alias is what matters. Many engines let you omit AS, but keeping it makes the query easier to scan.
• If I alias a column to the same name as the original column, did I rename the data? No. You only changed the visible label in that query, which can confuse readers and should usually be avoided.

Common Mistakes:

• Using a SELECT alias in WHERE. Correction: move the computation into a subquery or CTE, then filter in the outer query.
• Thinking aliases are permanent. Correction: aliases are temporary labels for one statement only; they do not change the schema.
• Forgetting table aliases in self-joins. Correction: give each copy of the table its own alias so you can tell them apart.
• Using awkward quoted names everywhere. Correction: reserve quoted aliases for special cases; simple names are easier to write and debug.

Memory Hook: Think: alias = sticky note, not a new file. The database keeps the real name in the drawer; the alias is just the label you see during this one query.

Cheat Sheet:

• Aliases are temporary names for columns or tables inside a query.
• Use AS for clarity, even when your database allows it to be omitted.
• WHERE usually cannot see a SELECT alias.
• ORDER BY usually can see a SELECT alias.
• Table aliases help joins; column aliases help output readability.
• Derived tables in FROM usually need an alias.

Practice Tasks:

• Rename three columns in a query with readable aliases like total_price and customer_name.
• Rewrite a long join using table aliases such as o and c instead of full table names.
• Take a computed column, try to filter on its alias, then fix the query with a subquery or CTE.