SQL Tips and Tricks

Original Article ↗ Hacker News Discussion ↗

Overall reception

  • Many readers find the collection of tips helpful, especially for less-experienced SQL users.
  • Others see several tips as workarounds for SQL’s design quirks or as “developer convenience” at the cost of clarity or correctness.

Set operations, anti-joins, and NULLs

  • The EXCEPT trick is considered neat but is criticized as rarely appropriate for production:
    • It removes duplicates by default (unless EXCEPT ALL).
    • It’s hard for optimizers to push down and can be a late, expensive step.
  • EXISTS / NOT EXISTS are widely recommended over LEFT JOIN + WHERE NULL or IN/NOT IN for “anti-join” logic, clarity, and performance.
  • Noted: NOT EXISTS and EXCEPT behave differently in the presence of NULLs.

Formatting, commas, and readability

  • Major bikeshed: leading commas vs trailing commas vs aligning columns.
    • Leading commas enable easy line-commenting and cleaner diffs but many find them visually offensive or unidiomatic.
    • Trailing commas are seen as more natural, but many major databases don’t allow them in SELECT lists.
  • There’s no consensus SQL formatting standard; some recommend using automated formatters (e.g., SQLFluff) to avoid arguments.
  • Long single lines and dense expressions are criticized; CTEs and intermediate columns are suggested for readability.

SELECT * and column selection

  • Many argue against SELECT * in production for performance and plan quality, especially on columnar warehouses.
  • Some defend SELECT * for ad-hoc analysis to save time, acknowledging it is less ideal for recurring queries.
  • There’s a desire for better composability: reusable column groups, EXCLUDE/EXCEPT syntax for “all but these columns,” or views as a workaround.

WHERE 1=1 and “dummy” predicates

  • Used by some as a dev-time convenience to always append AND conditions or easily comment/uncomment filters.
  • Others see it as unnecessary, confusing to newcomers, and potentially encouraging unsafe dynamic SQL construction.
  • Disagreement over whether it introduces real security/performance risk:
    • One side: the pattern often appears in string-built dynamic queries that risk SQL injection and plan bloat.
    • Other side: 1=1 itself is harmless; the risk is how queries are constructed, not the literal.

Performance, indexing, and query plans

  • Strong emphasis on learning each DBMS, reading EXPLAIN plans, and profiling real workloads.
  • EXISTS often outperforms IN and some JOIN patterns for existence checks.
  • Some argue “any non-one-off query should avoid table scans” and advocate liberal indexing; others counter that:
    • Scans are fine for small tables or analytical queries.
    • Indexes have costs (storage, slower inserts) and should be balanced against data size and growth.
  • Discussion about:
    • Indexing computed columns or expressions to make filters sargable.
    • GROUP BY often driving index usefulness.
    • UNION ALL sometimes being faster than OR-heavy predicates.
    • Foreign keys not always auto-creating needed indexes; both sides of a relationship often need explicit indexes.

Subqueries, CTEs, and temp tables

  • One contributor suggests replacing some JOIN + DISTINCT patterns with scalar subqueries in the SELECT list; others warn:
    • Performance varies widely by data shape and indexing.
    • Naive use can lead to per-row subquery execution or errors when multiple rows match.
  • CTEs are not being discouraged by most; they’re recommended for complex logic and readability, though past performance quirks in some databases are noted.
  • For complex stored procedures:
    • One pattern: copy relevant data into temp tables, manipulate, then update base tables in a transaction.
    • Others warn this can add overhead and should be driven by profiling, not assumed.
    • Advice not to over-break queries into small steps without evidence; give the optimizer a full picture first, then optimize if needed.

Dialect differences and non-standard features

  • QUALIFY is praised as useful but noted to be non-standard and unsupported in common OLTP engines like Postgres and SQL Server; supported in some warehouses (Snowflake, Databricks, Teradata).
  • Trailing commas in SELECT and advanced niceties (like SELECT * EXCLUDE/EXCEPT or JOIN USING) exist in some engines (BigQuery, DuckDB, Spanner) but not in mainstream ones.
  • Some wish for FROM-first / pipe-style syntaxes (like Kusto) to improve query ergonomics.

Comments and metadata

  • For SQL Server, using /* ... */ is suggested over -- in some contexts, because features like Query Store may strip line breaks, making line-commented code hard to reformat later.
  • Reminder that multi-line comments can be managed to ease toggling large blocks on and off.

Miscellaneous best practices

  • Don’t rely on foreign keys alone to create all necessary indexes; verify both referencing and referenced columns are indexed appropriately.
  • UPDATE statements should avoid writing unchanged rows to reduce log volume and trigger activity, with care around NULL semantics.
  • Window functions and INTERSECT are briefly highlighted as powerful but underused tools.
  • Several commenters emphasize: avoid “just in case” helpers in production SQL; prioritize clear intent and maintainability over tiny typing conveniences.