SQL Fundamentals

SQL (Structured Query Language) is the standard language for relational databases. The same core skills — selecting, filtering, joining, and aggregating — carry across PostgreSQL, MySQL, SQLite, and nearly every other relational engine, which makes SQL one of the highest-leverage things a developer can learn.

You can get far with SELECT … WHERE … JOIN, but the real power shows up in aggregation, common table expressions, and window functions, which let the database do heavy lifting that would be slow and clumsy in application code.

TL;DR

• Master the basics: SELECT, INSERT, UPDATE, DELETE with WHERE.
• Combine tables with JOINs; summarize with GROUP BY + aggregates.
• Use CTEs (WITH) and window functions for complex queries.
• **Avoid SELECT *, index filtered/joined columns, and parameterize inputs.

Quick Example

A typical read — filter, sort, and limit, selecting only the columns you need:

Core Concepts

CRUD

Filtering

Aggregation

GROUP BY collapses rows into groups; aggregates summarize them; HAVING filters groups:

Joins

Subqueries

💡 EXISTS is often faster than IN for correlated existence checks.

CTEs (WITH)

Named, composable subqueries that make complex queries readable:

Recursive CTEs

Walk hierarchies (org charts, trees) with UNION ALL:

Window Functions

Aggregate-like calculations without collapsing rows — rankings, running totals, moving averages:

Indexes, Transactions & Upserts

Best Practices

• Select only the columns you need — SELECT * wastes I/O and breaks on schema changes.
• Index columns used in WHERE, JOIN, and ORDER BY.
• Parameterize every value — never concatenate input into SQL (SQL Injection).
• LIMIT large result sets; read EXPLAIN ANALYZE to find slow plans; prefer keyset over deep OFFSET pagination.

Common Mistakes

Comparing to NULL with =

The N+1 query pattern

FAQ

What's the difference between INNER and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, filling NULLs where the right table has no match — useful for "users and their orders, including users with none."

What is a CTE and why use one?

A Common Table Expression (WITH … AS (…)) is a named, temporary result you can reference in the main query. CTEs make complex queries readable, avoid repeating subqueries, and (when recursive) handle hierarchical data.

When should I use a window function?

When you need an aggregate and the individual rows — running totals, rankings within groups, moving averages, or "top N per category." Unlike GROUP BY, window functions compute across a set of rows without collapsing them.

Why avoid SELECT *?

It fetches columns you don't need (more I/O and network), can break when the schema changes, and prevents some index-only optimizations. Listing explicit columns is faster and more robust.

WHERE or HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation. Use WHERE for row conditions and HAVING only for conditions on aggregates (e.g. HAVING COUNT(*) > 100).

Related Topics

• PostgreSQL — A feature-rich SQL engine
• MySQL — Another popular SQL database
• Database Indexing — Making queries fast
• Query Optimization — Reading plans, fixing slow queries
• SQL Injection — Parameterizing safely

References

• PostgreSQL — SQL documentation
• SQL Style Guide
• Use The Index, Luke!
• Mode — SQL Tutorial