Add support for cross join

[L-Mario564]

Addresses #1414.

Implements cross join for all dialects.

await db
  .select({
    user: users.name,
    city: cities.name
  })
  .from(users)
  .crossJoin(cities);

[selalipop]

Any reason this was never merged?

[TweedChristian]

@L-Mario564 has done a lot of work that supports closure tables (e.g., INSERT INTO SELECT FROM) and it is a bit of a pain to reach for decent SQL patterns and not have the support for them. It would be really nice to get his work merged in or at least considered for V1.

[kravetsone]

Idk why many PRs just ignored in Drizzle...

[olee]

Bad luck for me as well I guess. Just started with drizzle and needed exactly this for a complicated query using CTEs. Any idea of there is an alternative as things like ... FROM cte_1, cte_2 also does not seem to be supported (as it generates an implicit cross join)

EDIT: Ohh, I think I found a workaround for this by using a full join instead:

db
  .select({
    ...
  })
  .from(cte_1)
  .fullJoin(cte_2, sql`1 = 1`)

which generates sql like

SELECT ...
FROM cte_1
FULL JOIN cte_2 ON 1 = 1

which is effectively a cross join from what I could determine. ~~I don't know however, how it behaves with regards to performance in postgres.~~ Update: I explained a large query where I used this trick in sql and indeed, it generated the same query plan as a cross join 👍