SQL Queries

SELECT and Filtering

SELECT specifies columns (or * for all), FROM names the table, and WHERE filters rows before aggregation. Use AND, OR, NOT, IN, BETWEEN, and LIKE for conditions.

Prefer explicit column lists over SELECT * in application code to reduce network payload and avoid breakage when columns are added.

SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

Join Types

INNER JOIN returns rows with matches in both tables. LEFT JOIN keeps all rows from the left table with NULLs for non-matching right side. RIGHT JOIN and FULL OUTER JOIN (via UNION) are less common but useful in reporting.

Join on indexed foreign key columns for performance. Avoid Cartesian products from missing ON clauses—they explode row counts silently.

• Use table aliases for readability in multi-join queries
• Filter early in WHERE rather than HAVING when possible
• Consider denormalization only after measuring join cost

SELECT p.name, c.name AS category
FROM products p
LEFT JOIN categories c ON c.id = p.category_id;

GROUP BY and ORDER BY

GROUP BY collapses rows sharing key values; aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute summaries. HAVING filters grouped results like WHERE filters rows.

Every non-aggregated SELECT column must appear in GROUP BY under ONLY_FULL_GROUP_BY mode, which MySQL enables by default in recent versions.

SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING revenue > 1000
ORDER BY revenue DESC;

Subqueries

Subqueries nest SELECT inside WHERE, FROM, or SELECT clauses. Correlated subqueries reference outer rows and can be slow on large datasets—rewrite as JOINs when EXPLAIN shows repeated execution.

EXISTS and NOT EXISTS often outperform IN for large subquery results because they short-circuit on first match.

SELECT id, email FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);

Query Patterns for Applications

Paginate with keyset (seek) pagination on indexed columns instead of OFFSET for large tables—OFFSET scans skipped rows. Use LIMIT with ORDER BY on an indexed unique or compound key.

Batch IN clauses or use temporary tables for large ID lists. Cache read-heavy aggregates in materialized summary tables updated by jobs or triggers.

-- Keyset pagination: next page after id 1000
SELECT * FROM products
WHERE id > 1000
ORDER BY id ASC
LIMIT 50;