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
3
votes
4 answers
How to use results of subquery in a parent query case statement
I have the following query that is returning the expected results:
SELECT
locations.*,
(
SELECT
id
FROM
hauls
WHERE
haul_type_id = 1
AND location_id = locations.id
…
Brian Kidd
- 153
- 3
- 11
3
votes
1 answer
LEFT JOIN match. If no match, need to match on most recent date
My current SQL code:
SELECT
[Date], [Count]
FROM
Calendar_Table pdv
LEFT JOIN
(SELECT
COUNT([FILE NAME]) AS [Count], [CLOSE DT]
FROM
Production_Table
GROUP BY
[CLOSE DT]) [Group] ON [pdv].[Date] =…
Tiffany C
- 63
- 4
3
votes
5 answers
Turn one column into multiple based on index ranges
I have the following table in SQL Server:
| idx | value |
| --- | ----- |
| 1 | N |
| 2 | C |
| 3 | C |
| 4 | P |
| 5 | N |
| 6 | N |
| 7 | C |
| 8 | N |
| 9 | P |
I would like to turn it to…
brubrudsi
- 39
- 3
3
votes
1 answer
How to include OPENJSON in View?
My JSON object is stored in the table (single cell). Right now, I'm reading the cell and saving the value to @json NVARCHAR(MAX)
SELECT *
FROM OPENJSON ( @json )
WITH (...)
, but that obviously doesn't work in views.
How can I do something like…
Mara
- 371
- 5
- 16
3
votes
1 answer
What does `t(sites)` means for presto query?
Here is snippet from aws site:
WITH dataset AS (
SELECT ARRAY[
CAST(
ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
),
CAST(
ROW('news.cnn.com', ROW(false)) AS ROW(hostname…
Cherry
- 31,309
- 66
- 224
- 364
3
votes
2 answers
Is there a way to do LEFT JOIN LATERAL with BigQuery?
Given some rows with duplicate names and different timestamps, I would like to select the row with the newest timestamp, if the duplicate name occurs within say, 45 minutes, of the first timestamp.
Here's what worked in PostgreSQL:
SELECT i.ts AS…
Martin Burch
- 2,726
- 4
- 31
- 59
3
votes
1 answer
Can a CROSS JOIN be "implicitly LATERAL"?
Assume the following, rather simplistic database:
CREATE TABLE test_table(
name TEXT,
data JSONB
);
INSERT INTO test_table VALUES ('name1', '{"a": 1, "b": 2}'), ('name2', '{"c": 3, "d": 4, "e": 5}');
so we have the following table:
# SELECT *…
mbork
- 564
- 3
- 23
3
votes
0 answers
Join on Lateral View Explode Spark1 vs Spark2
If I run the following in spark 1.6 it works fine, but in spark 2.2 I am getting the following error. Anyone know why and how to fix the syntax?
sqlContext.sql("SELECT a.* FROM table1 a Lateral View explode(attributes) attributes_table as…
Breandán
- 1,855
- 22
- 34
3
votes
1 answer
Generate random inside a loop in postgresql
I want to execute several times (lets say 30) an "experiment" that involves random numbers
My approach was:
select
rnd
from
generate_series(0,30) as l, -- number of times
lateral (
select random() as rnd -- the "experiment"
) as t…
nanounanue
- 7,942
- 7
- 41
- 73
3
votes
2 answers
Hive lateral view explode with 2 table joins
Checking to see if this is possible in Hive:
Select a.col1,b.col1
from tableA a join tableB b on a.col1 = b.col1
lateral view explode(numcred) tableA as creds
where creds.id = 9;
I can not find the answer in the docs. In short:
I want to JOIN on…
Don
- 98
- 1
- 4
- 10
3
votes
1 answer
Select from PostgreSQL function that returns composite type
How to include a function that returns a composite type in a SELECT?
I have composite type:
CREATE TYPE public.dm_nameid AS (
id public.dm_int,
name public.dm_str
);
Also, I have a function that returns this type…
Dumitru
- 833
- 3
- 12
- 26
2
votes
4 answers
SQL: how to limit a join on the first match with criterias?
Here are the exemple tables:
Product Prices (prd)
start_date
product
price
2023-04-01
prod_A
10.0
2023-04-15
prod_A
20.0
2023-04-01
prod_B
20.0
Order Products…
Alvaro Santos
- 29
- 3
2
votes
5 answers
How to search in columns in SQL
I have two table,the names table and the earnings table.
Name_ID
Name_desc
1
mark
2
smith
3
becky
4
jimmy
and the earnings…
Petersselorse
- 53
- 6
2
votes
1 answer
Snowflake cross join + lateral flatten
I have a semistructured column that I would like to left lateral join after a cross join.
with t as (
select parse_json('{"1": 1, "2": 2}') as col
)
, cartesian as (
select 1 as a union
select 2 as a union
select 3 as a
)
select…
Nolan Conaway
- 2,639
- 1
- 26
- 42
2
votes
3 answers
Laravel: Limit only 1 record from the 2nd table using eloquent join
I have a two tables colors and color_translations with structure like this:
colors
id
created_at
updated_at
1
2021-08-25
NULL
2
2021-09-01
NULL
color_translations
id
color_id …
jojo
- 126
- 11