Expressions

Examples on this page use this small demo schema:

CREATE TABLE products  (id BIGINT PRIMARY KEY, name TEXT, price DOUBLE PRECISION,
                        category TEXT, discount DOUBLE PRECISION, stock BIGINT);
CREATE TABLE orders    (id BIGINT PRIMARY KEY, customer_id BIGINT, amount DOUBLE PRECISION,
                        status TEXT, order_date DATE, created_at TIMESTAMP);
CREATE TABLE customers (id BIGINT PRIMARY KEY, name TEXT, email TEXT, country TEXT);
CREATE TABLE users     (id BIGINT PRIMARY KEY, name TEXT, username TEXT, email TEXT,
                        age INT, status TEXT, nickname TEXT, first_name TEXT);
CREATE TABLE employees (id BIGINT PRIMARY KEY, name TEXT, department TEXT,
                        salary DOUBLE PRECISION, manager_id BIGINT);
CREATE TABLE students  (id BIGINT PRIMARY KEY, name TEXT, score DOUBLE PRECISION);

CASE

-- Searched CASE
SELECT name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

-- Simple CASE
SELECT name,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'inactive' THEN 'Inactive'
        ELSE 'Unknown'
    END
FROM users;

BETWEEN

SELECT * FROM orders
WHERE amount BETWEEN 100 AND 500;

-- Equivalent to:
SELECT * FROM orders
WHERE amount >= 100 AND amount <= 500;

-- NOT BETWEEN
SELECT * FROM orders
WHERE created_at NOT BETWEEN '2024-01-01' AND '2024-06-30';

LIKE / ILIKE

-- Case-sensitive pattern matching
SELECT * FROM products WHERE name LIKE 'Pro%';         -- starts with
SELECT * FROM products WHERE name LIKE '%widget%';     -- contains
SELECT * FROM products WHERE name LIKE 'Item_';        -- single char wildcard

-- Case-insensitive
SELECT * FROM products WHERE name ILIKE '%widget%';

-- NOT LIKE
SELECT * FROM products WHERE name NOT LIKE 'TEST%';

Wildcards: % matches any sequence of characters, _ matches any single character.

COALESCE

Returns the first non-NULL argument:

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

NULLIF

Returns NULL if the two arguments are equal:

SELECT NULLIF(discount, 0) FROM products;
-- Returns NULL when discount is 0, otherwise returns discount

IN / NOT IN

-- Value list
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- Subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- NOT IN
SELECT * FROM products
WHERE category NOT IN ('deprecated', 'archived');

EXISTS / NOT EXISTS

-- Correlated subquery
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 500
);

SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS/NOT EXISTS subqueries are algebraically unnested to Semi/Anti-Semi joins by the optimizer - no per-row execution.

Subqueries

Scalar Subquery

SELECT name, salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Correlated Scalar Subquery

SELECT p.name, p.price,
    (SELECT AVG(price) FROM products p2 WHERE p2.category = p.category) AS cat_avg
FROM products p;

Correlated subqueries are automatically decorrelated by the optimizer into LEFT JOINs.

CAST

SELECT CAST(price AS INTEGER) FROM products;
SELECT price::INTEGER FROM products;
SELECT CAST('2024-01-15' AS DATE);