Export High-Res PDF
Switch to Light Mode
SkillSnap Official GuideUpdated 7/24/2026
SQL
The language of data. Master execution plans, window functions, and indexing strategies to optimize query performance.
Performance & Indexing
Fetch only needed columns to reduce I/ONO SELECT *
Avoid functions on columns in WHERE (e.g. YEAR(date))SARGable Queries
View Execution Plan and actual timingsEXPLAIN / ANALYZE
Order matters: Most restrictive column firstComposite Index
WHERE id > x (Faster than OFFSET 10000)Keyset Pagination
Leading wildcards prevent index usageAvoid LIKE '%term'
Query Logic & Filtering
EXISTS stops searching at first match (Faster)EXISTS vs IN
Default choice (UNION forces expensive deduplication)UNION ALL
Return first non-null value (Defaults)COALESCE(col, val)
Conditional logic inside SELECT/ORDER BYCASE WHEN
Filter results AFTER groupingHAVING
Joins & Relationships
Matches in both tables (Default)INNER JOIN
All from Left, matches from RightLEFT JOIN
Cartesian Product (Use with caution)CROSS JOIN
Subquery referencing preceding FROM tableLATERAL / APPLY
LEFT JOIN where B.id IS NULL (Find missing)Anti-Join
Window Functions (Analytics)
Unique rank (Great for deduplication)ROW_NUMBER()
Rank skips ties (1,2,2,4); Dense does not (1,2,2,3)RANK() vs DENSE
Access next/prev row (Deltas/Growth)LEAD() / LAG()
Reset calculation group (Window scope)PARTITION BY
Split results into n buckets (Quartiles)NTILE(n)
Integrity & Transactions
Wrap changes in Atomic TransactionsBEGIN / COMMIT
Reduce redundancy/anomaliesNormalization (3NF)
Enforce integrity at DB level (not App)Foreign Keys
ON CONFLICT (Pg) / MERGE (SQL Server)Upsert
Use is_deleted flag instead of DELETESoft Deletes
Admin & Operations
Reclaim space from deleted rowsVACUUM / OPTIMIZE
Faster than DELETE (No row logging)TRUNCATE
High-speed data loadingCOPY / BULK
Reuse connections (PgBouncer/Hikari)Connection Pool