4 ms·
I'd never heard of "CROSS JOIN LATERAL" before. So I went and read the PostgreSQL documentation. If I understand correctly what LATERAL does, it is not needed
by SQLite 9y ago
I'd never heard of "CROSS JOIN LATERAL" before. So I went and read the PostgreSQL documentation. If I understand correctly what LATERAL does, it is not needed by SQLite. The SQLite query planner will figure out on its own that you are doing a LATERAL join and do the right thing.
- ufmace 9y agoWell that's interesting. I checked around a bit and tried a few things. Seems that SQLite doesn't support LATERAL joins, but you can get the same effect by putting the subquery in the JOIN condition[0]. I'm not sure if they're exactly the same and you can do all of the same things, but it worked for the quick use-case I came up with: select a.username, p2.* from accounts a join posts p2 on p2.id in (select p1.id from posts p1 where p1.account_id = a.id order by p1.score desc limit 2); [0] https://dba.stackexchange.com/a/100850/ https://dba.stackexchange.com/a/100850/