/clickhouse-best-practices

Use ANY JOIN When Only One Match Needed

Impact: HIGH

Use ANY JOINs when you only need a single match rather than all matches. They consume less memory and execute faster.

Incorrect (returns all matches):

-- Returns all matching rows, uses more memory
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;

Correct (returns first match only):

-- Returns only first match per row, faster and less memory
SELECT o.order_id, c.name
FROM orders o
LEFT ANY JOIN customers c ON c.id = o.customer_id;

ANY JOIN types:

Reference: Minimize and Optimize JOINs