SQL; 2003 permite una subconsulta en la cláusula FROM que tiene como prefijo la palabra clave lateral para acceder a los atributos de las tablas anteriores o subconsultas en la cláusula FROM.
Muy pocas implementaciones de SQL en la actualidad soportan el uso de esta palabra clave. Una de ellas es IBM DB2.
SQL Server y MySQL ofrecen alternativas para la clausula LATERAL.
A partir de la versión 12c de la base de datos Oracle , la palabra clave lateral es soportada y se utiliza para designar subconsulta como una vista lateral en línea.
Según mis validaciones, a nivel del optimizador de la base de datos, no genera un cambio en la forma en como se PARSEA la instrucción, pero si, estéticamente se ve mucho mejor.
A continuación encontrarán la forma tradicional de como escribir la consulta y la nueva sintaxis, utilizando la clausula LATERAL.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> explain plan for
select last_name, first_name, department_name from employees e, departments d
where e.department_id=d.department_id;
Explained.
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 4254995515
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3604 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 106 | 3604 | 0 (0)| 00:00:01 |
| 3 | VIEW | index$_join$_001 | 107 | 1926 | 0 (0)| 00:00:01 |
|* 4 | HASH JOIN | | | | | |
| 5 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 1926 | 0 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 1926 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(ROWID=ROWID)
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
21 rows selected.
SQL>
SQL>
SQL> explain plan for
2 select last_name, first_name, department_name
3 from hr.employees e, lateral ( select * from hr.departments d where e.department_id=d.department_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 4254995515
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3604 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 106 | 3604 | 0 (0)| 00:00:01 |
| 3 | VIEW | index$_join$_001 | 107 | 1926 | 0 (0)| 00:00:01 |
|* 4 | HASH JOIN | | | | | |
| 5 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 1926 | 0 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 1926 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(ROWID=ROWID)
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
21 rows selected.
