PostgreSQL :- CROSS APPLY

Background

SQL Server / Transact SQL

In Microsoft’s SQL Server there is solid support for joining tables ( virtual tables from common table expressions, table value functions ).

That support comes from usage of joins ( inner, left joins, right joins ).

And, also cross apply and outer apply.

PostgreSQL

As I was trying to incorporate the lone record set from a common table expression, I did not want to manufacture a join clause and reached for cross apply.

But, it is not supported in PostgreSQL.

What is supported is the Lateral Function.

Sample

Let us put together a contrived sample.

We will bunch a few numbers together, sum them up, and get percentile of each number from the sum.

Get %

Query – Inner Join

Outline

  1. Common Table Expressions
    • cteNumber
      • Get Numbers
    • cteSum
      • Sum Numbers
    • Inner Join
      • on true

SQL


/* Get Numbers */
with cteNumber
(
    number
)
as
(
    select 10
    union all
    select 20
    union all
    select 30
    union all
    select 40
    union all
    select 50
)
/* Get Sum */
, cteNumberSum
as
(

    select
            sum
            (
                coalesce
                (
                    cteN.number
                    , 0
                )
            ) as numberSum

    from   cteNumber cteN

)
/*
    Get Percentile
*/
select 

           cteN.number

         , cteNS.numberSum

         , cast
            (
                (
                    cteN.number * 100.00
                    / cteNS.numberSum
                )
                as decimal(8,2)
            )
         as percentile

from   cteNumber cteN

inner join cteNumberSum cteNS

    on true

Output

laterjoin.exists.01.20190720.1202PM

Query – Inner Join Lateral

Outline

  1. Common Table Expressions
    • cteNumber
      • Get Numbers
    • cteSum
      • Sum Numbers
    • Inner Join Lateral
      • on true

SQL


/*
     * Get Numbers
*/
with cteNumber
(
    number
)
as
(
    select 10
    union all
    select 20
    union all
    select 30
    union all
    select 40
    union all
    select 50
)
/*
    Get Sum
*/
, cteNumberSum
as
(

    select
            sum
            (
                coalesce
                (
                    cteN.number
                    , 0
                )
            ) as numberSum

    from   cteNumber cteN

)
/*
    Get Percential
*/
select 

           cteN.number

         , cteNS.numberSum

         , cast
            (
                (
                    cteN.number * 100.00
                    / cteNS.numberSum
                )
                as decimal(8,2)
            )
         as percentile

from   cteNumber cteN

/*
    inner join cteNumberSum cteNS

        on true

*/
inner join lateral
        (
            select *
            from   cteNumberSum cteNS
        ) cteNS

    on true 

Output

laterjoin.exists.01.20190720.1202PM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s