Ispirer Website Ispirer Capabilities: PostgreSQL Migration Free Trial

Outer Apply and Cross Apply Conversion from Microsoft SQL Server to MySQL and PostgreSQL

The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.

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.

MYSQL and PostgreSQL do not support OUTER APPLY and CROSS APPLY. Therefore, OUTER APPLY will be converted to LEFT JOIN LATERAL, and CROSS APPLY will be converted to CROSS JOIN LATERAL. Note that the LATERAL keyword in MySQL is only supported from version 8.

Let's have a look at the following sample:

We have two tables with data - Department table and Employee table:

An analogue of CROSS APPLY in MySQL and PostgreSQL is CROSS JOIN LATERAL.

MS SQL Server MySQL PostgreSQL
SELECT * FROM Department D
CROSS APPLY (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A 		SELECT * FROM Department D
CROSS JOIN LATERAL (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A; 		SELECT * FROM Department D
CROSS JOIN LATERAL (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A;

Result:

You can check and make sure that the queries are equivalent and return the same result set.

An analogue of OUTER APPLY in MySQL and PostgreSQL is LEFT JOIN LATERAL.

MS SQL Server MySQL PostgreSQL
SELECT * FROM Department D
OUTER APPLY (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A 		SELECT * FROM Department D
LEFT JOIN LATERAL (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A on TRUE; 		SELECT * FROM Department D
LEFT JOIN LATERAL (SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID) A on TRUE;

Result:

As you can see, this solution completely repeats the logic of the OUTER APPLY.

This solution is automatically executed by our software SQLWays Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.


If you have any questions or face any difficulties, please contact our support team: support@ispirer.com