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
0
votes
2 answers
How to use Lateral join in Oracle?
I am trying to use Lateral Join in Oracle(Oracle Database 11g Release 11.2.0.1.0) but it is giving some error. I have followed this link…
Ayaz49
- 325
- 2
- 4
- 18
0
votes
1 answer
Apache Drill: Providing a limit in the subquery for a lateral join is not returning the correct results
I am trying to create a simple query with a inner lateral join but I want to restrict the join to a single result in the subquery
select b.`CODE`
from foo.bar.`BRANCH` b
inner join lateral (
select branch_id
from foo.bar.`BRANCH_DISTANCE`
…
thisisshantzz
- 1,067
- 3
- 13
- 33
0
votes
1 answer
PostgreSQL left join lateral output in all cases
CREATE TABLE public.temp (
a text COLLATE pg_catalog."default",
b text COLLATE pg_catalog."default",
c text COLLATE pg_catalog."default",
d text COLLATE pg_catalog."default",
e numeric,
pkey bigint NOT NULL,
CONSTRAINT…
Chapo
- 2,563
- 3
- 30
- 60
0
votes
2 answers
How to create SQL query to sort JSON array using 1 attribute?
I have a table with a column that contains a JSON body that has arrays that I want to sort based on a attribute associated with that array.
I have tried selecting the array name and displaying the attribute which will display the entire array
The…
user162232
- 7
- 4
0
votes
1 answer
Using Lateral on Two Tables in Postgresql
Hullo everyone,
I need a little help figuring out how to use lateral across two tables in Postgresql.
I have two tables, student, and course, and a function student_course_assessment_info_by_course(student_id, course_id): course_assessment_info that…
calben
- 1,328
- 3
- 18
- 33
0
votes
2 answers
Why does a lateral joins with LIMIT increase execution time?
when I run a query with a lateral join and a LIMIT inside, it uses nested a loop join. But when I remove the LIMIT it uses a Hash Right Join. Why?
EXPLAIN ANALYSE
SELECT proxy.*
FROM jobs
LEFT OUTER JOIN LATERAL (
SELECT proxy.*
…
Amr Essam
- 36
- 4
0
votes
2 answers
Same output in two different lateral joins
I'm working on a bit of PostgreSQL to grab the first 10 and last 10 invoices of every month between certain dates. I am having unexpected output in the lateral joins. Firstly the limit is not working, and each of the array_agg aggregates is…
user3096803
0
votes
1 answer
Convert lateral join query to sqlalchemy
I am having difficulty translating a query I created in sql (postgres) into sqlalchemy. In particular, my attempted mapping in sqlalchemy is leading to absurd recursive results that will run far slower than what I originally wrote.
Given the…
P. Greene
- 36
- 1
- 3
0
votes
0 answers
SELECT union of results from same subquery in MySQL
Say I have a query which looks something like this:
select step0.a, step1.a, step2.a
from (select id from tbl1) as step
inner join tbl1 as step0 on step0.id = step.id
left join tbl1 as step1 on step1.b = step0.a
left join tbl1 as step2 on step2.b =…
Fela Maslen
- 2,022
- 3
- 25
- 46
0
votes
0 answers
MariaDB GROUP_CONCAT LATERAL DERIVED
I have two MariaDB instances running on two different machines
1. iMac (Late 2012, 32GB) OSX 10.13.3 (High-Sierra)
2. Brand-new Intel-Server with 4 cores (VMWare) 64GB exclusive Database-Server
On both machines MariaDB 10.3.4 is installed (latest…
Reggaeny
- 1
- 1
0
votes
1 answer
Fast Points in polygon count PostGIS
I am having speed issues building a simple points within buffer query, ie.
counting the number of points inside a buffer given a centroid and a buffer distance.
I have <100 centroids (buffers) and 250.000 points.
Both tables have indexes and have…
illpack
- 107
- 2
- 8
0
votes
2 answers
Comparing rows vs array elements postgres
I have a table A with n rows (200+) and different numeric columns.
I have a table B with m rows (100K+) and a column called multipliers, which is of type array (REAL[]). For every row in B, this array's length is n, ie. a multiplier for every…
illpack
- 107
- 2
- 8
0
votes
1 answer
Using Cross Apply on Greenplum
I would like to know if there's a way to use cross apply the way I'm using it on MSSQL.
Select
pt.PersonName,
psc.Charges,
psc.Taxes
from tbl.PersonTable pt
cross apply(
Select
PersonName,
sum(Charges) Charges,
…
Calvs
- 15
- 5
0
votes
1 answer
Unnest elements from JSON array column
I have a Postgres table test like this:
id | data
---+----------------------------------
0 | {'0':'a','1':'b','2':'c'}
1 | {'0':'d','1':'e' }
2 | {'0':'f','1':'g','2':'h','3':'i'}
How to get the following output?
id | data
---+-----
0 | a
0 |…
user3601578
- 1,310
- 1
- 18
- 29
-1
votes
0 answers
Postgres sql - why aggregated function - count(*) is taking more time?
When i am taking count(*) from my base query it is taking more than 30 min to get the result.
instead of count(*), added '*' the query executed so fast. Here is the explain details.
base query :
explain
select -- QUERY 1
sec.ts,t.cnt
from…
Learn Hadoop
- 2,760
- 8
- 28
- 60