SELECT structure
The logical evaluation order is: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Counterintuitive: although you write SELECT first, it runs near the end. That's why you can't use a SELECT alias inside the WHERE of the same query.
Explicit vs implicit JOINs
It used to be common to write FROM a, b WHERE a.id = b.a_id. Today the
correct form is FROM a JOIN b ON a.id = b.a_id. Explicit syntax separates
join conditions from filters, prevents accidental cartesian products and makes the join
type clear (INNER, LEFT, RIGHT, FULL).
WHERE vs HAVING
WHERE filters rows before aggregation; HAVING filters groups after. If you don't use
GROUP BY, use WHERE. HAVING COUNT(*) > 10 makes sense; HAVING age
> 18 without GROUP BY is likely a bug.
Subqueries vs JOINs vs CTEs
For data you'll use multiple times, a CTE (WITH x AS (SELECT ...)) is more
readable than repeating a subquery. Modern planners typically optimize them like inline
subqueries. For simple cases, a JOIN is usually faster and clearer than a subquery.
Window functions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and aggregations with OVER allow per-partition
calculations without collapsing rows. For "top 3 products per category", use
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) and filter
where row_number ≤ 3 (in a CTE).
Performance: indexes and EXPLAIN
Before optimizing, run EXPLAIN ANALYZE (Postgres) or EXPLAIN
(MySQL). Look for sequential scans on large tables, sorts without index and nested loops
with many iterations. Rule of thumb: index columns used in JOIN and WHERE; on queries
with ORDER BY + LIMIT, an index on the order column helps.
SELECT * is an anti-pattern
It pulls all columns, transfers bytes you don't use, prevents covering indexes and breaks if someone adds a sensitive column. List the columns you need explicitly. Genfy generates explicit SELECT in every example.
SQL injection
Never concatenate user input into the query. Use parameters ($1, $2 in
Postgres, ? in MySQL) or an ORM that handles them. The queries here are
examples for understanding; in production use prepared statements.