Renaming Columns — AS (Aliases)

A SQL alias is a temporary name assigned to a column expression or a table for the duration of a query. It exists only in the query output — it does not change the database schema, the table structure, or the column names stored in the database. The AS keyword introduces the alias: expression AS alias_name. AS is optional in most databases but always recommended for readability.

The two types are column aliases and table aliases. A column alias renames a column in the result set — it controls the header name that appears in the output. Column aliases are most important for calculated columns, where without an alias the header would be the full expression text: unit_price - cost_price as a column header versus profit. Column aliases are also used to rename existing columns to more readable names for specific reports or applications.

A table alias gives a table a shorthand name for use throughout the query. Table aliases serve two purposes: they shorten long table names in queries that reference the same table many times, and they are essential in JOIN queries to distinguish which table each column comes from when multiple tables share the same column name. In self-joins — where a single table appears twice in the FROM clause — table aliases are required because the database needs two distinct names to refer to the two instances.

The answer comes from SQL's logical execution order. SQL clauses are not executed in the order they are written. The actual execution order is: FROM (identify source tables), WHERE (filter rows), GROUP BY (group filtered rows), HAVING (filter groups), SELECT (compute and name output columns), ORDER BY (sort the result), LIMIT (truncate to N rows).

Column aliases are defined during the SELECT step — step 6 in this sequence. WHERE is step 2 and GROUP BY is step 3. Since both execute before SELECT, aliases do not yet exist when WHERE and GROUP BY run. Referencing an alias in WHERE — WHERE profit > 50, where profit is a SELECT alias — would ask the database to use a name that has not been defined yet, which is why it fails with "column does not exist."

ORDER BY, however, executes after SELECT — step 7. By that point the SELECT has already run and all aliases have been defined. The database knows what profit means because SELECT has already computed and named it. This is why ORDER BY margin_pct DESC works correctly. The practical implication: repeat the full expression in WHERE and GROUP BY, but use the cleaner alias in ORDER BY. Some databases (MySQL, DuckDB) extend the standard and allow aliases in GROUP BY as a convenience — but this is not portable and should be avoided in code intended to run on multiple database systems.

Without an alias, a calculated column's header in the result is whatever the database chooses to call it — typically the full expression text. In PostgreSQL, unit_price - cost_price appears as a column named exactly "?column?" or "unit_price - cost_price". In MySQL it might be "unit_price - cost_price". ROUND((unit_price - cost_price) / unit_price * 100, 1) might appear as a column named with the entire expression.

This creates three practical problems. First, readability — a column named "ROUND((unit_price - cost_price) / unit_price * 100, 1)" in a report is useless to any business user. Second, application code — most application frameworks reference columns by name. A Python script doing result['margin_pct'] works. result['ROUND((unit_price - cost_price) / unit_price * 100, 1)'] does not. Third, portability — the auto-generated column name varies by database system, so the same query produces differently named columns on PostgreSQL and MySQL, breaking any code that references the column name.

The professional standard: every calculated column, every CASE expression, every aggregate function result, and every concatenated string expression should have an explicit alias. This applies even when you think only you will run the query — the next person to run it (or future you) will thank the aliases.

Table aliases are required in two specific situations. First, self-joins — when a table is joined to itself, the database needs two distinct names to refer to the two instances. Without aliases, FROM employees JOIN employees produces an error because there is no way to distinguish the two copies of the table. FROM employees AS e JOIN employees AS m provides the two names needed: e for the employee record and m for the manager record. This pattern is used for any hierarchy stored in a single table: org charts, category trees, location hierarchies.

Second, subquery aliases — any subquery in the FROM clause must be given an alias. The database treats the subquery result as a temporary table and needs a name to refer to it. FROM (SELECT ...) as an unnamed subquery produces a syntax error. FROM (SELECT ...) AS subq gives the derived table the name subq, which can then be referenced in the outer query's WHERE, JOIN ON, and SELECT clauses.

Table aliases are recommended (but not required) in all JOIN queries involving multiple tables. Without aliases, every column reference must be fully qualified with the table name: customers.customer_id, orders.order_id, customers.first_name — verbose and repetitive. With aliases c, o, the same references become c.customer_id, o.order_id, c.first_name — much more concise. Professional SQL writers use table aliases in every multi-table query as a matter of course, not just when required.

