Expressions, aliases and DISTINCT

Real queries rarely return raw columns; they compute, rename and de-duplicate.

The select list can contain any expression, not just column names. Arithmetic, string concatenation and function calls are all fine, and each produces a column in the result:

SELECT name, price, price * 1.2 AS price_with_vat FROM products;

AS gives the output column a name (an alias). Without one, PostgreSQL invents a name such as ?column?, which is awkward to read in client code. The AS keyword is optional for column aliases, but writing it avoids confusion when a comma goes missing: SELECT name city silently returns name labelled as city.

Strings are joined with || in PostgreSQL, SQLite and Oracle. MySQL treats || as logical OR by default and uses CONCAT(); SQL Server uses + or CONCAT(). CONCAT() works almost everywhere.

Watch the types in arithmetic. Dividing two integers gives an integer in PostgreSQL, SQL Server and SQLite: 7 / 2 is 3. Make one side numeric (7 / 2.0, or CAST(x AS numeric), or the PostgreSQL shorthand x::numeric) when you want a fraction. MySQL is the odd one out and returns 3.5000.

DISTINCT removes duplicate rows from the result. It applies to the whole row, not to the first column:

SELECT DISTINCT city FROM customers;      -- Yerevan, London, NULL
SELECT DISTINCT city, name FROM customers; -- all 4 rows, each pair is unique

DISTINCT is a sort or hash over the whole result, so it is not free. If you find yourself adding it to hide duplicates from a join, the join is usually wrong.

Example

SELECT name,
       price,
       price * 1.2               AS price_with_vat,
       name || ' (' || category || ')' AS label
FROM products;
--  name     | price  | price_with_vat | label
--  Keyboard |  49.00 |        58.800 | Keyboard (hardware)
--  Mouse    |  19.50 |        23.400 | Mouse (hardware)
--  Monitor  | 189.00 |       226.800 | Monitor (hardware)
--  Ebook    |   9.99 |        11.988 | Ebook (digital)

SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div;
--  int_div | num_div
--        3 |  3.5000000000000000

SELECT DISTINCT category FROM products;
--  hardware
--  digital

Remember

Any expression can appear in the select list; name it with AS
Integer / integer truncates in PostgreSQL; cast to numeric for fractions
DISTINCT de-duplicates whole rows, not a single column

Watch out for

Using DISTINCT to paper over duplicate rows caused by a missing join condition.

Check yourself

What does SELECT DISTINCT city, name FROM customers return for our four customers?
In PostgreSQL, what is SELECT 5 / 2?

Official docs: www.postgresql.org/docs/current/queries-select-lists.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks