sql-query-fundamentals

SQL Query Fundamentals

Essential SQL patterns for querying relational databases.

Core Principles

1. SELECT only what you need - Avoid SELECT * in production
2. Use explicit JOINs - Never use implicit joins with WHERE
3. Filter early - Apply WHERE before GROUP BY when possible
4. Use CTEs for clarity - Make complex queries readable

Basic SELECT Patterns

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM customers
WHERE status = 'active'
ORDER BY last_name, first_name;

See select-patterns.md for:

• Column aliases and calculated columns
• DISTINCT vs GROUP BY
• LIMIT/OFFSET pagination across databases
• Column selection best practices

JOIN Types

-- INNER JOIN (only matching rows)
SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN + find unmatched rows
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

See join-patterns.md for:

• Join types decision matrix
• Multiple table joins and self joins
• Join conditions vs WHERE filters
• Range joins and CROSS JOINs

WHERE Clause Patterns

WHERE age >= 18 AND age < 65
-- IN clause (better than multiple ORs)
WHERE country IN ('USA', 'Canada', 'Mexico')
-- BETWEEN for ranges
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
-- NULL handling
WHERE COALESCE(mobile_phone, home_phone) IS NOT NULL

See where-clause-patterns.md for:

• Operator precedence and pattern matching
• NULL handling strategies
• Performance considerations (sargable queries)

GROUP BY and Aggregations

SELECT
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(price * quantity) as total_revenue
FROM products
GROUP BY category
HAVING COUNT(*) > 5 AND SUM(price * quantity) > 1000
ORDER BY total_revenue DESC;