Aliases

An alias is a temporary name for a column or a table, which lasts only for that query. You've met column aliases already; here's the full picture.

Column aliases

SELECT
  name                    AS product,
  price                   AS unit_price,
  ROUND(price * stock, 2) AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 5;

Aliases make calculated columns readable, and ORDER BY can use them because it runs after SELECT.

Aliases with spaces

To use spaces or exact capitals, put the alias in double quotes:

SELECT name AS "Product name", price AS "Unit price"
FROM products
WHERE price > 100;

AS is optional

SELECT name product FROM products works too, but it's easy to misread as a missing comma. Writing AS makes the intent obvious.

Table aliases

Long table names get short aliases, which makes joins far easier to read:

SELECT o.order_id, c.name, o.status
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.country = 'India';

Once a table has an alias, use the alias everywhere in that query.

When an alias is required

Joining a table to itself is impossible without aliases, because they're how you tell the two copies apart. This pairs each employee with their manager:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees AS e
JOIN employees AS m ON m.employee_id = e.manager_id;

The self joins lesson explores this. A query used in FROM (a derived table) needs an alias too:

SELECT category, ROUND(avg_price, 2) AS avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS category_prices
WHERE avg_price > 100;

Where a column alias can't go

WHERE runs before SELECT, so in standard SQL it can't see column aliases:

SELECT price * stock AS stock_value
FROM products
WHERE stock_value > 3000;      -- an error in PostgreSQL, MySQL and SQL Server

Repeat the expression instead: WHERE price * stock > 3000. SQLite happens to accept the alias, but code that relies on it won't move to another database.