CROSS APPLY - SQL Server to PostgreSQL Migration

SQL Server CROSS APPLY clause allows you to perform a correlated inner join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand.

In PostgreSQL you can use CROSS JOIN LATERAL clause.

Consider a sample data:

  -- Sample tables
  CREATE TABLE colors (name VARCHAR(30), letters VARCHAR(30));
  CREATE TABLE chars (c CHAR(1));
 
  -- Sample rows
  INSERT INTO colors VALUES ('green', 'g,r,e,e,n');
  INSERT INTO colors VALUES ('red', 'r,e,d');
  INSERT INTO colors VALUES ('black', NULL);
 
  INSERT INTO chars VALUES ('r');
  INSERT INTO chars VALUES ('e');
  INSERT INTO chars VALUES ('n');

SQL Server:

  -- Duplicate each 'colors' line for every row returned by table-valued function
  SELECT name, value
  FROM colors 
    CROSS APPLY STRING_SPLIT(colors.letters, ',');

Result:

Note that 'Black' row is not returned by the query as the table-valued function returns NULL for it.

In PostgreSQL you can use CROSS JOIN LATERAL.

PostgreSQL:

  -- Duplicate each 'colors' line for every row returned by table-valued function
  SELECT name, value
  FROM colors 
     CROSS JOIN LATERAL UNNEST(STRING_TO_ARRAY(colors.letters, ',')) t(value);

Result:

Now let's perform CROSS APPLY using a subquery:

SQL Server:

  SELECT name, c
  FROM colors 
    CROSS APPLY (SELECT c FROM chars WHERE CHARINDEX(chars.c, colors.name) <> 0) c;

Result:

In PostgreSQL we have to use LATERAL keyword for the correlated subquery:

PostgreSQL:

  -- Join fails without LATERAL keyword
  SELECT name, c
  FROM colors 
    CROSS JOIN (SELECT c FROM chars WHERE POSITION(chars.c IN colors.name) <> 0) c;
  /* ERROR:  invalid reference to FROM-clause entry for table "colors" */
  /* DETAIL:  There is an entry for table "colors", but it cannot be referenced from this part of the query. */
  /* HINT:  To reference that table, you must mark this subquery with LATERAL. */
 
  -- Now with LATERAL
  SELECT name, c
  FROM colors 
    CROSS JOIN LATERAL (SELECT c FROM chars WHERE POSITION(chars.c IN colors.name) <> 0) c;

Result:

For more information, see SQL Server to PostgreSQL Migration.