Expressions, Calculated Columns and DISTINCT

Concept

The select list is not limited to stored columns. Anything that produces a value can go there: arithmetic, string building, function calls, conditional logic. Columns built this way are called calculated or derived columns, and they exist only in the result.

Syntax

SELECT expression AS alias
FROM   table_name;

SELECT DISTINCT column_list
FROM   table_name;

Example

SELECT first_name,
       salary,
       salary * 12                      AS annual_salary,
       ROUND(salary * 0.10, 2)          AS monthly_bonus,
       salary + ROUND(salary * 0.10, 2) AS total_package
FROM   employees
ORDER BY total_package DESC;

-- Building a string (MySQL / MariaDB)
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       UPPER(status)                      AS status_code
FROM   employees;

-- The standard operator, used by PostgreSQL, Oracle and SQLite
SELECT first_name || ' ' || last_name AS full_name FROM employees;

Explanation

Each expression is evaluated once per row. total_package repeats the bonus expression rather than referring to the monthly_bonus alias, because a select list alias is not available to another item in the same select list. If you need to reuse it, either repeat the expression or wrap the query in a CTE or subquery:

WITH paid AS (
    SELECT first_name,
           salary,
           ROUND(salary * 0.10, 2) AS monthly_bonus
    FROM   employees
)
SELECT first_name, salary, monthly_bonus, salary + monthly_bonus AS total_package
FROM   paid;

DISTINCT

-- Distinct values of one column
SELECT DISTINCT status FROM employees;

-- Distinct COMBINATIONS, not distinct per column
SELECT DISTINCT dept_id, status FROM employees;

-- How many different departments are staffed?
SELECT COUNT(DISTINCT dept_id) AS staffed_departments FROM employees;

DISTINCT applies to the whole row of the result, never to a single column in a longer list. SELECT DISTINCT dept_id, status returns every unique pair. This is the single most misunderstood keyword in beginner SQL.

Important rules

• Any arithmetic involving NULL produces NULL. Guard it with COALESCE(bonus, 0).
• DISTINCT treats all NULLs as equal to each other, so it returns at most one NULL - even though NULL = NULL is unknown.
• DISTINCT usually forces a sort or a hash, so it is not free on a large result.
• Integer division may truncate: salary / 12 can lose the fraction in some dialects. Multiply by 1.0 or cast.

Common mistakes

• Believing DISTINCT can be applied to just one column of several. SELECT DISTINCT(dept_id), status is the same as SELECT DISTINCT dept_id, status; the parentheses are decoration.
• Using DISTINCT to paper over a join that produces duplicates. Fix the join instead - see the note on duplicate rows caused by joins.
• Referring to a select list alias from another select list item.
• Forgetting that CONCAT in some dialects returns NULL if any argument is NULL.

Best practices

• Alias every calculated column with the name the consumer should see.
• Round money explicitly rather than trusting display formatting.
• Reach for a CTE when the same expression is needed twice; it is clearer than repeating it.
• Before adding DISTINCT, ask why duplicates appeared. Usually the answer is a join, and the join is the bug.

Practice

1. Return each employee's name, salary and salary as a percentage of 200000, rounded to one decimal place.
2. List every distinct combination of country and city from customers.
3. Explain the difference between COUNT(dept_id) and COUNT(DISTINCT dept_id) on the sample data.