sql

SQL

Overview

SQL patterns for querying, data manipulation, and database design.

Query Fundamentals

Basic Queries

-- SELECT with filtering
SELECT
    id,
    email,
    name,
    created_at
FROM users
WHERE active = true
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- Multiple conditions
SELECT *
FROM orders
WHERE status IN ('pending', 'processing')
  AND total_amount > 100
  AND (priority = 'high' OR customer_type = 'premium');

-- LIKE and pattern matching
SELECT *
FROM products
WHERE name LIKE '%widget%'          -- Contains 'widget'
   OR name LIKE 'Premium%'          -- Starts with 'Premium'
   OR sku SIMILAR TO '[A-Z]{3}-[0-9]{4}';  -- Regex pattern (PostgreSQL)

-- NULL handling
SELECT
    id,
    COALESCE(nickname, name, 'Anonymous') AS display_name,
    NULLIF(discount, 0) AS discount_or_null
FROM users
WHERE deleted_at IS NULL;

-- CASE expressions
SELECT
    id,
    name,
    CASE
        WHEN total >= 1000 THEN 'Gold'
        WHEN total >= 500 THEN 'Silver'
        WHEN total >= 100 THEN 'Bronze'
        ELSE 'Standard'
    END AS tier,
    CASE status
        WHEN 'active' THEN 1
        WHEN 'pending' THEN 2
        ELSE 3
    END AS sort_order
FROM customers
ORDER BY sort_order;

-- Distinct and counting
SELECT DISTINCT category
FROM products;

SELECT
    category,
    COUNT(*) AS product_count,
    COUNT(DISTINCT brand) AS brand_count
FROM products
GROUP BY category;

Joins

-- INNER JOIN (only matching rows)
SELECT
    o.id AS order_id,
    o.total_amount,
    u.name AS customer_name,
    u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

-- LEFT JOIN (all from left, matching from right)
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple joins
SELECT
    o.id AS order_id,
    u.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Self join
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Cross join (cartesian product)
SELECT
    p.name AS product,
    c.name AS color
FROM products p
CROSS JOIN colors c;