19 Raw SQL Hacks That Saved Me From Ugly Dashboards and Crying ETL Runs

This isn't another SQL tricks post where someone tells you about GROUP BY or joins or CTEs like it's a revelation. This is straight from the trenches. Stuff that saved real queries, real pipelines, real dashboards from burning to the ground.

1. LIMIT to Cut Metadata Scans

If you know you're just checking if a record exists, or probing low-cardinality stuff, throw a LIMIT 1 in there. Even for joins or subqueries. Cuts IO, avoids full scans, especially on wide tables.

select 1 from orders where user_id = 42 limit 1

This is a classic pattern for existence checks.

2. NOT EXISTS Instead of LEFT JOIN with IS NULL

Engines optimize NOT EXISTS better, avoids result bloat and stops early on match.

select id
from users u
where not exists (
  select 1
  from orders o
  where o.user_id = u.id
)

Optimal anti-join pattern backed by performance evals. [dba.stackexchange] [thoughtspot] [erikdarling]

3. CASE WHEN to Avoid Joining Lookup Tables

Inline static mappings cleaner, faster, no extra join.

select case status
  when 'a' then 'active'
  when 'i' then 'inactive'
  else 'unknown'
end as status_label
from accounts

Used everywhere in high-stakes dashboards.

4. DISTINCT ON with ORDER BY for First-Row-Per-Group

Works in Postgres/DuckDB. Simpler and faster than ROW_NUMBER windows.

select distinct on (user_id) *
from events
order by user_id, created_at desc

"SELECT DISTINCT ON … keeps only the first row…" - that's canonical (johnnunemaker.com).

5. FULL OUTER JOIN Emulation Using UNION ALL

For engines without FULL JOIN.

select * from a left join b on a.id = b.id
union all
select * from a right join b on a.id = b.id
where a.id is null

Pure SQL workaround, widely proven in cross-engine tools.

6. Push Filters Inside CTEs Manually

CTEs may get materialized - put your WHERE inside.

with active_users as (
  select *
  from users
  where is_active = true
)
select *
from active_users
where signup_source = 'organic'

Filter pushdown is anti-optimizer horror if ignored (dba.stackexchange.com, thoughtspot.com, erikdarling.com).

7. Bucketing Timestamps with Integer Math

Way faster than date functions.

select floor(epoch_ts / 3600) * 3600 as hour_bucket
from logs

A straight-up speed hack for time-series without DB-specific functions.

8. Boolean XOR via AND/NOT

SQL lacks XOR. Build it clean:

where (is_new and not is_premium)
   or (not is_new and is_premium)

Read-once, sum-twice truth. Minimal, tight, reliable.

9. Positional GROUP BY for Fast Edits

GROUP BY 1 saves you from rewriting column names during ad hoc runs.

select region, count(*)
from users
group by 1

Dev life hack for quick iterations.

10. Dedup Before Join to Prevent Row Explosions

Force-deduping to prevent insane joins.

select *
from users u
join (
  select distinct user_id from orders
) o on u.id = o.user_id

Prevents data multiplication without windowing logic.

11. Avoid SELECT * in Joins

Tight projection avoids spilling temp files.

select u.id, u.name, o.id as order_id
from users u
join orders o on u.id = o.user_id

Clean queries, predictable performance (bennadel.com, timescale.com, wikipedia, risingwave.com, medium.com).

12. WHERE 1=0 for Dry-Run Stubs

Create structure without pulling rows.

select * from payments where 1=0

Scaffolding queries in one line.

13. COALESCE for Cleaner Boolean Logic

Use COALESCE to avoid null traps though be aware of perf implications.

where coalesce(is_active, false) = true

It's just CASE under the hood (stackoverflow.com).

14. Fake Number Sequences Using UNIONs

Simulate generate_series cheaply.

select a.n + b.n * 10 as seq
from (select 0 as n union all select 1 union all select 2) a,
     (select 0 as n union all select 1 union all select 2) b

Clever, portable test data hack.

15. Emulate LEAD/LAG Without Window Functions

If window functions aren't an option:

select id,
  (select min(id)
   from events e2
   where e2.id > e1.id
  ) as next_id
from events e1

Slow on big data, but works everywhere.

16. NULL-Safe Equality Without IS NOT DISTINCT FROM

Portable approach:

where (email = input_email
   or (email is null and input_email is null))

Leverages three-valued logic (dba.stackexchange.com, stackoverflow.com, wikipedia).

17. INTERSECT and EXCEPT for Set Logic

Better expresses intent than joins in some cases.

select user_id from churned_users
intersect
select user_id from active_users

Backed by common set-logic patterns (sqlperformance.com, sqlshack.com).

18. Fallback Row with UNION and NOT EXISTS

Guarantee at least one row:

select * from products
union all
select 'no data', null, null
where not exists (select 1 from products)

Safe guard for dashboards expecting rows.

19. Width Bucketing with FLOOR

Reduce group explosion cleanly:

select floor(age / 10) * 10 as age_bucket, count(*)
from users
group by 1

Quick and effective continuous-to-bucket conversion.