Expressions

Expressions are computations that produce a value. They can appear in SELECT lists, WHERE clauses, ORDER BY, and anywhere a value is expected.

Arithmetic Operators

-- Computed columns
SELECT
  name,
  salary,
  salary * 12 AS annual_salary,
  salary * 0.30 AS tax,
  salary - (salary * 0.30) AS net_salary
FROM employees;

-- Arithmetic in WHERE
SELECT * FROM products
WHERE (price * quantity) > 1000;

String Concatenation

Use the || operator to concatenate strings.

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

SELECT 'Order #' || CAST(id AS VARCHAR) AS order_label
FROM orders;

-- Concatenation with NULL yields NULL
SELECT 'Hello' || NULL;  -- NULL

CASE WHEN ... THEN ... ELSE ... END

Conditional expressions that return different values based on conditions.

Searched CASE

SELECT
  name,
  salary,
  CASE
    WHEN salary >= 120000 THEN 'Senior'
    WHEN salary >= 80000  THEN 'Mid-level'
    WHEN salary >= 50000  THEN 'Junior'
    ELSE 'Entry'
  END AS level
FROM employees;

Simple CASE

SELECT
  name,
  department,
  CASE department
    WHEN 'Engineering' THEN 'Tech'
    WHEN 'Research'    THEN 'Tech'
    WHEN 'Sales'       THEN 'Business'
    WHEN 'Marketing'   THEN 'Business'
    ELSE 'Other'
  END AS division
FROM employees;

CASE in Aggregation

-- Conditional counting
SELECT
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
  COUNT(CASE WHEN salary <= 100000 THEN 1 END) AS standard_earners
FROM employees;

-- Conditional sum
SELECT
  department,
  SUM(CASE WHEN active THEN salary ELSE 0 END) AS active_payroll
FROM employees
GROUP BY department;

CAST

Explicit type conversion.

CAST(expression AS target_type)

-- Numeric to string
SELECT 'Total: ' || CAST(SUM(amount) AS VARCHAR) FROM orders;

-- String to numeric
SELECT CAST('42' AS INTEGER) + 8;   -- 50

-- String to date
SELECT CAST('2024-06-15' AS DATE);

-- Integer to real for precise division
SELECT CAST(count AS REAL) / total AS ratio FROM stats;

NULL Handling

NULL represents a missing or unknown value. Most operations involving NULL produce NULL.

-- Arithmetic with NULL
SELECT 10 + NULL;       -- NULL
SELECT 10 * NULL;       -- NULL

-- Comparison with NULL
SELECT NULL = NULL;     -- NULL (not TRUE!)
SELECT NULL != NULL;    -- NULL (not TRUE!)
SELECT 1 > NULL;        -- NULL

-- Use IS NULL / IS NOT NULL for null checks
SELECT * FROM employees WHERE manager_id IS NULL;

-- COALESCE: returns first non-NULL argument
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

-- NULLIF: returns NULL if two values are equal
SELECT NULLIF(denominator, 0);  -- returns NULL instead of 0
SELECT numerator / NULLIF(denominator, 0) AS safe_ratio;

Operator Precedence

From highest to lowest precedence:

Use parentheses to override the default precedence:

-- Without parentheses: AND binds tighter than OR
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Research'
  AND salary > 100000;
-- Means: Engineering (any salary) OR (Research AND salary > 100k)

-- With parentheses: explicit grouping
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Research')
  AND salary > 100000;
-- Means: (Engineering OR Research) AND salary > 100k