25

Im trying to find a SQL query on the equivalent usage of OUTER APPLY from MSSQL to PostgreSQL but it seems hard to find.

My MSSQL sample query is like this.

hope someone can help me with my problem. thanks in advance. 

SELECT table1.col1, table1.col2, Supp.ID, Supp.Supplier

FROM SIS_PRS table1 

OUTER APPLY (SELECT TOP 1 ID, SupplierName  FROM table2 WHERE table2.ID = table1.SupplierID) AS Supp
ayanix
  • 429
  • 1
  • 4
  • 9

1 Answers1

39

It is a lateral join:

SELECT table1.col1, table1.col2, Supp.ID, Supp.Supplier
FROM SIS_PRS table1 LEFT JOIN LATERAL
     (SELECT ID, SupplierName
      FROM table2
      WHERE table2.ID = table1.SupplierID
      FETCH FIRST 1 ROW ONLY
     ) Supp
     ON true;

However, you can come pretty close in either database with just a correlated subquery:

SELECT table1.col1, table1.col2, table1.SupplierID, 
       (SELECT Name
        FROM table2
        WHERE table2.ID = table1.SupplierID
        FETCH FIRST 1 ROW ONLY
       ) as SupplierName
FROM SIS_PRS table1;

Also note that in both databases, fetching one row with no ORDER BY is suspicious.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    In the 1st example you have `SELECT ID, SupplierName`, made the (virtual) table alias as `Supp` and selected the fields with `Supp.ID, Supp.Supplier`. This should work. But in the **2nd example**, with the correlated subquery, you included the subquery in the `SELECT` (its result would be a field returned by it), but you are selecting **2 fields** (`SELECT ID, Name`) and defining **the resulting field** as `SupplierName`. How would this work (which of the 2 fields would be returned)? Am I missing something? – Lucas Basquerotto Nov 12 '18 at 13:33
  • 2
    @LucasBasquerotto . . . Good catch. That was a typo. – Gordon Linoff Nov 12 '18 at 13:39
  • `JOIN LATERAL` expects `ON true` after expression – Alex Sham Feb 21 '19 at 17:00