Questions tagged [lateral-join]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1 votes
2 answers
36 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
2 votes
1 answer
143 views

When to use an array aggregate vs a lateral join array subquery in postgres

tl;dr: When to use array_agg over a lateral join with array subquery? Context: create table t_zipcode ( zipcode text primary key, location geography ); create table t_user ( id text primary key, ...
1 vote
2 answers
111 views

ON predicate of Postgres LATERAL JOINs

How does ON predicate of Postgres LATERAL JOIN work? Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN. As far as I understood it is a ...
1 vote
1 answer
407 views

Query optimisation - how to reference lateral join table to avoid duplicate lookup

First time posting here but long time reader. I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in ...
0 votes
1 answer
101 views

MYSQL: Return a predefined value if result of join is null

Before all, sorry my very bad english level. Here's my problem. If i have two tables: 'Products' |id | product | |---|---------| |1 | "Fork" | |2 | "Spoon" | |3 | "Knife&...
0 votes
1 answer
126 views

Add row from table B based on minimum distance between table A and B

I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom): TableA includes a given 'ID', geom, etc , TableB is a "DB" of ...
1 vote
0 answers
29 views

Greedily select rows that match against rules for each group

Suppose there's a product table with the following fields id (pk), category (index), name, picture, stock and we want to find ONE product in each of these category (1, 2, 3) that match at least one ...
2 votes
2 answers
3k views

Do PostgreSQL LATERAL joins require or allow an ON clause?

I don't often see the ON clause used with the LATERAL joins (PostgreSQL 11+). For example, the official documentation has this example: A trivial example of LATERAL is SELECT * FROM foo, LATERAL (...