FB4 Lateral join OUTER

[Karol Bieniaszewski]

I test FB4 Lateral join but it look as it work like INNER JOIN.

But i do not see a way to use LEFT JOIN.

 

You know, you have main table and you need for it e.g. last record from detail table, but if not exists still return record from main table.

Is there a way to do LEFT LATERAL? Or i still must use old stored procedure way?

 

regards,

Karol Bieniaszewski

 

[Vlad Khorsun]

On Thursday, 2 December 2021 at 14:31:59 UTC+2 liviuslivius wrote:

I test FB4 Lateral join but it look as it work like INNER JOIN.

But i do not see a way to use LEFT JOIN.

Something like

SELECT R.RDB$RELATION_ID, R.RDB$RELATION_NAME, F.RDB$FIELD_NAME, F.RDB$FIELD_POSITION
  FROM RDB$RELATIONS R LEFT JOIN
         LATERAL (SELECT RF.RDB$FIELD_NAME, RF.RDB$FIELD_POSITION FROM RDB$RELATION_FIELDS RF
                   WHERE RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
                     AND RF.RDB$FIELD_POSITION > 2
                  ORDER BY RF.RDB$FIELD_POSITION DESC
                  ROWS 1) F
       ON 1 = 1

It will show all relations and

- last field name and position, if there is more than 3 fields in relation, or

- nulls as  field name and position

It is artificial, of course, but you see the syntax.

Regards.

Vlad

[Karol Bieniaszewski]

Thank you

 

Then i suppose firebird-40-language-reference.pdf should be updated

As it show example in syntax like.

 

RDB$RELATIONS R,  LATERAL

And i have tried 😉 RDB$RELATIONS R,  LEFT LATERAL

 

I have supposed that this is some special syntax.

But when i look at this now, it is simply old style join SQL 92 if i remember correctly?

 

Pozdrawiam,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e8f6b608-8759-4668-88aa-c0192c685975n%40googlegroups.com.

 

[Mark Rotteveel]

On 2021-12-02 15:20, Karol Bieniaszewski wrote:
> Then i suppose firebird-40-language-reference.pdf should be updated
>
> As it show example in syntax like.
>
> RDB$RELATIONS R, LATERAL
>
> And i have tried 😉 RDB$RELATIONS R, LEFT LATERAL
>
> I have supposed that this is some special syntax.
>
> But when i look at this now, it is simply old style join SQL 92 if i
> remember correctly?

The example shouldn't be "updated", because it is correct. However, what
you're missing is that the example is a CROSS JOIN with the SQL-89
syntax. Yes, it could probably use some more examples, but the possible
syntax is described in "The FROM clause"[1] and "Joins"[2]:

```
SELECT
...
FROM <source>
[<joins>]
[...]

<source> ::=
{ table
| view
| selectable-stored-procedure [(<args>)]
| <derived-table>
| LATERAL <derived-table>
| <common-table-expression>
} [[AS] alias]

<joins> ::= <join> [<join> ...]

<join> ::=
[<join-type>] JOIN <source> <join-condition>
| NATURAL [<join-type>] JOIN <source>
| {CROSS JOIN | ,} <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition> ::= ON <condition> | USING (<column-list>)
```

In other words, if you can do `FROM table1 LEFT JOIN <derived table> ON
<condition>`, you also do `FROM table1 LEFT JOIN LATERAL <derived table>
ON <condition>, with the difference that the derived table with LATERAL
can reference table/derived tables preceding it in the FROM clause,
where a derived table without LATERAL cannot.

Mark

[1]:
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-select-from
[2]:
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-select-joins

[Karol Bieniaszewski]

Hi Mark

 

You know, one example is better then 1000 of words. No one look for syntax definition at first strep only at example.

First example should show basic usage, this is working example, but should show something more natural.

Current example as you can see can be misleading in some way.

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6cbdd08311442df39af0e63744292a1b%40lawinegevaar.nl.

 

[Mark Rotteveel]

On 2021-12-02 20:11, Karol Bieniaszewski wrote:
> You know, one example is better then 1000 of words. No one look for
> syntax definition at first strep only at example.
>
> First example should show basic usage, this is working example, but
> should show something more natural.
>
> Current example as you can see can be misleading in some way.

We happily accept contributions for better examples through pull
requests on https://github.com/FirebirdSQL/firebird-documentation. The
example in this case was taken from the Firebird 4.0 Release Notes.

Mark

[Karol Bieniaszewski]

Done

 

https://github.com/FirebirdSQL/firebird-documentation/pull/167

 

regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/a293f968e6f1f4554112a36fb27cc20b%40lawinegevaar.nl.

 

[Mark Rotteveel]

On 2021-12-03 17:33, Karol Bieniaszewski wrote:
> Done
>
> https://github.com/FirebirdSQL/firebird-documentation/pull/167

Thanks!

Mark

[Karol Bieniaszewski]

A little off topic, but where can i find markup description?

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6d43b711a221eb9c34255a2471e480bc%40lawinegevaar.nl.

 

[Mark Rotteveel]

On 03-12-2021 22:44, Karol Bieniaszewski wrote:
> A little off topic, but where can i find markup description?

On the Asciidoctor documentation site:
https://docs.asciidoctor.org/asciidoc/latest/

And in the Firebird Docwriting Guide:
https://firebirdsql.org/file/documentation/html/en/firebirddocs/docwritehowto/firebird-docwriting-guide.html

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 03-12-2021 17:33, Karol Bieniaszewski wrote:
> Done
>
> https://github.com/FirebirdSQL/firebird-documentation/pull/167

I made some additional changes and also added it to the Firebird 4.0
Language Reference, which is now online (see [1]). The change to the
release notes will be released together with Firebird 4.0.1 (expected
later this month).

Thanks for helping out.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-select-joins-lateral

--
Mark Rotteveel