This topic introduces the basic concepts and limitations of lateral derived tables, and provides relevant examples.
Introduction to derived tables
A derived table is a subquery used in the FROM clause. The result set generated by this subquery is temporarily used as a table in the outer query. Derived tables are typically used for grouping and aggregating data, or for creating datasets that meet specific conditions for further use in the main query.
A lateral derived table is a special type of derived table. By using the LATERAL keyword, you can specify a derived table so that it can reference fields from other tables or derived tables defined earlier in the same FROM clause. This allows the subquery within the derived table to depend on tables defined in the same FROM clause, thereby accessing column values from those tables.
The main characteristic of lateral derived tables is that they can reference columns of tables defined before them in the FROM clause; regular derived tables do not have this capability.
Limitations on using lateral derived tables (LATERAL keyword)
- The
LATERALkeyword must be used in theFROMclause. It can be placed in a comma-separated list of tables or in a join expression (such asJOIN,INNER JOIN,CROSS JOIN,LEFT [OUTER] JOIN,RIGHT [OUTER] JOIN). - When
LATERALis used in aJOINstatement and references a field of the left table, the allowed join types includeINNER JOIN,CROSS JOIN, andLEFT [OUTER] JOIN. - If
LATERALis used in aJOINstatement and references a field of the right table, the valid join operations areINNER JOIN,CROSS JOIN, andRIGHT [OUTER] JOIN. - When using an aggregate function in a lateral derived table, that aggregate function cannot directly depend on the outer query where the
FROMclause containing the lateral derived table is located. That is, aggregate functions inside a lateral derived table cannot directly reference the results of the outer query.
Usage of the LATERAL keyword
SELECT select_list
FROM table_name1,
LATERAL (SELECT select_list
FROM table_name2
WHERE table_name2.col_name = table_name1.col_name) AS lateral_derived_table_name
[...];
The parameters are described as follows:
Parameter |
Description |
|---|---|
| select_list | The list of columns to retrieve, which can be column names, expressions, aggregate functions, etc. Multiple columns can be separated with commas. |
| table_name1 | The primary table to be queried. |
| LATERAL | Specifies a laterally correlated subquery (a lateral derived table). |
| table_name2 | The secondary table, which is referenced in the LATERAL subquery to provide additional information related to each row of table_name1. |
| lateral_derived_table_name | The alias of the derived table, which is used for reference in subsequent queries. |
| [...] | Optional query clauses, such as the WHERE clause. |
Examples
Create test tables and add test data
Create the
studentstable.CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );Insert 3 rows of data into the
studentstable.INSERT INTO students (name, age) VALUES ('name1', 20), ('name2', 22), ('name3', 21);Create the
scorestable.CREATE TABLE scores ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, subject VARCHAR(50) NOT NULL, score DECIMAL(5, 2), FOREIGN KEY (student_id) REFERENCES students(id) );Insert 9 rows of data into the
scorestable.INSERT INTO scores (student_id, subject, score) VALUES (1, 'A', 86.5), (1, 'B', 90.0), (1, 'C', 91.5), (2, 'A', 86.0), (2, 'B', 92.0), (2, 'C', 89.5), (3, 'A', 93.0), (3, 'B', 92.5), (3, 'C', 85.0);
Query the test data
Retrieve each student's name, average score, and highest score from the students and scores tables.
Q1: Use multiple subqueries to obtain the required data. Perform two independent grouping and aggregation operations on the scores table, scanning it twice to calculate the average score and the highest score respectively. Then, join the results with the students table using the WHERE clause.
SELECT st.name, sc.avg_score, scs.max_score
FROM students st,
(SELECT student_id, AVG(score) avg_score
FROM scores
GROUP BY student_id) sc,
(SELECT student_id, MAX(score) max_score
FROM scores
GROUP BY student_id) scs
WHERE sc.student_id = st.id
AND scs.student_id = st.id;
The query result is as follows:
+-------+-----------+-----------+
| name | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 | 91.50 |
| name2 | 89.166667 | 92.00 |
| name3 | 90.166667 | 93.00 |
+-------+-----------+-----------+
3 rows in set
Q2: Use the LATERAL keyword to obtain the required data. By using the LATERAL keyword (lateral derived table), you can calculate each student's average score and highest score in a single SQL statement, while scanning the scores table only once for each student's ID.
SELECT st.name, ld_tbl.avg_score, ld_tbl.max_score
FROM students st,
LATERAL (SELECT AVG(score) avg_score, MAX(score) max_score
FROM scores sc
WHERE sc.student_id = st.id) ld_tbl;
The query result is as follows:
+-------+-----------+-----------+
| name | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 | 91.50 |
| name2 | 89.166667 | 92.00 |
| name3 | 90.166667 | 93.00 |
+-------+-----------+-----------+
3 rows in set
Compared to Q1, Q2 may offer higher performance when processing large datasets because it avoids repeated scans of the scores table. The Q2 approach is also clearer and more concise, making it easier to understand and maintain.