Tags
#php
#kotlin
#bash
#go
#sql
#rust
#typescript
#html
#java
#python
#files
#utils
#strings
#http
#concurrency
#async
#json
#arrays
#security
#types
#crypto
#database
#dates
#format
SQL
TRUNCATE vs DELETE
`DELETE` is logged per-row and respects triggers; `TRUNCATE` deallocates whole pages and resets the table to empty. `TRUNCATE` is much faster on large tables but has caveats (no WHERE, can't be rolled back in some DBs).
@admin
SQL
DELETE with JOIN / USING
Delete rows from one table conditional on a related table. PostgreSQL uses `USING`; MySQL uses join syntax in the DELETE. Run as a SELECT first to verify what you're about to remove.
SQL
Pattern Matching — LIKE / ILIKE / SIMILAR
`LIKE` uses `%` (any string) and `_` (single char). PostgreSQL `ILIKE` is case-insensitive. For real regex, reach for `SIMILAR TO` (POSIX) or `~` (PostgreSQL).
SQL
BETWEEN — Range Filtering
`BETWEEN a AND b` is inclusive on both ends. Cleaner than `col >= a AND col <= b`. For dates, watch for end-of-day vs midnight boundaries.
SQL
FULL OUTER JOIN / CROSS JOIN
`FULL OUTER` keeps unmatched rows from BOTH sides. `CROSS JOIN` is the Cartesian product — every row of A paired with every row of B. Rare but useful for date-spine and matrix generation.
SQL
INNER JOIN — Match Rows in Both Tables
Returns rows where the join condition matches in BOTH tables. The default JOIN. Use table aliases (`u`, `o`) for readability when joining 3+ tables.
SQL
LEFT JOIN — Keep Unmatched Left Rows
Returns every row from the LEFT side, NULL-filled where the right side doesn't match. The standard pattern for "this entity and its optional children".
SQL
Correlated Subquery
A subquery that references the outer query's row. Slower than a JOIN for big result sets but sometimes the most readable expression.
SQL
WITH Clause — Common Table Expressions
Name a subquery so you can refer to it like a table. Improves readability for complex multi-stage queries — and lets you reuse the same logical block multiple times.
SQL
Pagination with LIMIT / OFFSET
`LIMIT N OFFSET M` is universal — but degrades on deep pages (the DB still has to scan past M rows). Keyset (cursor) pagination is much faster for huge tables.
SQL
JSONB Columns (PostgreSQL)
`jsonb` stores binary-optimized JSON. Index sub-fields with GIN for fast containment queries. Most-used operators: `->` (get field), `->>` (get as text), `@>` (contains), `?` (key exists).
SQL
Array Columns (PostgreSQL)
Native array columns avoid an extra join for "small list of things per row" patterns — tags, categories, allowed_roles. Combine with GIN index for fast `@>` / `&&` operators.
SQL
EXTRACT — Pull Parts Out of a Timestamp
`EXTRACT(field FROM timestamp)` pulls year, month, hour, day-of-week, week, epoch — whatever you need to group, filter, or render. Standard SQL across every database.
SQL
Median with PERCENTILE_CONT
SQL has no `MEDIAN()` — use `PERCENTILE_CONT(0.5) WITHIN GROUP`. The same function gets you P95, P99, quartiles, etc. for latency analysis.
SQL
Recursive CTE — Date Spine Generator
Generate a row per day (or month, year, hour) without a calendar table. Useful for filling gaps when a date has no events but you still want it in your output.
SQL
Recursive CTE — Walk a Hierarchy
`WITH RECURSIVE` lets a CTE refer to itself. The standard way to walk parent/child trees (org charts, comment threads, category nestings) in a single query.
SQL
Self-Join — Org Chart / Hierarchies
A self-join is just a regular join with the same table aliased twice. The canonical example is an employee table where each row points to a manager in the same table.