Join Types

KQL supports several join kinds that go beyond standard SQL joins. Anti and semi joins are particularly useful for filtering rows based on the presence or absence of matching keys in a second table. Where a dialect lacks native support, the compiler rewrites the join into an equivalent subquery pattern.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Join Kind	SQLite	MySQL	ClickHouse	PostgreSQL	Notes
inner / innerunique	✅	✅	✅	✅	INNER JOIN
leftouter	✅	✅	✅	✅	LEFT JOIN
rightouter	⚠️	✅	✅	✅	SQLite: emits warning, falls back to LEFT JOIN
fullouter	⚠️	⚠️	✅	✅	SQLite/MySQL: emits warning, falls back to LEFT JOIN
leftanti	🔄	🔄	✅	🔄	Non-ClickHouse: rewritten as NOT EXISTS subquery
rightanti	🔄	🔄	✅	🔄	Non-ClickHouse: rewritten as RIGHT JOIN + IS NULL
leftsemi	🔄	🔄	✅	🔄	Non-ClickHouse: rewritten as EXISTS subquery
rightsemi	🔄	🔄	✅	🔄	Non-ClickHouse: approximated as INNER JOIN
leftantisemi	🔄	🔄	✅	🔄	Treated as leftanti
rightantisemi	🔄	🔄	✅	🔄	Treated as rightanti

note

SQLite does not support RIGHT JOIN or FULL OUTER JOIN natively. Queries using rightouter or fullouter against a SQLite target will emit a runtime warning and fall back to a LEFT JOIN, which may produce incomplete results.