jueves, 17 de marzo de 2022

SQL subselect arbitrary field from arbitrary table

Imagine you used to have `select f1, f2 from t1`. But now f2 becomes a fk to a table t2, so now we have f2_id that points to t2 (with fields id, name) where t1.f2_id = t2.id .

In my case, I only had access to the select fields list (the 'from t1' was already fixed, and I just could modify the retrieved fields), so I couldn't do an actual join, BUT! I found you can do a similar thing to an sql join for individual fields, adding a subquery as a field in the field_list.

select t1.f1, (select name from t2 where  t2.id=t1.f2_id) as f2 from t1 

Really really nice to know that t1.f2_id can be gathered from the subquery. The subquery will be triggered once per t1 row, so it'll probably be slower than a join, but check your EXPLAIN ANALYZE, and see if the cost is bearable, or even if it has any impact at all for your use case.

Some more explanations in here, or here.