Page 11 - SQL

A many-to-many relationship cannot be represented directly in two tables, it needs a third table acting as a mediator. This article shows how junction tables are built, whether a composite or a surrogate primary key makes more sense, and how additional attributes are stored on the relationship itself without endangering referential integrity.

An endpoint loads a list of a hundred rows and triggers a hundred additional single row queries, one per row. The N+1 problem rarely comes from bad SQL, it comes from how application code loads relationships between tables. Once you recognize the pattern in the query log, you can fix the root cause with a JOIN or batch loading instead of optimizing symptoms.

Returning a limited number of rows from a query sounds like a trivial task, yet MySQL, PostgreSQL, SQL Server, and Oracle write this limit with four completely different syntax forms. Anyone who wants to keep pagination code portable across these systems needs an overview of LIMIT, TOP, ROWNUM, and the common ANSI standard denominator OFFSET FETCH.

Database replication distributes data from a primary instance to one or more replica instances, in order to scale read load and increase fault tolerance. Anyone who does not clearly distinguish synchronous from asynchronous replication risks either unnecessary latency or silent data loss during failover.

Every new database connection costs memory, CPU time, and time for the connection setup itself. Connection pooling keeps a limited number of reusable connections open and prevents an application from opening more connections under load than the database can handle. Get pool size wrong and you either produce connection errors or waste capacity.

String aggregation folds multiple rows of a group into a single, comma separated text list, for example every tag of a product in one row instead of several rows. MySQL uses GROUP_CONCAT for this, PostgreSQL and SQL Server use STRING_AGG, Oracle uses LISTAGG. This article shows syntax, ordering, separators and length limits of all four systems side by side.

MVCC lets a database show every transaction a consistent snapshot of the data without reads blocking writes or vice versa. Instead of locking rows, the database maintains several versions of each row at once, PostgreSQL and InnoDB solve this in different ways, with direct consequences for vacuum, purge, and the choice of isolation level.

ETL patterns can largely be expressed directly in SQL instead of pushing every transformation into application code. Treating extract, transform and load as set based operations produces pipelines that are idempotent, traceable and easy to test, rather than fragile chains of scripts with hidden side effects.

A foreign key is not an optional decoration, it is the mechanism a database uses to enforce referential integrity itself instead of relying on application code. This post explains FOREIGN KEY syntax, the behavior of ON DELETE CASCADE, RESTRICT and SET NULL in detail, what ON UPDATE means, correct indexing of foreign key columns, and why disabling FK checks for supposed convenience almost always leads to data corruption.

A trigger can guarantee data integrity and audit trails regardless of which application is writing. It can also create invisible, hard to maintain business logic if used the wrong way. This article covers BEFORE versus AFTER, row level versus statement level, real audit trail patterns, recursion traps, and clear criteria for when a trigger should be used sparingly and deliberately.