Advanced SELECT & Filtering

Mastering the SELECT statement goes far beyond SELECT * FROM table. This lesson covers complex WHERE clauses, CASE expressions, null-handling functions, pattern matching, and DISTINCT ON — the building blocks for writing expressive, efficient queries.

---

Complex WHERE Clauses

Real-world filtering rarely involves a single condition. Combine predicates with AND, OR, NOT, and parentheses to express precise logic.

-- Find active customers who placed an order in the last 30 days
-- OR are flagged as VIP regardless of order history
SELECT id, name, email, is_vip
FROM customers
WHERE (
    status = 'active'
    AND last_order_date >= CURRENT_DATE - INTERVAL '30 days'
  )
  OR is_vip = true;

Operator Precedence

SQL evaluates NOT first, then AND, then OR. Always use parentheses to make intent explicit:

-- Without parentheses: AND binds tighter than OR
-- This means: (status = 'active' AND region = 'EU') OR country = 'GB'
SELECT * FROM customers
WHERE status = 'active' AND region = 'EU' OR country = 'GB';

-- Explicit intent: active customers in EU or GB
SELECT * FROM customers
WHERE status = 'active' AND (region = 'EU' OR country = 'GB');

IN, BETWEEN, and ANY

-- IN: match against a list
SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Toys');

-- BETWEEN: inclusive range
SELECT * FROM orders WHERE total BETWEEN 100.00 AND 500.00;

-- ANY with an array
SELECT * FROM products WHERE id = ANY(ARRAY[1, 5, 12, 33]);

---

CASE Expressions

CASE is SQL's if/else. It can appear in SELECT, WHERE, ORDER BY, and even inside aggregate functions.

Simple CASE

SELECT
  order_id,
  total,
  CASE status
    WHEN 'pending'   THEN 'Awaiting Payment'
    WHEN 'shipped'   THEN 'On Its Way'
    WHEN 'delivered'  THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Searched CASE

SELECT
  product_id,
  name,
  price,
  CASE
    WHEN price < 10       THEN 'Budget'
    WHEN price < 50       THEN 'Mid-Range'
    WHEN price < 200      THEN 'Premium'
    ELSE                       'Luxury'
  END AS price_tier
FROM products;

CASE in ORDER BY

-- Sort so that 'urgent' priority appears first
SELECT * FROM tickets
ORDER BY
  CASE priority
    WHEN 'urgent' THEN 1
    WHEN 'high'   THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low'    THEN 4
  END;

---

Null Handling: COALESCE and NULLIF

COALESCE

Returns the first non-null argument. Essential for providing defaults:

-- Use the nickname if available, otherwise the full name
SELECT
  COALESCE(nickname, first_name || ' ' || last_name) AS display_name
FROM users;

-- Chain multiple fallbacks
SELECT
  COALESCE(mobile_phone, home_phone, work_phone, 'No phone on file') AS contact_phone
FROM contacts;

NULLIF

Returns NULL if the two arguments are equal; otherwise returns the first argument. Useful to avoid division-by-zero errors:

-- Safe division: returns NULL instead of error when quantity is 0
SELECT
  order_id,
  total,
  quantity,
  total / NULLIF(quantity, 0) AS unit_price
FROM order_items;

---

Pattern Matching

PostgreSQL offers multiple ways to match text patterns.

LIKE and ILIKE

-- % matches any sequence of characters, _ matches exactly one
SELECT * FROM products WHERE name LIKE 'Pro%';       -- starts with 'Pro'
SELECT * FROM products WHERE sku LIKE 'AB-___-2024';  -- AB, 3 chars, -2024

-- ILIKE: case-insensitive (PostgreSQL-specific)
SELECT * FROM users WHERE email ILIKE '%@gmail.com';

SIMILAR TO

Provides SQL-standard regex-like patterns (a hybrid of LIKE and regex):

-- Match UK postcodes like 'SW1A 1AA' or 'EC2M 4YE'
SELECT * FROM addresses
WHERE postcode SIMILAR TO '[A-Z]{1,2}[0-9][0-9A-Z]? [0-9][A-Z]{2}';

POSIX Regular Expressions

The most powerful pattern matching. Use ~ (case-sensitive) or ~* (case-insensitive):

-- Match emails from any subdomain of example.com
SELECT * FROM users
WHERE email ~ '^[a-zA-Z0-9._%+-]+@([a-z]+\.)*example\.com$';

-- Case-insensitive match for phone numbers
SELECT * FROM contacts
WHERE phone ~* '^\+?[0-9]{10,15}$';

Operator	Description
~	Matches regex (case-sensitive)
~*	Matches regex (case-insensitive)
!~	Does not match regex
!~*	Does not match regex (case-insensitive)

---

DISTINCT ON

A powerful PostgreSQL extension. While standard DISTINCT removes complete duplicate rows, DISTINCT ON keeps only the first row for each unique value of the specified expression(s).

-- Get the most recent order for each customer
SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total
FROM orders
ORDER BY customer_id, order_date DESC;

Important: The DISTINCT ON columns must match the leftmost columns in the ORDER BY clause. The remaining ORDER BY columns determine which row is kept (the first one).

DISTINCT ON vs GROUP BY

-- DISTINCT ON: returns the full row of the latest order per customer
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;

-- GROUP BY: only gives aggregated values, not full rows
SELECT customer_id, MAX(order_date) AS latest_order
FROM orders
GROUP BY customer_id;

---

Combining Techniques

-- Find the most recent high-value order per customer,
-- showing a human-readable status and safe unit price
SELECT DISTINCT ON (o.customer_id)
  o.customer_id,
  c.name AS customer_name,
  o.order_id,
  o.total,
  o.total / NULLIF(o.item_count, 0) AS avg_item_price,
  COALESCE(o.notes, 'No notes') AS notes,
  CASE
    WHEN o.total >= 1000 THEN 'High Value'
    WHEN o.total >= 100  THEN 'Standard'
    ELSE                      'Small'
  END AS order_tier
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status IN ('delivered', 'shipped')
  AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY o.customer_id, o.total DESC;

---

Summary

• Use parentheses to make complex WHERE logic unambiguous.
• CASE expressions add conditional logic anywhere in a query.
• COALESCE provides fallback values for NULLs; NULLIF turns specific values into NULL.
• PostgreSQL offers LIKE, SIMILAR TO, and POSIX regex (~) for pattern matching at increasing power levels.
• DISTINCT ON is a PostgreSQL-specific tool for selecting "the first row per group" — simpler and often faster than window functions for this use case.