Questions tagged [lateral-join]
The lateral-join tag has no usage guidance.
7
questions
2
votes
1
answer
112
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
95
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
370
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
82
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
119
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
28
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 ...
1
vote
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 (...