DIRECTED JOINS in SQL

Today I learned about a SQL feature I hadn’t seen before: DIRECTED JOINS.
Snowflake quietly added it last month as a preview feature.

Here’s a quick example 👇

SELECT
  orders.order_id,
  orders.order_date,
  order_lines.*
FROM orders
INNER DIRECTED JOIN order_lines ON (
  orders.line_id = order_lines.id
);

By adding the DIRECTED keyword, you’re telling Snowflake:

👉 “Scan the table on the left (orders) first before scanning the right (order_lines).”

Why this matters?

🔹 Normally, Snowflake’s query optimizer picks the join order for you
🔹 Most of the time, that works just fine
🔹 But sometimes… the optimizer makes a bad choice leading to big performance hits

When to use DIRECTED JOIN?

✅ You know one side of the join is always smaller (e.g., orders)
✅ You’re filtering heavily on the left side
✅ You need an extra lever for tuning complex joins

🔧 My takeaway is this: DIRECTED JOIN adds a new tool to the performance-tuning toolkit. Likely you won’t need it every day, but when you know more than the optimizer, it gives you a way to take control.

⚡ TL;DR: Directed joins can be used to improve (complex) join performance when you know (or you think you know) better than the optimizer.

🤔 What do you think about this new feature? Did I miss something?