Questions tagged [lateral-join]
LATERAL Derived Table
lateral-join
222
questions
0
votes
1
answer
43
views
Left join later flatten works not like left join, but the same as inner join
With this data:
create or replace table test_json as
select
parse_json('{
"include": [
"x",
"y",
"z"
...
- 45
0
votes
0
answers
32
views
Using lateral join from a subquery in sqlalchemy
I have 2 tables (defined as models in python): Productions and Tasks.
A Production can contain 1 or more Tasks.
Tasks have a name (string), a priority value (int) and a state (TODO / ACTIVE / COMPLETE)...
0
votes
3
answers
90
views
Performance loss introducing lateral join in Postgres query
I am dealing with a PostgreSQL (v14) query of this kind
SELECT
...,
EXISTS (
SELECT 1
FROM table2 t2
WHERE t2.fk = t1.id
AND LOWER(t2.table_name) = 't1'
) ...
- 15
0
votes
1
answer
30
views
Postgresql lateral join on condition evaluation order
Hello I am having problem with postgresql lateral join. My query is this:
SELECT *
FROM foo
LEFT JOIN LATERAL (SELECT tsrange(now() at time zone 'utc', expiration_date) as time_range)
...
- 53
-1
votes
1
answer
62
views
Use results from one query in WHERE clause of the next query
This is how it currently works, first select query returns a list of parameters.
SELECT character_id, foe_id, location_id, date_time, damage, points
FROM events
ORDER BY date_time DESC
LIMIT 100
...
2
votes
1
answer
73
views
For each row in table A, update A.amount related rows in table B
Using PostgreSQL 16.2, I have two tables:
task_pool table
create table task_pool (
task_id serial primary key
, order_id integer
, user_id integer
, expire_time timestamp
, status integer default 0
)...
- 75
0
votes
0
answers
24
views
¿Is it possible to use a table alias generated in a lateral subquery in another lateral subquery?
The doubt arises from a scenario like this:
I have a table named one_table that has many related rows in another table named another_table, via foreign key like this another_table(one_table_id) ...
- 3,911
1
vote
1
answer
58
views
Lateral Join returns too many rows
I try to understand LATERAL JOIN with this query:
select m1.contributor_prescription, derniere_publication.date_publication_prescription
from activite.metadonnee m1
left join lateral
(select ...
- 177
0
votes
1
answer
72
views
snowflake query is taking too much time to run
SELECT
category_tree_master__."category_id" AS category_id,
--searchchildrenmaster.children,
--ARRAY_CONTAINS(category_product_nb_stand_nb_product."category_id" ::...
0
votes
1
answer
75
views
KNN Across categories in postgis using indexing
I have a dataset of points of different types. For every point in the dataset I want to find the closest point in every category. I can achieve this but the compute time is very long and I'm ...
3
votes
2
answers
766
views
Why is a LATERAL JOIN faster than a correlated subquery in Postgres?
I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why?
This SO answer says that there is no golden rule and ...
- 1,822
0
votes
1
answer
257
views
LATERAL JOINS Causing Performance Issues
This query is for a Tableau dashboard. The users want to see the active clients in a sector as well as the contacts at those firms and how the composition of the data changes from quarter to quarter. ...
- 323
5
votes
2
answers
1k
views
ON predicate of Postgres LATERAL JOIN
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 ...
- 301
0
votes
1
answer
53
views
Snowflake Equiv of PySpark STACK (LATERAL VIEW)
I have working code using PySpark which uses the STACK function to turn columns into rows, creating a display_name column in doing so.
I am trying to convert this to a Snowflake query, in theory my ...
- 69
0
votes
2
answers
129
views
lateral join with a right join
so I have two tables, A and B.
first I do a lateral join of A with one column (items)
select A.id, item->>'id' as item_id, b.id
from a, jsonb_array_elements(items) rx(item)
then I do a right ...
- 129