BLOG → SQL PATTERNS

SQL & QUERYING · 18 PATTERNS

The SQL patterns that quietly break your queries

Eighteen ways a query can look right and still return the wrong thing — ordered from foundations to the deeper cuts. Read them in any order.

Learn the Concepts. Validate Your Skills.

Explore all 18 SQL patterns, then take the free diagnostic to find your strengths, identify your gaps, and know exactly what to learn next.

No card required · Save your progress

Take the free assessment

FOUNDATIONS

P01–P04
P01The WHERE clause that silently returns nothingthe filter that matches zero rows — no error, no warningTESTED IN THE FREE DIAGNOSTICP02NULL changes how comparisons worka missing value quietly eats your comparisonsP03Return the same top rows every timeUse ORDER BY, LIMIT, and tie-breakers to make Top-N queries stable.P04GROUP BY totals that don't add upCOUNT skips the blanks and the total reads lowTESTED IN THE FREE DIAGNOSTIC

JOINS & COMBINING

P05–P09
P05The LEFT JOIN that quietly becomes an INNERone WHERE on the right table and your rows vanishTESTED IN THE FREE DIAGNOSTICP06The join that doubles your revenueone-to-many multiplies every total after itP07The UNION that loses a rowUNION dedups the row you were countingTESTED IN THE FREE DIAGNOSTICP08The subquery that works in dev and dies in prodone extra matching row and it errorsTESTED IN THE FREE DIAGNOSTICP09The count that counts everythinga stray ELSE 0 turns a count into a row count

ANALYTICAL & SEQUENCE

P10–P14
P10The standings that split a tieROW_NUMBER invents an order the scores don't haveTESTED IN THE FREE DIAGNOSTICP11Window frames you never asked forthe default frame counts tied rows twiceTESTED IN THE FREE DIAGNOSTICP12The month that ends at midnightBETWEEN drops the last day's afternoonTESTED IN THE FREE DIAGNOSTICP13The latest row that keeps changinga tie with no tiebreaker flickers between runsTESTED IN THE FREE DIAGNOSTICP14The streak that secretly brokea plain count can't see the gapTESTED IN THE FREE DIAGNOSTIC

SHAPING, WRITES & SCALE

P15–P18
P15The unpivot that swallows a cellUNION drops the label and merges two valuesTESTED IN THE FREE DIAGNOSTICP16The write that applies half your updatesa duplicated source key is undefined territoryTESTED IN THE FREE DIAGNOSTICP17The filter that scans the whole tablewrap a column in a function and the index is goneP18The leaderboard that ranks 85 above 9JSON values are text until you cast themTESTED IN THE FREE DIAGNOSTIC
How the diagnostic is scored →Why these 18 patterns →The six-skill framework →