Expressions, Literals, and Computed Columns

Learning outcomes

A select-list item can be more than a stored column. SQL expressions combine columns, literals, operators, and functions to derive values at query time. These values are returned in the result but are not written back to the table.

Expressions produce one value per result row

SELECT    s.sale_id,    s.quantity,    p.unit_price,    s.quantity * p.unit_price AS gross_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_id;

For each matched sale row, the DBMS evaluates s.quantity * p.unit_price. The alias gross_amount labels that output expression.

Literal values

A literal represents a value written directly in the statement.

SELECT    42                  AS integer_value,    3.14                AS real_value,    'SQL'               AS text_value,    NULL                AS missing_value,    '2026-08-05'        AS date_text,    X'53514C'           AS blob_value;

Single quotes and identifiers are different

Single quotes delimit string literals. Identifier quoting differs by dialect; standard SQL uses double quotes for delimited identifiers. Avoid creating identifiers that require quoting unless there is a strong reason.

SELECT    'product_name' AS literal_text,    product_name   AS column_valueFROM product;

The first expression returns the same text for every row. The second reads the column value.

Arithmetic expressions

SELECT    s.sale_id,    s.quantity,    p.unit_price,    s.discount_rate,    s.quantity * p.unit_price AS gross_amount,    s.quantity * p.unit_price * (1 - s.discount_rate)        AS net_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_id;

Parentheses communicate grouping and protect the intended evaluation order. Chapter 5 studies operator precedence more directly.

Integer and real arithmetic

Type behavior matters. In SQLite, the values participating in an operation influence the result.

SELECT    5 / 2       AS integer_division,    5.0 / 2     AS real_division,    CAST(5 AS REAL) / 2 AS cast_division;

In SQLite, 5 / 2 produces integer division because both operands are integers, while introducing a real operand yields a real result. Other engines have their own numeric type rules, so use deliberate types and test boundary cases.

Text concatenation

SQLite and PostgreSQL use || for concatenation. SQL Server commonly uses +, while MySQL commonly uses CONCAT() unless configured otherwise.

SELECT    p.sku,    p.sku || ' — ' || p.product_name AS product_labelFROM product AS p;

Computed display labels are useful, but avoid mixing presentation concerns into every query if the application already has a consistent formatting layer.

NULL propagates through many expressions

SELECT    c.customer_id,    c.full_name,    c.city,    c.city || ', customer' AS city_labelFROM customer AS c;

For Lina Chen, city is NULL, so concatenation also returns NULL. Chapter 6 introduces COALESCE and related tools for deliberate fallback behavior.

Aliases for computed columns

Without an alias, clients may receive an engine-generated expression label such as quantity * unit_price. That label is awkward and unstable.

SELECT    s.sale_id AS sale_id,    s.quantity * p.unit_price AS gross_amount,    s.quantity * p.unit_price * (1 - s.discount_rate)        AS net_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_id;

Choose names that describe the value and, when needed, its unit or basis: net_amount, duration_seconds, price_usd, or temperature_celsius.

Lab: produce a sale-line report

SELECT    s.sale_id,    s.sold_at,    p.sku,    p.product_name,    s.quantity,    p.unit_price,    s.discount_rate * 100 AS discount_percent,    s.quantity * p.unit_price AS gross_amount,    s.quantity * p.unit_price * (1 - s.discount_rate)        AS net_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_id;

Verify the calculations manually for at least one row. Query output is not trustworthy merely because the SQL executed successfully.

Type inspection in SQLite

SELECT    typeof(42)                  AS integer_type,    typeof(3.14)                AS real_type,    typeof('SQL')               AS text_type,    typeof(NULL)                AS null_type,    typeof(X'53514C')           AS blob_type,    typeof(5 / 2)               AS division_type,    typeof(CAST(5 AS REAL) / 2) AS cast_division_type;

typeof() is SQLite-specific and useful for learning. Portable schemas and applications should rely on documented column and expression types rather than engine-specific inspection alone.

Common mistakes

Forgetting an alias

Unnamed computed columns create unclear client contracts.

Assuming arithmetic types

Integer division, decimal precision, overflow, and implicit conversion differ across engines.

Using double quotes for text

Double quotes are for delimited identifiers in standard SQL. Use single quotes for string literals.

Ignoring NULL

A correct formula over known values may return NULL when any required input is missing.

Embedding units only in prose

When ambiguity is possible, encode the unit in the alias or data model.

Summary and references

• Select-list expressions are evaluated for result rows.
• Literals represent values written directly in SQL.
• Aliases give computed columns stable names.
• Numeric result types depend on operand types and engine rules.
• NULL commonly propagates through arithmetic and concatenation.

References

• SQLite: SQL Language Expressions
• SQLite: Datatypes
• PostgreSQL: Value Expressions