📋

Description

This error occurs when a `LATERAL` derived table or subquery attempts to use a `JOIN` type other than `INNER JOIN` or `LEFT JOIN`. MySQL restricts `LATERAL` references to these specific join types to ensure proper evaluation of correlated subqueries.

💬

Error Message

INNER or LEFT JOIN must be used for LATERAL references made by '%s'

🔍

Known Causes

3 known causes

⚠️

Unsupported JOIN Type

You have used a `JOIN` type (e.g., `RIGHT JOIN`, `FULL JOIN`, or `CROSS JOIN` without an `ON` clause) that is explicitly forbidden with `LATERAL` derived tables.

⚠️

Misconfigured LATERAL Query

The structure of your `LATERAL` derived table or subquery implicitly requires a join type that is not `INNER` or `LEFT`, leading to the restriction.

⚠️

Semantic Mismatch with RIGHT JOIN

Attempting to use `LATERAL` with `RIGHT JOIN` can create a semantic conflict, as `LATERAL`'s evaluation order and `RIGHT JOIN`'s row preservation logic are incompatible.

🛠️

Solutions

3 solutions available

1. Use INNER JOIN for LATERAL References easy

Replace the invalid join type with INNER JOIN when referencing a derived table or subquery with LATERAL.

1

Identify the query that is causing the error. Look for `LATERAL` keywords used with join types other than `INNER` or `LEFT`.

2

Modify the query to use `INNER JOIN` for the `LATERAL` reference. If the intent was to include rows from the left table even if the lateral subquery returns no rows, then `LEFT JOIN` should be used instead.

SELECT ... FROM table_a CROSS JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table;

-- Corrected to INNER JOIN:
SELECT ... FROM table_a INNER JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table ON TRUE;

3

Re-run the query to confirm the error is resolved.

2. Use LEFT JOIN for Optional LATERAL References easy

Employ LEFT JOIN when the lateral subquery might not return any rows for a given row in the preceding table.

1

Locate the SQL statement triggering error 3668. Pay close attention to `LATERAL` clauses.

2

If the intention is to preserve rows from the preceding table even if the `LATERAL` subquery yields no results, change the join type to `LEFT JOIN`. Note that `CROSS JOIN LATERAL` is equivalent to `INNER JOIN LATERAL`.

SELECT ... FROM table_a CROSS JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table;

-- Corrected to LEFT JOIN:
SELECT ... FROM table_a LEFT JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table ON TRUE;

3

Execute the modified query and verify that MySQL Error 3668 no longer occurs.

3. Review and Simplify LATERAL Subquery Logic medium

Ensure the lateral subquery's `WHERE` clause correctly correlates with the preceding table and that the join type is appropriate.

1

Examine the `LATERAL` subquery and its correlation condition. Ensure that the columns used for correlation (e.g., `table_b.a_id = table_a.id`) are valid and accurately represent the desired relationship.

2

Re-evaluate the join type used with the `LATERAL` keyword. If the subquery is expected to always return at least one row for each row in the preceding table, `INNER JOIN LATERAL` (or `CROSS JOIN LATERAL`) is appropriate. If there's a possibility of no matching rows, `LEFT JOIN LATERAL` is necessary.

SELECT ...
FROM table_a
-- Incorrect join type or correlation
-- WRONG: table_a RIGHT JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table ON TRUE;

-- Corrected example:
SELECT ...
FROM table_a
INNER JOIN LATERAL (SELECT ... FROM table_b WHERE table_b.a_id = table_a.id) AS derived_table ON TRUE;

3

Test the query thoroughly with various data scenarios to ensure the `LATERAL` subquery behaves as expected and the join type is correctly applied.

🔗

Related Errors

5 related errors

MySQL Error 3668: Invalid LATERAL JOIN Type