LATERAL Derived Table
A LATERAL derived table is a subquery in the FROM clause that can reference preceding JOIN-ed tables (in the same FROM clause.)
Example:
SELECT ... FROM t1 JOIN LATERAL (SELECT * FROM t2 WHERE t2.cx = t1.cy) tl ON ...
Questions tagged [lateral-join]
212 questions
2
votes
2 answers
Why does this Snowflake query work without requiring the LATERAL keyword?
I have this view in Snowflake:
create or replace view foo as
select $1 as id_foo, $2 as sales
from (values (1,100),(2,200),(3,300));
And this user-defined table function:
CREATE OR REPLACE FUNCTION build_aux_table ( restriction number )
…
danidiaz
- 26,936
- 4
- 45
- 95
2
votes
1 answer
How to limit top N of each group in Django ORM by using Postgres Window functions or Lateral Joins?
I have following Post, Category & PostScore Model.
class Post(models.Model):
category = models.ForeignKey('Category', on_delete=models.SET_NULL, related_name='category_posts', limit_choices_to={'parent_category': None}, blank=True, null=True)
…
Mahender Thakur
- 510
- 4
- 13
2
votes
2 answers
How do write an SQL query that transforms phone numbers from columns into a single column?
How do I write an SQL query that transforms phone numbers from columns into a single column. Assume there are multiple personIDs and each personID has up to 3 phone types, Primary, Secondary and Tertiary. Currently, for each person, they are listed…
cmomah
- 165
- 2
- 9
2
votes
1 answer
Un-Pivoting Postgres for a large number of columns
So I've been looking at crosstab for pivoting but not sure if there is a more scalable way to do this.
Right now I have a structure that looks like
Date
Amount1
Amount2
Amount3
Date
1
2
1
Date
1
3
2
Date
2
4
1
Date
3
5
2
I'd like…
Nicholas Tong
- 105
- 8
2
votes
1 answer
Iterate over each element in JSON array and join with rows
I am trying to write a SQL query that cross joins each row with its own JSON array elements.
Let's say this is the data we have (I know, it doesn't make much sense):
| id | name | info …
Özenç B.
- 928
- 3
- 8
- 25
2
votes
2 answers
Insert a tableA into another tableB with a different structure
I have a table A looks like :
id isin typ1 typ2
1 aa typA typB
2 bb …
Giovanni
- 61
- 6
2
votes
3 answers
Need help on to execution plan in SQL Server in Type Conversion
I am getting below warnings in execution plan. Please some provide suggestions on how to resolve this error.
Type conversion in expression (CONVERT(int,STRING_SPLIT.[value],0)) may affect "CardinalityEstimate" in query plan choice
Above warning in…
Baxy
- 139
- 1
- 13
2
votes
1 answer
Use field value from a subquery in another subquery where whit generate_series in PostgreSQL
I need to select and Id from the data table, but where the Account is equal to the Account selected by the first subquery. Is that possible? I'm using generate_series because I need to populate a table with random data and I tried the query below…
user33276346
- 1,501
- 1
- 19
- 38
2
votes
2 answers
Oracle: Get the price based on a variable and history table
i have a table "variables" and a table "variables_history", as following
create table variables
(
variables_id number,
variables_name varchar2(50),
variables_value varchar2(50),
variables_updated_at timestamp
);
create table…
Malkath
- 77
- 3
- 13
2
votes
2 answers
Avoid repeating predefined value in SQL insert into
I am looking to insert a couple of values associated to a pair of ids without hardcoding those ids in the query.
More concretely, I have this table foo:
create table if not exists foo(id int, val text);
and I can insert my values by doing:
insert…
ffigari
- 431
- 6
- 18
2
votes
2 answers
Postgres: If we select a computed column multiple times, will Postgres compute it again and again?
Here is the query that I am trying,
SELECT s.id, s.name AS name,
CASE WHEN (ARRAY_AGG(tgs.status) @> '{Hard} ') THEN 'Hard'
WHEN (ARRAY_AGG(tgs.status) @> '{Soft} ') THEN 'Soft'
WHEN (ARRAY_AGG(tgs.status) @> '{Fluid} ') THEN 'Fluid'
WHEN…
Surya
- 2,429
- 1
- 21
- 42
2
votes
1 answer
What is the best SQL effective dates design pattern
I have, what I imagine is a standard SQL Design Pattern. I have two tables that I am trying to join. One has a Date, the other an Effective Date, and I want the last record of the second table with an Effective Date less than or equal to the Date of…
Joel Smallwood
- 21
- 2
2
votes
0 answers
PostgreSQL planner with join lateral (big and small table)
It is my belief that these two queries have the same output
my question is why doesn't the planner test both variants and pick the most efficient one?
select n.id, n.n <-> hc.n hdist_n, hc.*
from n
join lateral (select * from hotelscombined hc…
jaksco
- 423
- 7
- 18
2
votes
2 answers
GROUP BY in CROSS APPLY
Let us have two tables
create table A (
fkb int,
groupby int
);
create table B (
id int,
search int
);
insert into A values (1, 1);
insert into B values (1, 1);
insert into B values (2, 1);
then the following query
select B.id,…
Radim Bača
- 10,646
- 1
- 19
- 33
2
votes
2 answers
Left join lateral for conditional sums
I have a dataset of purchases with customer, product and category.
customer product category sales_value
A aerosol air_care 10
B aerosol air_care 12
C aerosol air_care …
Jivan
- 21,522
- 15
- 80
- 131