Questions tagged [lateral-join]
10 questions
4
votes
2 answers
If LATERAL is optional for table-valued functions, then why does this query error without it?
Setup
CREATE TABLE persons
(
person_id int not null,
name TEXT
);
INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');
CREATE TABLE json_to_parse
(
person_id int not null,
block json
);
INSERT INTO json_to_parse…
J. Mini
- 1,161
- 8
- 32
3
votes
1 answer
Merge two queries into one, involving aggregation
I have these two queries in Postgres.
One is:
SELECT _id, created_at
FROM pedidos
WHERE _id = '123abc;
Works fine, returns:
{
"_id": "123abc",
"created_at": "Sun Jun 08 2025 10:28:23 GMT-0300 (Brasilia Standard Time)",
}
The…
flourigh
- 143
- 4
3
votes
2 answers
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 (SELECT * FROM bar WHERE bar.id =
foo.bar_id)…
tinlyx
- 3,810
- 14
- 50
- 79
2
votes
1 answer
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, zipcode text references t_zipcode);
I want to design…
ed__
- 23
- 5
1
vote
2 answers
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 foreach loop with a correlated subquery inside -…
Nikita Glukhov
- 169
- 5
1
vote
1 answer
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 javascript but don't play well with SQL.
I'm…
George B
- 13
- 3
1
vote
0 answers
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 of the predefined rules in a way that, if there're…
Ian Chen
- 111
- 1
0
votes
1 answer
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" |
and 'taxes'
|id | id_prod | tax |
|---|---------|---------|
|1 | …
Matt Ross
- 23
- 6
0
votes
1 answer
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 locations with a 'name' column, geom, etc...
Need to find for…
domiho1
- 25
- 5
-1
votes
2 answers
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 function is called for every row.
Is there a way to…
Björn Morén
- 143
- 7