OUTER APPLY - SQL Server to MariaDB Migration

SQL Server OUTER APPLY clause allows you to perform a correlated outer 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 MariaDB you can use LEFT OUTER JOIN LATERAL ON TRUE clause (still in progress, see https://jira.mariadb.org/browse/MDEV-19078). .

Consider a sample data:

  CREATE TABLE colors (name VARCHAR(30), arr VARCHAR(30));
  CREATE TABLE chars (c CHAR(1));
 
  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 
    OUTER APPLY OPENJSON(colors.arr) WITH (value CHAR '$') t;

Result:

Note that 'Black' row is also returned by the query although the table-valued function returns NULL for it i.e outer join is performed.

In MariaDB you can use LEFT OUTER JOIN LATERAL ON TRUE. Note that LATERAL keyword is not allowed for joins with table-valued functions:

MariaDB:

  -- Duplicate each 'colors' line for every row returned by table-valued function
  SELECT name, value
  FROM colors 
     LEFT OUTER JOIN JSON_TABLE(colors.arr, '$[*]' COLUMNS (value CHAR(1) PATH '$')) t ON TRUE;

Result:

Now let's perform OUTER APPLY using a subquery:

SQL Server:

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

Result:

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

MariaDB:

  -- Join fails without LATERAL keyword
  SELECT name, c
  FROM colors 
    LEFT OUTER JOIN (SELECT c FROM chars WHERE INSTR(colors.name, chars.c) <> 0) c ON TRUE;
  -- ERROR 1054 (42S22): Unknown column 'colors.name' in 'where clause'
 
  -- Now with LATERAL
  SELECT name, c
  FROM colors 
    LEFT OUTER JOIN LATERAL (SELECT c FROM chars WHERE INSTR(colors.name, chars.c) <> 0) c ON TRUE;

Result:

For more information, see SQL Server to MariaDB Migration.