Use = instead of IN on join subquery with limit 1

[freder1c]

Not sure if sequel provides this functionality but I have following problem. I like to join a table like this:

SELECT t1.*, t2.column_a 
FROM t1
LEFT JOIN t2 ON t2.id = (SELECT id FROM t2 WHERE t2.t1_id = t1.id) ORDER BY column_a DESC LIMIT 1)

To generate this query I tried using:

DB[:t1].select_all(:t1).select_append(Sequel[:t2][:column_a]).left_join(
  :t2, id: DB[:t2].select(:id).where(Sequel[:t2][:t1_id] => Sequel[:t1][:id]).order(Sequel.desc(:column_a)).limit(1)
)

But sequel is interpreting the DB[:t2] as an array and will use:

ON t2.id IN (SELECT id FROM t2 WHERE t2.t1_id = t1.id) ORDER BY column_a DESC LIMIT 1)

which works functional wise, but will be much slower as using =. Is there a way to tell sequel to use = instead of IN or do I need to rethink my query?

[jeremyevans]

Sequel isn't interpreting DB[:t2] as an array, it is treating it as a dataset. If you want to use = instead of IN, you could try the following as the join conditions:

DB[:t2].select(:id).where(Sequel[:t2][:t1_id] => Sequel[:t1][:id]).order(Sequel.desc(:column_a)).limit(1) => Sequel[:t2][:id]

This is basically the same, just with the key/value switched. Sequel will use IN for identifier => dataset, but will use = for dataset => identifier.

[freder1c]

Indeed, this solves the problem and it works now as expected. Didn't came to my mind that it can be switched. But which makes sense, because it doesn't matter on which side the subquery is when using = from sql perspective. Thanks for the help!