Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 125 Β· Querying Data

Column and Table Aliases

Rename columns, expressions and tables with SQL aliases and AS. Learn quoting rules, table aliases in joins, and exactly where each database lets you use an alias.

When you write SELECT price * stock FROM products, the result column is called price * stock. That's awkward to read and awkward to use in application code. An alias gives a column, an expression or a table a new name for the duration of one query. The table itself isn't renamed; only the result (or the way you refer to the table inside the query) changes.

Aliases look like a small convenience, but they're everywhere in real SQL. They make reports readable, they're required for some queries, and they're the first place where you meet an important idea: the clauses of a SELECT are evaluated in a fixed order, which decides where a name you invented can be used.

Column aliases with AS

Write AS and the new name after any column or expression in the SELECT list:

SQL Naming result columns Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result columns are now called product, unit_price and stock_value. Without the last alias, the heading would be the raw expression price * stock, and in some databases (SQL Server, for example) it would have no name at all. When your application reads rows by column name, unnamed or expression-named columns are a nuisance, so always alias calculated columns.

AS is optional for column aliases: price * stock stock_value means the same thing. Most style guides keep AS, though, because without it a missing comma silently turns a column into an alias:

SQL The missing comma bug Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result has only two columns, and the one called price contains product names. No error, no warning. Writing AS every time makes this kind of mistake stand out.

Aliases with spaces and special characters

An alias is an identifier, so the same rules apply as for table and column names (see SQL Syntax and Style). A simple name in snake_case needs no quotes. If you want spaces, capitals that are preserved, or a reserved word, quote the alias with double quotes, the SQL standard identifier quote:

SQL Quoted aliases Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The headings now read Product name, Price (EUR) and order. order is a reserved word (think ORDER BY), so it only works as an alias because it's quoted.

Dialects add their own identifier quotes:

Database Standard "..." Other identifier quotes
SQLite Yes `...` and [...] also accepted
PostgreSQL Yes β€”
MySQL / MariaDB Only with ANSI_QUOTES mode `...` (backticks)
SQL Server Yes [...] (square brackets)
Oracle Yes β€”

Don't put aliases in single quotes. SELECT 1 AS 'total' happens to work in SQLite, MySQL and SQL Server, but single quotes mean a string in standard SQL, and PostgreSQL rejects it. Use double quotes, or better, a plain snake_case name that needs no quotes.

SQL Server also accepts a reversed form, SELECT stock_value = price * stock FROM products, which you'll see in older T-SQL code. It isn't standard; AS works there too.

Table aliases

You can alias tables in FROM as well. Then you refer to columns as alias.column, which is shorter than table_name.column and makes it obvious where each column comes from. Table aliases shine as soon as a query reads from more than one table. Here products are listed with the name of their category, which lives in another table (joins are explained in Why Joins?):

SQL Table aliases in a join Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both tables have a column called name, so p.name and c.name are needed to tell them apart, and the column aliases product and category keep the two apart in the result too. Short aliases, often the first letter of the table, are the norm. For longer queries, pick aliases that still read well, such as oi for order_items.

Once you've given a table an alias, use it consistently. The original name is no longer visible in that query:

SQL The alias replaces the table name Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

This query fails with no such column: products.price. Change products.price to p.price and it runs.

When a table alias is required

Table aliases are optional most of the time, but two situations need them:

  1. A table that appears twice. When you join a table to itself, for example employees to their managers, both copies need different names. You'll do this in Self Joins:

    SQL
    SELECT e.first_name AS employee, m.first_name AS manager
    FROM employees AS e
    JOIN employees AS m ON m.id = e.manager_id;
    
  2. A subquery in FROM (a derived table). PostgreSQL (before version 16), MySQL and SQL Server require it to have an alias; SQLite doesn't, but adding one is good practice:

SQL Aliasing a derived table Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner query summarizes employees per department, the alias stats names that temporary result, and the outer query joins it to departments. Engineering is the largest department, with 10 people. Subqueries covers derived tables properly.

