SQL Column and Table Aliases: AS and Qualified Names

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

A column alias labels the value produced by one selected expression. SELECT name AS customer_name returns the existing name values under a clearer heading. SELECT amount * 2 AS doubled_amount labels a calculation. Neither changes the stored column definition. Choose a name that states the meaning and, when relevant, the unit, such as revenue_usd or days_open. Avoid labels that imply a count when the expression actually calculates a monetary sum.

A table alias identifies a table reference inside the statement. FROM customers AS c lets you qualify a column as c.name. Qualification is essential when more than one input has an id or name column. A JOIN between customers and sales exposes two ids, so SELECT id can be ambiguous. c.id and s.id state exactly which one you want. Once a table reference has an alias, use that alias consistently for its columns rather than switching between the original name and the alias.

Aliases follow scope rules. A SELECT-list alias is generally available to ORDER BY because ordering operates on the output values. It is generally unavailable in WHERE, which determines input rows before the SELECT expressions are established. Some engines permit aliases in additional clauses, but relying on those extensions makes a query harder to port. Put a calculation in a derived table or CTE when you want the next query level to filter it by its alias.

Use meaningful short aliases rather than random letters in a complicated query. In a self join, employee and manager are clearer roles than a and b. Aliases with spaces require identifier quoting, whose punctuation varies between databases, so customer_name is more portable than Customer Name. An alias can also hide an inconvenient source column name from a report, but it does not protect against schema changes: the original expression must still refer to a real source column. Keep output names stable if application code reads them by name.

AS for column and ordinary table aliases is supported by these four systems. Alias visibility in WHERE, GROUP BY and HAVING varies, so the portable habit is to filter source expressions or use an outer query. Identifier quoting differs; simple underscore-separated aliases avoid that dependency.

Check the engine-specific syntax and version requirements in PostgreSQL select-list aliases.

Return sale 102 with the customer name labelled buyer and the amount labelled total. Use c and s as table aliases.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT c.name AS buyer, s.amount AS total
FROM customers AS c
JOIN sales AS s ON s.customer_id = c.id
WHERE s.id = 102;

buyer | total
Ana   | 30

Sale 102 has customer_id 1, so it joins to Ana. The query exposes two chosen labels and no ambiguous id column. The output names differ from those in the earlier example, while the underlying table definitions and saved values are unchanged.

Rewrite the example with SELECT c.id AS customer_id,s.id AS sale_id and verify that the two identifiers have different meanings. Next attempt to filter sale_amount in the same-level WHERE and check the engine’s behaviour. Replace that reference with s.amount, or move the named expression into a derived table before filtering it. The portable versions should select the same values.