For column aliases — no, and it should not be done. If you define two columns with the same alias name in the same SELECT, the behaviour is database-specific. PostgreSQL returns both columns with the same name, which confuses application code that reads columns by name. Most other databases exhibit similar undefined or database-specific behaviour. The practical rule: every column alias in a SELECT must be unique. Column aliases are the names application code uses to access values, and duplicate names make it impossible to reliably access either value.

For table aliases — no, for the same reason. Two tables with the same alias in the FROM clause would create ambiguity about which table a column reference belongs to. FROM customers AS c JOIN orders AS c creates an error — the database cannot determine whether c.customer_id refers to the customers or orders alias. All table aliases in a FROM clause must be distinct.

The only scenario where the same alias name can appear is in separate subqueries at different levels of nesting. A subquery alias named totals does not conflict with another subquery alias also named totals in a different part of the query, because each alias is scoped to its own level of the query. However, this is still bad practice — use descriptive, unique names for every alias in the query regardless of scope. Repeated alias names, even in different scopes, create confusion for anyone reading the query.

Cause: You used a SELECT alias in a WHERE clause. WHERE executes before SELECT in the logical execution order, so when WHERE evaluates 'profit > 50', the alias 'profit' has not been defined yet — it will be defined later during SELECT. The database sees 'profit' in WHERE and looks for a column with that name in the table, finds none, and throws a column-not-found error.

Fix: Repeat the full expression in WHERE: WHERE (unit_price - cost_price) > 50. Do not use the alias. The alias is only available in ORDER BY (which runs after SELECT). If you find yourself repeating a complex expression many times, consider using a CTE (WITH clause, Module 55) or a subquery to define the calculation once and reference the alias in the outer query.

Cause: You used a subquery in the FROM clause without giving it an alias. The database needs to treat the subquery result as a temporary table and reference it by name in the rest of the query. Without an alias, the database has no name for the derived table and cannot proceed.

Fix: Add AS alias_name immediately after the closing parenthesis of the subquery: FROM (SELECT ... FROM ...) AS my_subquery. The alias name can be anything valid — use something descriptive that explains what the subquery contains: customer_orders, monthly_totals, ranked_products. Then reference the subquery's columns using alias.column_name in the outer SELECT and WHERE.

Cause: The table alias declaration is incomplete — AS appears but the alias name is missing. This happens when an alias is started but not finished, often from a copy-paste error or mid-edit save. AS must be followed immediately by the alias name with no other keywords between them.

Fix: Complete the alias: FROM customers AS c. If you do not want a table alias at all, remove the AS entirely: FROM customers. Check that no SQL keywords accidentally ended up where the alias name should be — FROM customers AS WHERE would trigger this error because WHERE is a SQL keyword, not a valid alias name (unquoted).

Cause: You forgot to add an AS alias to a calculated column. The database auto-generates a column name from the expression, which varies by database: PostgreSQL might show '?column?', MySQL shows the full expression text, DuckDB shows '(unit_price - cost_price)'. This is not an error — it is the expected behaviour without an alias.

Fix: Add AS alias_name after the expression: unit_price - cost_price AS profit. Always alias every calculated column, every aggregate function result, and every CASE expression. Build this as a habit rather than a correction — it prevents the problem entirely. If you are working from a query someone else wrote and cannot modify it, most SQL clients allow you to rename columns in the result view — but the SQL itself should have proper aliases.

Cause: You used a positional reference in GROUP BY (GROUP BY 1, GROUP BY 2) instead of the column name or alias. When you later add a new column to the SELECT list before the grouped column, the position numbers shift — GROUP BY 1 now refers to the newly added first column, not the intended column. The query runs without error but groups on the wrong column, producing incorrect results.

Fix: Always use the column name or expression in GROUP BY, never positional references: GROUP BY product_name instead of GROUP BY 1. If you need to group by an expression, repeat the expression: GROUP BY EXTRACT(MONTH FROM order_date). This makes the GROUP BY clause self-documenting and immune to SELECT list reordering. The same applies to ORDER BY — avoid ORDER BY 1, ORDER BY 2 in production queries; use column names or aliases instead.

Write a clean, well-aliased query for the FreshCart monthly business review. The query should show: each store's ID (aliased as 'store'), the store's city, total number of orders placed (aliased as 'total_orders'), total revenue from delivered orders only (aliased as 'delivered_revenue'), and average order value across all orders (aliased as 'avg_order_value', rounded to 2 decimal places). Join the stores and orders tables. Group by store. Sort by delivered_revenue descending.