SQL is a sneaky language because you can keep building your select statement to your heart’s desires until one change suddenly makes the query slow. There are some anti-patterns you can avoid - in fact, they’re easy to spot with enough practice.
Doing dbt style statements for the latest values
So dbt has been recommending doing incremental refreshes using this sort of syntax:
SELECT * FROM subscriptionsWHERE date_from >= (SELECT max (date_from) FROM subscriptions)
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ subscription_id │
│ user_id │
│ plan_id │
│ date_from │
│ date_to │
│ │
│ ~29 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ NESTED_LOOP_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ │
│ Conditions: ├──────────────┐
│ date_from >= SUBQUERY │ │
│ │ │
│ ~29 rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ PROJECTION │
│ ──────────────────── ││ ──────────────────── │
│ Table: subscriptions ││ CASE WHEN ((#1 > 1)) THEN│
│ Type: Sequential Scan ││ ("error"('More than one │
│ ││ row returned by a │
│ Projections: ││ subquery used as an │
│ date_from ││ expression - scalar │
│ subscription_id ││ subqueries can only │
│ user_id ││ return a single row. │
│ plan_id ││ Use "SET │
│ date_to ││ scalar_subquery_error_on_m│
│ ││ ultiple_rows=false" to │
│ ││ revert to previous │
│ ││ behavior of returning a │
│ ││ random row.')) ELSE #0 END│
│ ││ │
│ ~400 rows ││ ~1 row │
└───────────────────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNGROUPED_AGGREGATE │
│ ──────────────────── │
│ Aggregates: │
│ "first"(#0) │
│ count_star() │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ #0 │
│ │
│ ~1 row │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNGROUPED_AGGREGATE │
│ ──────────────────── │
│ Aggregates: max(#0) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ date_from │
│ │
│ ~400 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ ──────────────────── │
│ Table: subscriptions │
│ Type: Sequential Scan │
│ │
│ Projections: │
│ date_from │
│ │
│ ~400 rows │
└───────────────────────────┘
Notice the plan: the scalar subquery forces a NESTED_LOOP_JOIN against a second scan of subscriptions, plus a CASE WHEN … error('More than one row…') guard that DuckDB injects to enforce scalar-subquery semantics. This happens because the database cannot use an existing query plan and can’t do “parameter sniffing”. One way to avoid this is to set the max value from the subquery and then pass it forward to the main query:
SET VARIABLE max_date = (SELECT max (date_from) FROM subscriptions);SELECT * FROM subscriptionsWHERE date_from >= getvariable('max_date' )
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ subscription_id │
│ user_id │
│ plan_id │
│ date_from │
│ date_to │
│ │
│ ~80 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ ──────────────────── │
│ Table: subscriptions │
│ Type: Sequential Scan │
│ │
│ Projections: │
│ date_from │
│ subscription_id │
│ user_id │
│ plan_id │
│ date_to │
│ │
│ Filters: │
│ date_from>='2025-03-22': │
│ :DATE │
│ │
│ ~80 rows │
└───────────────────────────┘
Now the plan collapses to a single SEQ_SCAN with a constant filter — the subquery is evaluated once, up front, and the main query never sees it.
Using OR in joins
Just because you can use an OR statement in a SQL query, doesn’t mean you have to - in practice, the query optimizer may not correctly interpret what you’re doing . See the query plan for this:
SELECT users.* FROM users INNER JOIN calendar calON signup_date = cal.date OR confirmation_date = cal.date where year (cal.date ) = 2023
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ user_id │
│ first_name │
│ last_name │
│ email │
│ signup_date │
│ confirmation_date │
│ activation_date │
│ │
│ ~438 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ BLOCKWISE_NL_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ ├──────────────┐
│ Condition: │ │
│ ((signup_date = date) OR │ │
│(confirmation_date = date))│ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ FILTER ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ ("year"(date) = 2023) ││ Table: users │
│ ││ Type: Sequential Scan │
│ ││ │
│ ││ Projections: │
│ ││ signup_date │
│ ││ confirmation_date │
│ ││ user_id │
│ ││ first_name │
│ ││ last_name │
│ ││ email │
│ ││ activation_date │
│ ││ │
│ ~438 rows ││ ~100 rows │
└─────────────┬─────────────┘└───────────────────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ ──────────────────── │
│ Table: calendar │
│ Type: Sequential Scan │
│ Projections: date │
│ │
│ ~2,192 rows │
└───────────────────────────┘
SELECT users.* FROM users INNER JOIN calendar calON signup_date = cal.date where year (cal.date ) = 2023 UNION SELECT users.* FROM users INNER JOIN calendar calON confirmation_date = cal.date where year (cal.date ) = 2023
┌───────────────────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: │
│ #0 │
│ #1 │
│ #2 │
│ #3 │
│ #4 │
│ #5 │
│ #6 │
│ │
│ ~20 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNION ├───────────────────────────────────────────┐
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ PROJECTION │ │ PROJECTION │
│ ──────────────────── │ │ ──────────────────── │
│ user_id │ │ user_id │
│ first_name │ │ first_name │
│ last_name │ │ last_name │
│ email │ │ email │
│ signup_date │ │ signup_date │
│ confirmation_date │ │ confirmation_date │
│ activation_date │ │ activation_date │
│ │ │ │
│ ~20 rows │ │ ~20 rows │
└─────────────┬─────────────┘ └─────────────┬─────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ HASH_JOIN │ │ HASH_JOIN │
│ ──────────────────── │ │ ──────────────────── │
│ Join Type: INNER │ │ Join Type: INNER │
│ │ │ │
│ Conditions: ├──────────────┐ │ Conditions: ├──────────────┐
│ date = signup_date │ │ │ date = confirmation_date │ │
│ │ │ │ │ │
│ ~20 rows │ │ │ ~20 rows │ │
└─────────────┬─────────────┘ │ └─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ FILTER ││ SEQ_SCAN ││ FILTER ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── ││ ──────────────────── ││ ──────────────────── │
│ ("year"(date) = 2023) ││ Table: users ││ ("year"(date) = 2023) ││ Table: users │
│ ││ Type: Sequential Scan ││ ││ Type: Sequential Scan │
│ ││ ││ ││ │
│ ││ Projections: ││ ││ Projections: │
│ ││ signup_date ││ ││ confirmation_date │
│ ││ user_id ││ ││ user_id │
│ ││ first_name ││ ││ first_name │
│ ││ last_name ││ ││ last_name │
│ ││ email ││ ││ email │
│ ││ confirmation_date ││ ││ signup_date │
│ ││ activation_date ││ ││ activation_date │
│ ││ ││ ││ │
│ ~438 rows ││ ~100 rows ││ ~438 rows ││ ~100 rows │
└─────────────┬─────────────┘└───────────────────────────┘└─────────────┬─────────────┘└───────────────────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ SEQ_SCAN │ │ SEQ_SCAN │
│ ──────────────────── │ │ ──────────────────── │
│ Table: calendar │ │ Table: calendar │
│ Type: Sequential Scan │ │ Type: Sequential Scan │
│ Projections: date │ │ Projections: date │
│ │ │ │
│ ~2,192 rows │ │ ~2,192 rows │
└───────────────────────────┘ └───────────────────────────┘
BLOCKWISE_NL_JOIN in the query plan is a blockwise nested loop join - it’s a very inefficient algorithm whereas the UNION is much faster.
left join with left side predicate in join
I haven’t tried this in other databases, but apparently DuckDB doesn’t support efficient joins when you want to join a table only to some of the rows but you want to keep the whole table intact. Luckily, in my experience, this is easy to overcome using a case when statement and overall this pattern is VERY rare!
#| echo: false #| results: asis show_plan ("SELECT users.* FROM users LEFT JOIN calendar cal ON confirmation_date = cal.date AND year(confirmation_date) = 2023 " )
```sql
SELECT users.*
FROM users
LEFT JOIN calendar cal
ON confirmation_date = cal.date
AND year(confirmation_date) = 2023
```
::: {.sql-plan}
```
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ user_id │
│ first_name │
│ last_name │
│ email │
│ signup_date │
│ confirmation_date │
│ activation_date │
│ │
│ ~2,192 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ BLOCKWISE_NL_JOIN │
│ ──────────────────── │
│ Join Type: RIGHT │
│ │
│ Condition: ├──────────────┐
│ ((confirmation_date = date│ │
│ ) AND ("year" │ │
│ (confirmation_date) = 2023│ │
│ )) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Table: calendar ││ Table: users │
│ Type: Sequential Scan ││ Type: Sequential Scan │
│ Projections: date ││ │
│ ││ Projections: │
│ ││ confirmation_date │
│ ││ user_id │
│ ││ first_name │
│ ││ last_name │
│ ││ email │
│ ││ signup_date │
│ ││ activation_date │
│ ││ │
│ ~2,192 rows ││ ~100 rows │
└───────────────────────────┘└───────────────────────────┘
```
:::
EXPLAIN WITH tmp AS (SELECT * ,CASE WHEN plan_autorenew = 1 THEN 1 WHEN plan_legacy = 1 THEN 1 END AS plan_vipFROM subscriptionsSELECT * FROM tmpINNER JOIN vip ON vip.id = plan_vip
EXPLAIN SELECT * FROM subscriptions INNER JOIN vip ON plan_autorenew = vip.id AND plan_legacy IS NULL UNION ALL SELECT * FROM subscriptions INNER JOIN vip ON plan_legacy = vip.id AND plan_autorenew IS NULL
Using UNION where UNION ALL is sufficient
UNION deduplicates data after the join, which is a waste of resources if you know that the data is already unique. Use UNION ALL instead.
SELECT users.* FROM users INNER JOIN calendar calON signup_date = cal.date where year (cal.date ) = 2023 UNION SELECT users.* FROM users INNER JOIN calendar calON confirmation_date = cal.date where year (cal.date ) = 2023
┌───────────────────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: │
│ #0 │
│ #1 │
│ #2 │
│ #3 │
│ #4 │
│ #5 │
│ #6 │
│ │
│ ~20 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNION ├───────────────────────────────────────────┐
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ PROJECTION │ │ PROJECTION │
│ ──────────────────── │ │ ──────────────────── │
│ user_id │ │ user_id │
│ first_name │ │ first_name │
│ last_name │ │ last_name │
│ email │ │ email │
│ signup_date │ │ signup_date │
│ confirmation_date │ │ confirmation_date │
│ activation_date │ │ activation_date │
│ │ │ │
│ ~20 rows │ │ ~20 rows │
└─────────────┬─────────────┘ └─────────────┬─────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ HASH_JOIN │ │ HASH_JOIN │
│ ──────────────────── │ │ ──────────────────── │
│ Join Type: INNER │ │ Join Type: INNER │
│ │ │ │
│ Conditions: ├──────────────┐ │ Conditions: ├──────────────┐
│ date = signup_date │ │ │ date = confirmation_date │ │
│ │ │ │ │ │
│ ~20 rows │ │ │ ~20 rows │ │
└─────────────┬─────────────┘ │ └─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ FILTER ││ SEQ_SCAN ││ FILTER ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── ││ ──────────────────── ││ ──────────────────── │
│ ("year"(date) = 2023) ││ Table: users ││ ("year"(date) = 2023) ││ Table: users │
│ ││ Type: Sequential Scan ││ ││ Type: Sequential Scan │
│ ││ ││ ││ │
│ ││ Projections: ││ ││ Projections: │
│ ││ signup_date ││ ││ confirmation_date │
│ ││ user_id ││ ││ user_id │
│ ││ first_name ││ ││ first_name │
│ ││ last_name ││ ││ last_name │
│ ││ email ││ ││ email │
│ ││ confirmation_date ││ ││ signup_date │
│ ││ activation_date ││ ││ activation_date │
│ ││ ││ ││ │
│ ~438 rows ││ ~100 rows ││ ~438 rows ││ ~100 rows │
└─────────────┬─────────────┘└───────────────────────────┘└─────────────┬─────────────┘└───────────────────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ SEQ_SCAN │ │ SEQ_SCAN │
│ ──────────────────── │ │ ──────────────────── │
│ Table: calendar │ │ Table: calendar │
│ Type: Sequential Scan │ │ Type: Sequential Scan │
│ Projections: date │ │ Projections: date │
│ │ │ │
│ ~2,192 rows │ │ ~2,192 rows │
└───────────────────────────┘ └───────────────────────────┘
SELECT users.* FROM users INNER JOIN calendar calON signup_date = cal.date where year (cal.date ) = 2023 UNION ALL SELECT users.* FROM users INNER JOIN calendar calON confirmation_date = cal.date where year (cal.date ) = 2023
┌───────────────────────────┐
│ UNION ├───────────────────────────────────────────┐
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ PROJECTION │ │ PROJECTION │
│ ──────────────────── │ │ ──────────────────── │
│ user_id │ │ user_id │
│ first_name │ │ first_name │
│ last_name │ │ last_name │
│ email │ │ email │
│ signup_date │ │ signup_date │
│ confirmation_date │ │ confirmation_date │
│ activation_date │ │ activation_date │
│ │ │ │
│ ~20 rows │ │ ~20 rows │
└─────────────┬─────────────┘ └─────────────┬─────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ HASH_JOIN │ │ HASH_JOIN │
│ ──────────────────── │ │ ──────────────────── │
│ Join Type: INNER │ │ Join Type: INNER │
│ │ │ │
│ Conditions: ├──────────────┐ │ Conditions: ├──────────────┐
│ date = signup_date │ │ │ date = confirmation_date │ │
│ │ │ │ │ │
│ ~20 rows │ │ │ ~20 rows │ │
└─────────────┬─────────────┘ │ └─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ FILTER ││ SEQ_SCAN ││ FILTER ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── ││ ──────────────────── ││ ──────────────────── │
│ ("year"(date) = 2023) ││ Table: users ││ ("year"(date) = 2023) ││ Table: users │
│ ││ Type: Sequential Scan ││ ││ Type: Sequential Scan │
│ ││ ││ ││ │
│ ││ Projections: ││ ││ Projections: │
│ ││ signup_date ││ ││ confirmation_date │
│ ││ user_id ││ ││ user_id │
│ ││ first_name ││ ││ first_name │
│ ││ last_name ││ ││ last_name │
│ ││ email ││ ││ email │
│ ││ confirmation_date ││ ││ signup_date │
│ ││ activation_date ││ ││ activation_date │
│ ││ ││ ││ │
│ ~438 rows ││ ~100 rows ││ ~438 rows ││ ~100 rows │
└─────────────┬─────────────┘└───────────────────────────┘└─────────────┬─────────────┘└───────────────────────────┘
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ SEQ_SCAN │ │ SEQ_SCAN │
│ ──────────────────── │ │ ──────────────────── │
│ Table: calendar │ │ Table: calendar │
│ Type: Sequential Scan │ │ Type: Sequential Scan │
│ Projections: date │ │ Projections: date │
│ │ │ │
│ ~2,192 rows │ │ ~2,192 rows │
└───────────────────────────┘ └───────────────────────────┘
GROUP BY vs. DISTINCT vs. EXISTS
EXISTS is the fastest but also most verbose way of returning unique values in a table. See the following: