equivalent for JOIN LATERAL

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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 ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

big stone
If not, would it be possible "without" too much effort ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

Simon Slavin-3
In reply to this post by big stone

On 8 Feb 2015, at 10:52pm, Big Stone <[hidden email]> wrote:

> 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>

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/08/2015 03:32 PM, Simon Slavin wrote:
> For those like me who hadn't heard of it, here's a reference:

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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlTYB58ACgkQmOOfHg372QSipwCfTJa/M8UPBqQ1UwRmoxgTVSwU
wBoAn0I9HpwyQswDjLjCKjoY6IdTh4lu
=EOku
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

James K. Lowden
In reply to this post by big stone
On Sun, 8 Feb 2015 23:52:43 +0100
Big Stone <[hidden email]> wrote:

> I fall over this presentation of LATERAL, from postgresql guys.
>
> Does it exist in SQLITE ?

Syntactically, no.  Functionally, in part.  

> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

big stone
In reply to this post by 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

Darren Duncan
In reply to this post by James K. Lowden
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

On 2015-02-08 9:12 PM, James K. Lowden wrote:

> On Sun, 8 Feb 2015 23:52:43 +0100
> Big Stone <[hidden email]> wrote:
>
>> I fall over this presentation of LATERAL, from postgresql guys.
>>
>> Does it exist in SQLITE ?
>
> Syntactically, no.  Functionally, in part.
>
>> 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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: equivalent for JOIN LATERAL

Dominique Devienne
In reply to this post by James K. Lowden
On Mon, Feb 9, 2015 at 6:12 AM, James K. Lowden <[hidden email]>
wrote:

> > 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.


>
> 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)
>

Thanks James. That was useful to me. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users