How to Use Lateral Derived Table in MySQL 8

In MySQL 8, you can use LATERAL derived tables to reference columns from preceding tables in the same FROM clause. Use LATERAL before a subquery to allow it to access columns from tables listed earlier in the FROM clause, enabling row-wise operations.

Syntax

The LATERAL keyword is placed before a derived table (a subquery in the FROM clause) to allow that subquery to refer to columns from tables that appear earlier in the same FROM clause.

Basic syntax:

SELECT columns
FROM table1
JOIN LATERAL (
  SELECT ... FROM table2 WHERE table2.col = table1.col
) AS alias
ON true;

Explanation:

• table1: The main table in the query.
• LATERAL: Allows the subquery to use columns from table1.
• table2: The table inside the lateral subquery.
• alias: Required alias for the derived table.
• ON true: Used to join lateral subquery results without a specific join condition.

sql

SELECT t1.id, t1.name, t2.max_score
FROM students t1
JOIN LATERAL (
  SELECT MAX(score) AS max_score
  FROM exams
  WHERE exams.student_id = t1.id
) AS t2 ON true;

Example

This example shows how to get each student's highest exam score using a lateral derived table. The lateral subquery accesses the students table's id to filter exams for each student.

sql

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE exams (
  id INT PRIMARY KEY,
  student_id INT,
  score INT
);

INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO exams VALUES (1, 1, 85), (2, 1, 90), (3, 2, 78);

SELECT t1.id, t1.name, t2.max_score
FROM students t1
JOIN LATERAL (
  SELECT MAX(score) AS max_score
  FROM exams
  WHERE exams.student_id = t1.id
) AS t2 ON true;

Output

id | name  | max_score
---|-------|----------
1  | Alice | 90
2  | Bob   | 78

Common Pitfalls

Common mistakes when using LATERAL include:

• Forgetting to add the LATERAL keyword before the derived table, which causes the subquery to not access outer table columns.
• Not providing an alias for the lateral derived table, which is required.
• Using JOIN without ON true or a proper join condition, which can cause syntax errors.

Wrong example (missing LATERAL):

SELECT t1.id, t1.name, t2.max_score
FROM students t1
JOIN (
  SELECT MAX(score) AS max_score
  FROM exams
  WHERE exams.student_id = t1.id
) AS t2 ON true;

This will cause an error because t1.id is not visible inside the subquery.

Corrected example:

SELECT t1.id, t1.name, t2.max_score
FROM students t1
JOIN LATERAL (
  SELECT MAX(score) AS max_score
  FROM exams
  WHERE exams.student_id = t1.id
) AS t2 ON true;

Quick Reference

LATERAL Derived Table Quick Tips:

• Use LATERAL before a subquery in FROM to access outer tables.
• Always give the lateral subquery an alias.
• Use JOIN LATERAL ... ON true to join without a condition.
• Useful for row-wise computations or filtering based on outer table columns.

Key Takeaways

Use LATERAL before a derived table to let it access columns from preceding tables in the FROM clause.

Always provide an alias for the lateral derived table.

Use JOIN LATERAL ... ON true to join lateral subqueries without explicit join conditions.

Lateral derived tables enable row-wise subqueries that depend on outer query columns.

Forgetting LATERAL or alias causes errors or unexpected behavior.