Skip to main content
Share Your Experience: Take the 2024 Developer Survey
2 votes
Accepted

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

I was under the impression that lateral joins should be avoided in general when possible ... That's generalizing a bit much. LATERAL join is a tool like any other: use when appropriate. It all ...
Erwin Brandstetter's user avatar
2 votes
Accepted

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

That's a nearest-neighbor classic. The basic solution: SELECT * FROM tableA a CROSS JOIN LATERAL ( SELECT b.name, b.geom <-> a.geom AS dist FROM tableB b ORDER BY dist LIMIT 1 ...
Erwin Brandstetter's user avatar
1 vote

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

Attempt #1: putting your condition in the WHERE clause works but it changes the query results as it will remove all rows with type!=5: SELECT t1.name, t1.type, t2.col1, t2.col2 FROM table1 t1 LEFT ...
bobflux's user avatar
  • 1,496
1 vote
Accepted

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

SELECT t0.name, t0.type, t2.col1, t2.col2 FROM table1 t0 LEFT JOIN table1 t1 ON t0.id = t1.id AND t1.type = 5 LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.id IS NOT NULL; fiddle ...
Akina's user avatar
  • 20k
1 vote

Do PostgreSQL LATERAL joins require or allow an ON clause?

I don't think that LATERAL changes anything here. A comma join or a CROSS JOIN cannot have ON (and you need to stuff the conditions into the WHERE if you have any), while other explicit JOIN types ...
jjanes's user avatar
  • 40k
1 vote
Accepted

ON predicate of Postgres LATERAL JOINs

Short answer: LEFT JOIN requires a join condition - as opposed to CROSS JOIN. Basics in the manual. See also: What is the difference between a LATERAL JOIN and a subquery in PostgreSQL? But the join ...
Erwin Brandstetter's user avatar
1 vote

ON predicate of Postgres LATERAL JOINs

CREATE TABLE ta (aid INT, a INT); CREATE TABLE tb (aid INT, b INT); INSERT INTO ta VALUES (1,10),(2,20); INSERT INTO tb VALUES (1,100),(1,200); SELECT * FROM ta LEFT JOIN LATERAL (SELECT * FROM tb ...
bobflux's user avatar
  • 1,496
1 vote

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

SELECT product.*, IFNULL(taxes.tax,21) AS tax FROM products LEFT JOIN taxes ON taxes.id_prod = products.id;
RolandoMySQLDBA's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible