Skip to content
Codeloom

Topics / SQL

SQL

The language of data. Queries, joins, indexes, and patterns.

Browse 47 tutorials → Other topics

Why learn SQL?

  • Almost every backend touches a relational database.

  • A skill that has stayed valuable for 50+ years and still will.

  • Declarative — describe what you want, not how to compute it.

  • Transferable across Postgres, MySQL, SQLite, and dozens of dialects.

What you can build with SQL

Querying production data for product, analytics, and ops Designing schemas for application backends Reporting and dashboards Data engineering pipelines

SQL tutorials

47 articles · page 1 of 3

Hand-written tutorials, ordered as a recommended learning path.

  1. 01 What Is SQL? A clear, no-hype introduction to SQL — what relational databases are, why declarative querying matters, the main dialects, and when SQL beats spreadsheets or NoSQL.
  2. 02 SELECT Basics A practical introduction to SELECT, FROM, WHERE, ORDER BY, LIMIT, and DISTINCT — with a running users and orders dataset so every query has real meaning.
  3. 03 CREATE & INSERT Build your first real schema — CREATE TABLE with the right column types, PRIMARY KEY, NOT NULL, DEFAULT, then load it with single and multi-row INSERT, and learn when to reach for ALTER or DROP.
  4. 04 JOINs A practical tour of INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins — when to reach for each, how ON differs from USING, and the Cartesian-product mistake that catches everyone once.
  5. 05 Subqueries & CTEs Compose queries from smaller queries — scalar, row, and table subqueries, IN/EXISTS/ANY, correlated subqueries, WITH clauses, and a peek at recursive CTEs. When to prefer a CTE for readability.
  6. 06 Indexes & Performance How B-tree indexes work, when CREATE INDEX helps, when it hurts, what EXPLAIN tells you, and the concept of selectivity that decides whether an index is worth its cost.
  7. 07 Postgres vs MySQL vs SQLite Where each database shines, where each frustrates, the dialect differences that bite when you port code (LIMIT vs TOP, autoincrement, JSON, full-text), and the hosting concerns that decide for you.
  8. 08 MongoDB vs PostgreSQL Compare MongoDB and PostgreSQL across data modeling, performance, scalability, and use cases. Choose the right database for your project.
  9. 09 PostgreSQL vs MySQL vs SQLite Compare PostgreSQL, MySQL, and SQLite on features, performance, scalability, and use cases. Find the best relational database for your project in 2026.
  10. 10 Bulk Insert & Upsert Master efficient bulk data loading with multi-row INSERT, COPY, ON CONFLICT upserts, and batch strategies that avoid locking and performance traps.
  11. 11 Cursor Pagination Replace slow OFFSET pagination with cursor-based (keyset) pagination. Covers implementation, indexing, bidirectional cursors, and encoding strategies.
  12. 12 Full-Text Search Move beyond LIKE queries with SQL full-text search. Covers tsvector, tsquery, ranking, indexes, and when to choose Postgres FTS over external search engines.
  13. 13 Temporary Tables Learn when temporary tables improve query performance and readability. Covers session-scoped temps, CTEs, unlogged tables, and cleanup strategies.
  14. 14 Triggers Learn how SQL triggers work, when to use them, and when to avoid them. Covers BEFORE/AFTER triggers, audit logging, and common pitfalls.
  15. 15 Deadlock Prevention Learn how SQL deadlocks occur, how databases detect them, and practical strategies to prevent deadlocks in your applications.
  16. 16 Indexing Strategies A practical guide to SQL index types -- B-tree, hash, partial, and composite -- and when to use each for maximum query performance.
  17. 17 JSON in Postgres Learn how to store, query, and manipulate JSON and JSONB data in PostgreSQL with practical examples and indexing strategies.
  18. 18 LATERAL Joins Learn how LATERAL joins work in SQL, how they replace correlated subqueries, and when to use them for top-N-per-group patterns.
  19. 19 Materialized Views Learn how to create, refresh, and index materialized views in PostgreSQL to dramatically speed up expensive queries.
  20. 20 PIVOT & UNPIVOT Learn how to pivot rows into columns and unpivot columns into rows using CASE, CROSSTAB, PIVOT, and UNPIVOT in SQL.