CODEWITHSUNDEEP
sqlamazon-redshiftlateral-join
Eugene Sukh
Eugene Sukh

Reputation: 2737

Rewrite OUTER APPLY to Redshift with subquery

I rewriting sql server scripts to redshift database queries

I have OUTER APPLY construction

OUTER APPLY
   (
   SELECT  q.*
   FROM    (
           SELECT  ROW_NUMBER() OVER(ORDER BY ca.Id DESC) AS rn,
                   ca.StateProvince,
                   ca.ZipPostalCode,
                   ca.ContactId
           FROM    public.contact_addresses ca 
           WHERE   ca.OrganizationId = <Parameters.DemographicsOrgId>
                   AND ca.DeletedDate IS NULL
                   AND ca.TypeId = 7
                   AND ca.ContactId = cc.Id
           ) q
   WHERE   q.rn = 1
   ) ca

But Redshift don't has outer apply. How Ican correctly rewrite it with LEFT JOIN?

UPDATE

I think about rewrite it like this

LEFT JOIN
   (
   SELECT  q.*,
           q.rn = 1
   FROM    (
           SELECT  ROW_NUMBER() OVER(ORDER BY ca.Id DESC) AS rn,
                   ca.StateProvince,
                   ca.ZipPostalCode,
                   ca.ContactId
           FROM    public.contact_addresses ca
           WHERE   ca.OrganizationId = <Parameters.DemographicsOrgId>
                   AND ca.DeletedDate IS NULL
                   AND ca.TypeId = 7
                   AND ca.ContactId = cc.Id
           ) q
   GROUP BY q.rn
   ) ca
ON ca.rn = 1

But is this correctly?

Upvotes: 1

Views: 1319

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

No, it does not look right. I would guess:

LEFT JOIN
(SELECT ca.OrganizationId,
        ROW_NUMBER() OVER (ORDER BY ca.Id DESC) AS rn,
        ca.StateProvince,
        ca.ZipPostalCode,
        ca.ContactId
 FROM  public.contact_addresses ca 
 WHERE ca.DeletedDate IS NULL AND
       ca.TypeId = 7
 GROUP BY ca.OrganizationId, ca.ContactId
) ca
ON ca.ContactId = cc.ID AND
   ca.OrganizationId = <Parameters.DemographicsOrgId> AND
   ca.rn = 1

Basically, you need to aggregate by the correlation conditions (if they are equality) and then use them for the outer ON conditions.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

The OUTER APPLY operator returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression

so your approach is correct

Upvotes: 2

Related Questions