Hi, is there a way to select from multiple tables ...

# exposed

[Farzad]

Hi, is there a way to select from multiple tables using DSL without joining them? For example:

select *
from Orders o,
     Customers c
where o.customerId = c.id
  and c.id = 1234;

[christophsturm]

is that even valid sql?

[Farzad]

That's just a gist of what I want to achieve, the query will have a where condition and selected fields. Edited the query to something that makes more sense.

[Farzad]

The only reason I don't want to use Join is because I'm working on a legacy db where tables are not designed properly which causes the join to be slower than a normal select

[hho]

@christophsturm select * from foo, bar is valid SQL and equivalent to select * from foo cross join bar. It results in the cartesian product.

[hho]

@Farzad So basically, you can't select from multiple tables without joining, because that's not possible in SQL. You can try to use crossJoin to achieve what you want, but I'd recommend just rewriting your query to

SELECT *
FROM Orders o JOIN Customers c ON o.customerId=c.id
WHERE c.id = 1234;

[Farzad]

Yeah that's initially what I did but again because of the table structure it slowed down the query significantly.
You can select from multiple tables in SQL, just need to write the correct where condition. Although I don't know how to achieve it using Exposed DSL

[hho]

Yes, you can select from multiple tables in SQL, but not without joining. Your query is doing a cross join.

[Farzad]

Yes seems like cross join achieves the same thing, thanks! I have a couple of more questions but I'll ask in a separate thread

[tapac]

You may try to make your own version of join like :

class SimpleJoin(private vararg val tables: Table) : ColumnSet() {
    override val columns: List<Column<*>>
        get() = tables.flatMap { it.columns }

    override fun describe(s: Transaction, queryBuilder: QueryBuilder): String = tables.joinToString { it.describe(s, queryBuilder) }

    override fun join(otherTable: ColumnSet, joinType: JoinType, onColumn: Expression<*>?, otherColumn: Expression<*>?, additionalConstraint: (SqlExpressionBuilder.() -> Op<Boolean>)?): Join {
        TODO("not implemented")
    }

    override fun innerJoin(otherTable: ColumnSet): Join = TODO("not implemented")

    override fun leftJoin(otherTable: ColumnSet): Join = TODO("not implemented")

    override fun crossJoin(otherTable: ColumnSet): Join = TODO("not implemented")
}

[tapac]

and use it like:

SimpleJoin(Orders, Customers).select {
Orders.customerId eq Customers.id and (Customers.id eq 1234)
}

I didn't test that code, so please let me know if that helps