More Than a Query Language: SQL in the 21 st Century @MarkusWinand - PowerPoint PPT Presentation

SQL:1999 — LATERAL Select-list sub-queries must be scalar [0] : (return no more than one domain value) More than 
 SELECT … ✗ one row? } , (SELECT column_1 ⇒ Runtime error! , column_2 FROM t1 WHERE t1.x = t2.y ) AS c More than 
 one column? FROM t2 ⇒ Syntax error …

SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: SELECT … , ldt.* FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt …

SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt …

SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt Still … “correlated”

SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 Regular join WHERE t1.x = t2.y semantics ) AS ldt Still … “correlated”

SQL:1999 — LATERAL


 SQL:1999 — LATERAL ‣ Top-N per group 
 inside a lateral derived table 
 FETCH FIRST (or LIMIT , TOP ) 
 applies per row from left tables.


 SQL:1999 — LATERAL ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ) derived_table


 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ) derived_table https://use-the-index-luke.com/sql/partial-results/top-n-queries


 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ‣ Also useful to find most recent ) derived_table news from several subscribed topics (“multi-source top-N”). https://use-the-index-luke.com/sql/partial-results/top-n-queries


 
 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ‣ Also useful to find most recent ) derived_table news from several subscribed topics (“multi-source top-N”). https://use-the-index-luke.com/sql/partial-results/top-n-queries ‣ Table function arguments 
 FROM t ( TABLE often implies LATERAL ) 
 JOIN TABLE (your_func(t.c))

SQL:1999 — LATERAL 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 MySQL 8.0.14 PostgreSQL 9.3 SQLite 1.0 DB2 LUW 9.1 Oracle 11gR1 [0] 12cR1 SQL Server 2005 [1] [0] Undocumented. Requires setting trace event 22829. [1] LATERAL is not supported as of SQL Server 2016 but [CROSS|OUTER] APPLY can be used for the same effect.

1999 2003

http://www.acm.org:80/sigmod/record/issues/0206/standard.pdf (via Wayback machine)

SQL:2003 — Schemaless & Analytical Schemaless ‣ Introduced XML ‣ Non-uniform 
 documents in 
 a single column

SQL:2003 — Schemaless & Analytical Schemaless ‣ Introduced XML ‣ Non-uniform 
 documents in 
 a single column Later: ‣ JSON added with SQL:2016 ‣ Proprietary JSON support: ‣ 2012: PostgreSQL ‣ 2014: Oracle ‣ 2015: MySQL ‣ 2016: SQL Server

SQL:2003 — Schemaless & Analytical Schemaless Analytical ‣ Introduced XML ‣ Introduced 
 ‣ Non-uniform 
 window functions ‣ Accessing other rows 
 documents in 
 a single column of the current result Later: ‣ JSON added with SQL:2016 ‣ Proprietary JSON support: ‣ 2012: PostgreSQL ‣ 2014: Oracle ‣ 2015: MySQL ‣ 2016: SQL Server

SQL:2003 — Schemaless & Analytical Schemaless Analytical ‣ Introduced XML ‣ Introduced 
 ‣ Non-uniform 
 window functions ‣ Accessing other rows 
 documents in 
 a single column of the current result Later: Later: ‣ JSON added with SQL:2016 ‣ Extended in SQL:2011 ‣ Proprietary JSON support: ‣ Popular among “New SQLs” ‣ 2012: PostgreSQL ‣ 2013: BigQuery, Hive ‣ 2014: Oracle ‣ 2014: Impala ‣ 2015: MySQL ‣ 2015: Spark SQL ‣ 2016: SQL Server ‣ 2016: NuoDB, MemSQL, Cockroach DB, VoltDB

SQL:2003 — Analytical id value SELECT id, value 1 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

SQL:2003 — Analytical id value bal SELECT id, value 1 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

SQL:2003 — Analytical id value bal SELECT id, value 1 +10 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

SQL:2003 — Analytical id value bal SELECT id, value 1 +10 +10 2 +20 +30 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

SQL:2003 — Analytical 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 10.2 8.0 MySQL PostgreSQL 8.4 SQLite 1.0 3.25.0 DB2 LUW 7.0 Oracle 8i SQL Server 2005 [0] 2012 [0] Without framing

OVER SQL:2011 groups option

SQL:2011 — OVER GROUPS option ORDER BY x <frame unit> between 1 preceding and 1 following

SQL:2011 — OVER GROUPS option rows, ORDER BY x range <frame unit> between 1 preceding and 1 following

SQL:2011 — OVER GROUPS option rows, ORDER BY x range <frame unit> between 1 preceding and 1 following x 1 CURRENT ROW 3 3.5 3.5 4

SQL:2011 — OVER GROUPS option rows, ORDER BY x range <frame unit> between 1 preceding and 1 following x 1 CURRENT ROW 3 rows 
 count(*) 3.5 3.5 4

SQL:2011 — OVER GROUPS option rows, ORDER BY x range <frame unit> between 1 preceding and 1 following x 1 CURRENT ROW 3 rows 
 count(*) 3.5 range 3.5 x between current_row.x - 1 
 and current_row.x + 1 4


 SQL:2011 — OVER GROUPS option rows, New in 
 ORDER BY x range SQL:2011 
 <frame unit> between 1 preceding groups and 1 following x 1 CURRENT ROW 3 rows 
 count(*) 3.5 range 3.5 x between current_row.x - 1 
 and current_row.x + 1 4


 SQL:2011 — OVER GROUPS option rows, New in 
 ORDER BY x range SQL:2011 
 <frame unit> between 1 preceding groups and 1 following x 1 CURRENT ROW 3 rows 
 groups count(*) 3.5 count(distinct x) range 3.5 x between current_row.x - 1 
 and current_row.x + 1 4

SQL:2011 — OVER GROUPS option 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 MySQL PostgreSQL 11 3.28.0 SQLite DB2 LUW Oracle SQL Server

OVER SQL:2003 frame exclusion

SQL:2003 — frame exclusion OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] )

SQL:2003 — frame exclusion default OVER (ORDER BY … BETWEEN … exclude [ no others | current row no others | group | ties ] )

SQL:2003 — frame exclusion default OVER (ORDER BY … BETWEEN … exclude [ no others | current row no others | group | ties ] ) x 1 2 2 2 3