[FEATURE]: Join different databases

[fuelkoy]

Enhancement hasn't been filed before.

• I have verified this enhancement I'm about to request hasn't been suggested before.

Describe the enhancement you want to request

It's unclear from the documentation whether Drizzle supports joins across multiple databases.

It was commented here #3010. 

There is support for joins across DBs in MySQL and across schemas in PG.

Can we have an example of how this is done in MySQL, please?

Personal thanks

Huge thank you for the Drizzle team for this amazing library. This is top notch technology, broad possibilities, great developer experience, fun and interactive website, and amazing people.

THANK YOU

[fuelkoy]

Got it working as in the following example:

const results= await db
.select({
	id: orders.id,
	time: sql<string>`
                  CASE
                    WHEN ${orders.deviceID} IS NOT NULL THEN second_db.devices.updated
                    WHEN ${orders.customID} IS NOT NULL THEN ${customIDs.created}
                    ELSE NULL
                  END AS time`,
})
.from(orders)
.where(eq(orders.id, BigInt(1)))
// Use raw SQL to join the second database's table
.leftJoin(
	sql`second_db.devices`,
	eq(orders.deviceID, sql`second_db.devices.joinID`),
)
.leftJoin(customIDs, eq(orders.customID, customIDs.id))
.orderBy(orders.id)
.limit(100);