DISTINCT, Aliases & Expressions

AS gives your result columns readable names, and DISTINCT removes duplicate result rows. This lesson cleans up the shape of a SELECT query so the grid says what it means.

You already know how to choose columns, filter rows, and sort rows. Now you will make those answers easier to read, reuse, and trust.

Naming columns with AS

An alias is a temporary name for something in the query result. It is a name tag on the grid, not a change to the table.

In 2.1, an expression like price * 0.9 came back as ?column?, PostgreSQL's no-name shrug. AS fixes that:

SELECT name AS item_name,
       category AS menu_section,
       price * 0.9 AS sale_price
FROM menu_items
WHERE category = 'coffee'
ORDER BY sale_price DESC, item_name ASC;

 item_name  | menu_section | sale_price
------------+--------------+------------
 Cold Brew  | coffee       |      4.050
 Flat White | coffee       |      3.600
 Cappuccino | coffee       |      3.420
 Espresso   | coffee       |      2.250
(4 rows)

The table still has columns named name, category, and price. The result grid has friendlier headers: item_name, menu_section, and sale_price.

Keep aliases boring and code-shaped: snake_case, no spaces, no punctuation. PostgreSQL can handle quoted aliases like "Sale Price", but that style spreads double quotes through every later reference. A clean alias is easier to type and harder to break.

Naming tables with AS

A table alias is the same idea, but for a table name inside a query. menu_items AS m says "inside this query, call menu_items m."

That looks unnecessary with one table, but it becomes essential in joins. Lesson 5.2 assumes this habit, so practice it now:

SELECT m.name AS item_name,
       m.category AS menu_section,
       m.price
FROM menu_items AS m
WHERE m.in_stock = true
ORDER BY m.category ASC, m.name ASC;

    item_name    | menu_section | price
-----------------+--------------+-------
 Cappuccino      | coffee       |  3.80
 Espresso        | coffee       |  2.50
 Flat White      | coffee       |  4.00
 Sparkling Water | drinks       |  2.20
 Banana Bread    | pastry       |  3.60
 Cinnamon Roll   | pastry       |  4.10
 Croissant       | pastry       |  3.20
 Earl Grey       | tea          |  3.00
(8 rows)

Once a table has an alias, use it consistently: m.name, m.category, m.price. That dot means "the name column that belongs to m." When queries later pull from two tables, the dot keeps your columns unambiguous.

Where aliases are visible

Column aliases are visible to ORDER BY, which is why the first query could sort by sale_price. But in PostgreSQL, a SELECT alias is not visible to WHERE.

This looks natural, but it fails:

SELECT name,
       price * 0.9 AS sale_price
FROM menu_items
WHERE sale_price < 4.00
ORDER BY sale_price ASC;

ERROR:  column "sale_price" does not exist
LINE 4: WHERE sale_price < 4.00
              ^

Why? WHERE filters raw rows before the select list has named sale_price. ORDER BY runs late enough to see the output name. Section 4.4 gives you the full query pipeline; for now, remember the practical rule: repeat the expression in WHERE, then use the alias in ORDER BY.

SELECT name,
       price * 0.9 AS sale_price
FROM menu_items
WHERE price * 0.9 < 4.00
ORDER BY sale_price ASC, name ASC;

      name       | sale_price
-----------------+------------
 Sparkling Water |      1.980
 Espresso        |      2.250
 Earl Grey       |      2.700
 Croissant       |      2.880
 Sencha          |      3.060
 Banana Bread    |      3.240
 Cappuccino      |      3.420
 Flat White      |      3.600
 Cinnamon Roll   |      3.690
(9 rows)

That repetition is not beautiful, but it is honest SQL at this stage. Later tools give you cleaner ways to name a calculation once and filter it later.

DISTINCT removes duplicate result rows

DISTINCT keeps one copy of each unique result row. The key phrase is result row, which means the values after SELECT, not the original table row.

Start with one selected column. The menu has ten rows, but only four category values:

SELECT DISTINCT category
FROM menu_items
ORDER BY category ASC;

 category
----------
 coffee
 drinks
 pastry
 tea
(4 rows)

Now select two columns. A duplicate means both selected values match:

SELECT DISTINCT category, in_stock
FROM menu_items
ORDER BY category ASC, in_stock DESC;

 category | in_stock
----------+----------
 coffee   | t
 coffee   | f
 drinks   | t
 pastry   | t
 tea      | t
 tea      | f
(6 rows)

coffee appears twice because the selected row is not just coffee; it is (coffee, t) and (coffee, f). pastry appears once because every pastry row has in_stock = true.

DISTINCT is not a broom for messy joins or fuzzy thinking. It answers one precise question: "which unique combinations of these selected values exist?"

Formatting readable SELECT queries

Formatting is not decoration. It is how you make the query easy to scan when it gets one clause longer.

Use the house style you have seen all section:

• Put SQL keywords in uppercase.
• Use snake_case aliases.
• Once a query has more than two clauses, put one clause per line.
• Put one select-list item per line when expressions or aliases need room.
• End every statement with a semicolon.

Here is the whole habit in one small report:

You now have the full Section 2 reading toolkit: choose columns, filter rows, sort and page them, name the output, and collapse duplicates when you mean unique result rows. Next, Section 3 looks at the values inside those columns: data types, missing values, and conditional logic.