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
287
votes
5 answers
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
Since PostgreSQL came out with the ability to do LATERAL joins, I've been reading up on it since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.
I understand…
jdotjdot
- 16,134
- 13
- 66
- 118
25
votes
1 answer
What is the equivalent Syntax for Outer Apply in PostgreSQL
Im trying to find a SQL query on the equivalent usage of OUTER APPLY from MSSQL to PostgreSQL but it seems hard to find.
My MSSQL sample query is like this.
hope someone can help me with my problem. thanks in advance.
SELECT table1.col1,…
ayanix
- 429
- 1
- 4
- 9
24
votes
3 answers
MySQL - How to unpivot columns to rows?
ID | a | b | c
1 | a1 | b1 | c1
2 | a2 | b2 | c2
How do I reorganize the rows as ID, columntitle, value?
1 | a1 | a
1 | b1 | b
1 | c1 | c
2 | a2 | a
2 | b2 | b
2 | c2 | c
user2128539
- 241
- 1
- 2
- 5
19
votes
4 answers
How to read a nested collection in Spark
I have a parquet table with one of the columns being
, array>
Can run queries against this table in Hive using LATERAL VIEW syntax.
How to read this table into an RDD, and more importantly how to filter, map etc this…
Tagar
- 13,911
- 6
- 95
- 110
16
votes
2 answers
Can PostgreSQL JOIN on jsonb array objects?
I am considering switching to PostgreSQL, because of the JSON support. However, I am wondering, if the following would be possible with a single query:
Let's say there are two tables:
Table 1) organisations:
ID (INT) | members (JSONB) …
Ruudy
- 217
- 1
- 4
- 17
10
votes
1 answer
Difference between LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake
What is the difference between the use of LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake? I checked the documentation on FLATTEN, LATERAL and TABLE and cannot make heads or tails of a functional difference between the following…
Marty C.
- 576
- 2
- 7
- 22
10
votes
3 answers
How to move set-returning function into a LATERAL FROM item PostgreSQL
I try this
select created_at,
sum((json_array_elements(shipping_lines::json) ->> 'price')::float) as shipping_price
from t1
group by 1
It show Error:
ERROR: aggregate function calls cannot contain set-returning function
calls LINE 5:…
Tom Tom
- 328
- 4
- 15
5
votes
3 answers
SQL query for finding latest or max value of timestamp from table corresponding to N ids Timescaledb
I have a table tab1 in timescale db which has 3 columns tag, time, value. time and tag make up the pk for the table: (time, tag).
There are more than 500 lakhs (50 000 000) of rows. I need to find the latest timestamp or the max(time) for each of…
CuriousCurie
- 113
- 10
5
votes
0 answers
How to workaround this case of lateral join with Spark SQL?
I have a lateral join defined in this way:
select A.id, B.value
from A
left join lateral (
select value
from B
where B.id = A.id
limit 1
) as X on true;
that has the particular point of having limit 1 inside (in more complicated…
Randomize
- 8,651
- 18
- 78
- 133
4
votes
1 answer
SQL query with linear interpolation and Group By
I have a datalake on AWS, queried using Athena, with the following structure and sample data
Key | Date | Value
----+---------------+-------
a | 01/01/2020 | 4.5
a | 05/01/2020 | 6
a | 06/01/2020 | 3.2
b | 01/01/2020 …
70ny
- 748
- 1
- 7
- 22
4
votes
2 answers
Get all keys of jsonb object in postgresql table where value is true
I have a postgresql table customers with name and features columns.
features contains jsonb objects like {"featureA": true, "featureB": false, "featureC":true}
What I'd like to get is an array of those keys in features where the value is true for…
dennisdee
- 160
- 10
4
votes
1 answer
LEFt JOIN LATERAL showing error with SELECT
I am trying to run below query :
SELECT
tc.ID_NUMBER AS AFC_RPP_Number,
hc.BUSINESS AS Business,
hc.DIRECTOR AS Director,
tc.REASON_FOR_REVISION AS Description_of_Change
FROM alo_gg.AWS_PIM tc
left join lateral(
select BUSINESS,DIRECTOR …
Safala
- 51
- 1
- 2
4
votes
3 answers
PostgreSQL: Sum of values related via LATERAL join
I'm trying to clean up data in a PostgreSQL table, where some records have a large number of profanities in the email_address column (the records in question have been entered by agitated users as a result of frustration due to a bug that has since…
Tyson
- 405
- 4
- 13
3
votes
2 answers
Minus 1 unit if the condition is met in mysql
I need some help here, my request is :
List the bookID and number of copy of this in Library (Number of copy in library = number of copy – number of copy student borrow but not return yet)
and this is my code
Select lms.books.bookid,
…
Long Hoàng
- 41
- 4
3
votes
1 answer
Query with lateral join is returning a value with parenthesis. How to remove it?
I'm using Postgres for a chat app. There I have two tables, one with the conversation ids and participants, and another table with the messages.
I'm running the following query to get all the conversations for a user, and I use a lateral join to…
hernanl
- 107
- 9