[sqlite] equivalent for JOIN LATERAL

[Big Stone]

Hello,

I fall over this presentation of LATERAL, from postgresql guys.
(look at pages 1 to 16)

Does it exist in SQLITE ?

If not, would it be possible too much effort ?

[Big Stone]

If not, would it be possible "without" too much effort ?

[Simon Slavin]

Post by Big Stone
I fall over this presentation of LATERAL, from postgresql guys.
(look at pages 1 to 16)

For those like me who hadn't heard of it, here's a reference:

<https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#LATERAL_JOIN>

and from the documentation, section 7.2.1.5 of

<http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html>

Post by Big Stone
Does it exist in SQLITE ?
If not, would it be possible [without] too much effort ?

I'm going to let other people answer the question.

Simon.

[Roger Binns]

Here is a presentation referenced "Modern SQL in PostgreSQL", with
title "Still using Windows 3.1? So why stick to SQL-92?" Lots of
nice exposition and diagrams. SQLite is also mentioned.

http://www.slideshare.net/MarkusWinand/modern-sql

Seen on HackerNews:

https://news.ycombinator.com/item?id=9018129

Roger

[James K. Lowden]

On Sun, 8 Feb 2015 23:52:43 +0100

Post by Big Stone
I fall over this presentation of LATERAL, from postgresql guys.
Does it exist in SQLITE ?

Syntactically, no. Functionally, in part.

Post by Big Stone
If not, would it be possible too much effort ?

I'm guessing the answer is No because the prerequisites are missing.

Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views. You think you'd like to be able to say,

SELECT S.*
FROM T join F(T.t) as S on T.t < S.x

where F is some function that produces a table for a scalar/row
input.

However, perfectly nothing new is really needed to express the idea:

SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

I suspect that new syntax like this is usually added to SQL for the
wrong reasons.

1. Marketing. Now with LATERAL added!
2. User-imagined need, because don't know SQL
3. Punt on query optimization, invent keyword as hint

In each case, they have added complexity without power. The "improved"
system is harder to use and to develop. But, hey, it's progress.

?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away?
? Antoine de Saint-Exupery

--jkl

[Darren Duncan]

I recall that
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ shows
how Pg 9.3's LATERAL join is useful in practice, as it lets you do in
declarational SQL what you may have needed procedural code for before, in which
case it is an improvement. -- Darren Duncan

Post by James K. Lowden
On Sun, 8 Feb 2015 23:52:43 +0100

Post by Big Stone
I fall over this presentation of LATERAL, from postgresql guys.
Does it exist in SQLITE ?

Syntactically, no. Functionally, in part.

Post by Big Stone
If not, would it be possible too much effort ?

I'm guessing the answer is No because the prerequisites are missing.
Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views. You think you'd like to be able to say,
SELECT S.*
FROM T join F(T.t) as S on T.t < S.x
where F is some function that produces a table for a scalar/row
input.
SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)
I suspect that new syntax like this is usually added to SQL for the
wrong reasons.
1. Marketing. Now with LATERAL added!
2. User-imagined need, because don't know SQL
3. Punt on query optimization, invent keyword as hint
In each case, they have added complexity without power. The "improved"
system is harder to use and to develop. But, hey, it's progress.
?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away?
? Antoine de Saint-Exupery
--jkl

[Dominique Devienne]

Post by James K. Lowden

Post by Big Stone
If not, would it be possible too much effort ?

I'm guessing the answer is No because the prerequisites are missing.

[DD] And that's the crux of it. SQLite has virtual tables, and as such can
approximate table functions, but in a very weird and tricky way. Table
functions are part of my top-5 wished for extension in SQLite.

Post by James K. Lowden
Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views. You think you'd like to be able to say,
SELECT S.*
FROM T join F(T.t) as S on T.t < S.x
where F is some function that produces a table for a scalar/row
input.
SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

Thanks James. That was useful to me. --DD

[Big Stone]

oups ! Thank you Roger, I had forgot to post the link.

I got it via a tweet of Wes McKinney, one of the DataScience leader in the
Python World.

https://twitter.com/wesmckinn/status/564526251591733248