Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the equivalent Syntax for Outer Apply in PostgreSQL [duplicate]

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
like image 680
ayanix Avatar asked Dec 21 '17 13:12

ayanix


People also ask

What is Outer apply in SQL?

It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two. On the other hand, OUTER APPLY retrieves all the records from both the table valued function and the table, irrespective of the match.

Is Outer apply same as LEFT join?

OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY's final output contains all records from the left-side table or table-evaluated function, even if they don't match with the records in the right-side table or table-valued function.

What is cross apply and outer apply?

Thus, the CROSS APPLY is similar to an INNER JOIN, or, more precisely, like a CROSS JOIN with a correlated sub-query with an implicit join condition of 1=1. The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression.

What is cross apply in PostgreSQL?

Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery.


1 Answers

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.

like image 90
Gordon Linoff Avatar answered Nov 05 '22 21:11

Gordon Linoff