Oracle is the odd one out for table aliases: it accepts FROM products p but not FROM products AS p. Leaving out AS before table aliases works in every database, so you'll see both styles.

Where can you use a column alias?

This is where aliases get tricky. You'd expect a name defined in the SELECT list to be usable everywhere, but the clauses of a query are evaluated in a logical order that differs from the order you write them:

Written order Logical order
1. SELECT 1. FROM (and joins)
2. FROM 2. WHERE
3. WHERE 3. GROUP BY
4. GROUP BY 4. HAVING
5. HAVING 5. SELECT (aliases are created here)
6. ORDER BY 6. ORDER BY
7. LIMIT 7. LIMIT / OFFSET

Aliases are created in step 5, so by the standard they exist only in ORDER BY. WHERE runs before the SELECT list is computed, so in standard SQL it can't see stock_value. Databases bend this rule to different degrees:

Alias used in… Standard SQLite PostgreSQL MySQL SQL Server
WHERE No Yes No No No
GROUP BY No Yes Yes Yes No
HAVING No Yes No Yes No
ORDER BY Yes Yes Yes Yes Yes
Another column in the same SELECT No No No No No

SQLite is the most permissive, so the first query below works here even though it would fail in PostgreSQL or SQL Server. The second query fails everywhere, SQLite included:

SQL Aliases in WHERE and in the SELECT list Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns the Ultralight Tent and the Mechanical Keyboard. The second stops with no such column: doubled.

To write portable SQL, repeat the expression in WHERE, or compute it once in a subquery or CTE and filter outside, where the alias has become an ordinary column:

SQL
-- Portable: repeat the expression
SELECT name, price * stock AS stock_value
FROM products
WHERE price * stock > 6000;

-- Portable: name it in a CTE, then filter
WITH valued AS (
  SELECT name, price * stock AS stock_value
  FROM products
)
SELECT name, stock_value
FROM valued
WHERE stock_value > 6000;

CTEs (the WITH clause) get their own lesson in Common Table Expressions.

An alias with the same name as a column

What if an alias has the same name as a real column? In WHERE, every database (including SQLite) uses the real column:

SQL Alias shadowing a column Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The WHERE filters on the original price (more than 100), so you get four products, but the result column called price shows the doubled values, from 378 down. That's confusing to read. Avoid giving an alias the name of an existing column unless it truly holds the same thing.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. What are the result column names of SELECT name product, price FROM products;?

    AS is optional for column aliases, so name product renames name to product.
  2. 2. Which alias is written in the portable, standard way?

    Double quotes are the standard quote for identifiers. Single quotes mean strings; brackets and backticks are dialect-specific.
  3. 3. In PostgreSQL, why does SELECT price * stock AS v FROM products WHERE v > 100 fail?

    Logically, FROM and WHERE run before SELECT, so standard SQL only lets ORDER BY see column aliases. SQLite happens to allow it.
  4. 4. After FROM products AS p, which reference works?

    The alias replaces the table name inside the query, so only p.price (or plain price) works.
  5. 5. When is a table alias required?

    Two copies of the same table need different names. Many databases also require aliases for subqueries in FROM.

Summary

  • expression AS name renames a result column; always alias calculated columns.
  • AS is optional but recommended: without it, a missing comma silently creates an alias.
  • Quote aliases that contain spaces or reserved words with double quotes, not single quotes.
  • Table aliases (FROM products AS p) shorten column references, are required for self joins and derived tables, and replace the table's name in that query. Oracle doesn't accept AS before a table alias.
  • Logically, SELECT runs after FROM, WHERE, GROUP BY and HAVING, so standard SQL allows column aliases only in ORDER BY. SQLite also accepts them in WHERE, GROUP BY and HAVING.
  • For portable code, repeat the expression or compute it in a subquery or CTE.

Next: SQL Operators, a complete tour of the arithmetic, comparison, logical and string operators.