Does Piccolo-ORM support multiple types of join queries, such as outer join queries and left join queries?

[DKSCIcat]

Confused about join queries in the documentation? Not quite sure how to use multiple join queries?

[sinisaos]

@DKSCIcat Piccolo automatically handles joins using LEFT JOIN as specified in the documentation. For any other type of join, you can simply use raw SQL.

[DKSCIcat]

I'll stick with native SQL. I'm not too comfortable with the approach shown in the documentation examples. It would be great if the join method could be updated.

[sinisaos]

@DKSCIcat I think LEFT JOIN is more practical, safer and more general for ORM. LEFT JOIN is used much more often than RIGHT JOIN in ORMs and unlike INNER JOIN it handles nullable relationships (INNER JOIN would exclude rows with missing relationships, and since Piccolo ORM null=True in foreign key by default, this is not a good option). FULL OUTER JOIN and CROSS JOIN are rare and usually handled by raw SQL, not standard ORM queries. That's why I think LEFT JOIN is the best option as a default join. I think it's easy to use joins in Piccolo (if you need a LEFT JOIN). For any other type of join (or any other feature that Piccolo doesn't currently support) you can simply use raw SQL which is also easy to use in Piccolo. Hope that make sense.

[mergisi]

Great explanation @sinisaos! The reasoning behind LEFT JOIN as default makes total sense for ORM use cases.

For anyone coming from raw SQL who wants to experiment with different JOIN types before implementing in Piccolo, I'd recommend:

1. Test your JOIN logic first - Write the raw SQL, verify results, then translate to ORM
2. 2. Understand the NULL behavior - LEFT JOIN preserves all left table rows (with NULLs for non-matches), while INNER JOIN filters them out
      Quick tip: Tools like ai2sql.io can help generate JOIN queries from natural language descriptions - useful for quickly prototyping the SQL before deciding whether to use Piccolo's built-in joins or raw SQL.

The raw SQL escape hatch is a great feature for edge cases like FULL OUTER JOIN or complex self-joins!

[sinisaos]

@mergisi Thank you very much. ai2sql.io looks great and useful.