Questions tagged [lateral-join]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2 votes
1 answer
75 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, ...
ed__'s user avatar
  • 23
1 vote
2 answers
83 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 ...
Nikita Glukhov's user avatar
1 vote
1 answer
342 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 ...
George B's user avatar
0 votes
1 answer
77 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&...
Matt Ross's user avatar
0 votes
1 answer
114 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 ...
domiho1's user avatar
  • 25
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 ...
Ian Chen's user avatar
  • 111
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 (...
tinlyx's user avatar
  • 3,490