still using windows 3.1?...‣literate sql organize sql code to improve maintainability ‣assign...
TRANSCRIPT
![Page 1: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/1.jpg)
Still using Windows 3.1? So why stick with
SQL-92?
@ModernSQL - https://modern-sql.com/ @MarkusWinand
![Page 2: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/2.jpg)
SQL:1999
![Page 3: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/3.jpg)
WITH (Common Table Expressions)
![Page 4: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/4.jpg)
Understand
this first
WITH (non-recursive) The ProblemNested queries are hard to read:
SELECT…FROM(SELECT…FROMt1JOIN(SELECT…FROM…)aON(…))bJOIN(SELECT…FROM…)cON(…)
![Page 5: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/5.jpg)
Then this...
WITH (non-recursive) The ProblemNested queries are hard to read:
SELECT…FROM(SELECT…FROMt1JOIN(SELECT…FROM…)aON(…))bJOIN(SELECT…FROM…)cON(…)
![Page 6: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/6.jpg)
Then this...
WITH (non-recursive) The ProblemNested queries are hard to read:
SELECT…FROM(SELECT…FROMt1JOIN(SELECT…FROM…)aON(…))bJOIN(SELECT…FROM…)cON(…)
![Page 7: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/7.jpg)
Finally the first line makes sense
WITH (non-recursive) The ProblemNested queries are hard to read:
SELECT…FROM(SELECT…FROMt1JOIN(SELECT…FROM…)aON(…))bJOIN(SELECT…FROM…)cON(…)
![Page 8: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/8.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
Keyword
WITH (non-recursive) Since SQL:1999
![Page 9: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/9.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
Name of CTE and (here optional) column names
WITH (non-recursive) Since SQL:1999
![Page 10: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/10.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
Definition
WITH (non-recursive) Since SQL:1999
![Page 11: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/11.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
Introduces another CTE
Don't repeat WITH
WITH (non-recursive) Since SQL:1999
![Page 12: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/12.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
WITH (non-recursive) Since SQL:1999
![Page 13: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/13.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)
May refer toprevious CTEs
WITH (non-recursive) Since SQL:1999
![Page 14: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/14.jpg)
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)AS(SELECT…FROM…)
SELECT…FROMbJOINcON(…)
Third CTE
WITH (non-recursive) Since SQL:1999
![Page 15: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/15.jpg)
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)AS(SELECT…FROM…)
SELECT…FROMbJOINcON(…)
No comma!
WITH (non-recursive) Since SQL:1999
![Page 16: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/16.jpg)
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)AS(SELECT…FROM…)
SELECT…FROMbJOINcON(…)
Main query
WITH (non-recursive) Since SQL:1999
![Page 17: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/17.jpg)
CTEs are statement-scoped views:
WITHa(c1,c2,c3)AS(SELECTc1,c2,c3FROM…),
b(c4,…)AS(SELECTc4,…FROMt1JOINaON(…)),
c(…)AS(SELECT…FROM…)
SELECT…FROMbJOINcON(…)
Read top down
WITH (non-recursive) Since SQL:1999
![Page 18: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/18.jpg)
‣ Literate SQL
Organize SQL code toimprove maintainability
‣ Assign column names
to tables produced by valuesor unnest.
‣ Overload tables (for testing)
with queries hide tablesof the same name.
Use-CasesWITH (non-recursive)
https://modern-sql.com/use-case/literate-sql
https://modern-sql.com/use-case/naming-unnamed-columns
https://modern-sql.com/use-case/unit-tests-on-transient-data
![Page 19: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/19.jpg)
WITH are the "private methods" of SQL
WITH is a prefix to SELECT
WITH queries are only visible in the SELECT they precede
WITH in detail: https://modern-sql.com/feature/with
WITH (non-recursive) In a Nutshell
![Page 20: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/20.jpg)
AvailabilityWITH (non-recursive)19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 10.2 MariaDB8.0 MySQL
8.4 PostgreSQL3.8.3[0] SQLite
7.0 DB2 LUW9iR2 Oracle
2005[1] SQL Server[0]Only for top-level SELECT statements[1]Only allowed at the very begin of a statement. E.g. WITH...INSERT...SELECT.
![Page 21: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/21.jpg)
WITHRECURSIVE (Common Table Expressions)
![Page 22: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/22.jpg)
(This page is intentionally left blank)
WITHRECURSIVE The Problem
![Page 23: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/23.jpg)
CREATETABLEt(idNUMERICNOTNULL,parent_idNUMERIC,…PRIMARYKEY(id))
Coping with hierarchies in the Adjacency List Model[0]
WITHRECURSIVE The Problem
[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”
![Page 24: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/24.jpg)
SELECT*FROMtASd0LEFTJOINtASd1ON(d1.parent_id=d0.id)LEFTJOINtASd2ON(d2.parent_id=d1.id)
Coping with hierarchies in the Adjacency List Model[0]
WITHRECURSIVE The Problem
WHEREd0.id=?
[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”
![Page 25: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/25.jpg)
SELECT*FROMtASd0LEFTJOINtASd1ON(d1.parent_id=d0.id)LEFTJOINtASd2ON(d2.parent_id=d1.id)
Coping with hierarchies in the Adjacency List Model[0]
WITHRECURSIVE The Problem
WHEREd0.id=?
[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”
![Page 26: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/26.jpg)
SELECT*FROMtASd0LEFTJOINtASd1ON(d1.parent_id=d0.id)LEFTJOINtASd2ON(d2.parent_id=d1.id)
Coping with hierarchies in the Adjacency List Model[0]
WITHRECURSIVE The Problem
WHEREd0.id=?
[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”
![Page 27: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/27.jpg)
SELECT*FROMtASd0LEFTJOINtASd1ON(d1.parent_id=d0.id)LEFTJOINtASd2ON(d2.parent_id=d1.id)
WITHRECURSIVE Since SQL:1999
WHEREd0.id=?
WITHRECURSIVEd(id,parent,…)AS(SELECTid,parent,…FROMtblWHEREid=?UNIONALLSELECTid,parent,…FROMdJOINtblON(tbl.parent=d.id))SELECT*FROMsubtree
![Page 28: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/28.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
Keyword
Since SQL:1999WITHRECURSIVE
![Page 29: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/29.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
Column list mandatory here
Since SQL:1999WITHRECURSIVE
![Page 30: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/30.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
Executed first
Since SQL:1999WITHRECURSIVE
![Page 31: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/31.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
Result sent there
Since SQL:1999WITHRECURSIVE
![Page 32: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/32.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
Result visible twice
Since SQL:1999WITHRECURSIVE
![Page 33: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/33.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
Once it becomes
part of the final result
Since SQL:1999WITHRECURSIVE
![Page 34: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/34.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
Second leg of UNION is executed
Since SQL:1999WITHRECURSIVE
![Page 35: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/35.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
Result sent there again
Since SQL:1999WITHRECURSIVE
![Page 36: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/36.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
It's a loop!
Since SQL:1999WITHRECURSIVE
![Page 37: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/37.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
It's a loop!
Since SQL:1999WITHRECURSIVE
![Page 38: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/38.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
It's a loop!
Since SQL:1999WITHRECURSIVE
![Page 39: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/39.jpg)
Recursive common table expressions may refer to themselves in a leg of a UNION[ALL]:
WITHRECURSIVEcte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<3)SELECT*FROMcte
n---123(3rows)
n=3 doesn't matchLoop terminates
Since SQL:1999WITHRECURSIVE
![Page 40: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/40.jpg)
AvailabilityWITHRECURSIVE19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 10.2 MariaDB8.0 MySQL
8.4 PostgreSQL3.8.3[0] SQLite
7.0 DB2 LUW11gR2 Oracle
2005 SQL Server[0]Only for top-level SELECT statements
![Page 41: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/41.jpg)
GROUPINGSETS
![Page 42: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/42.jpg)
Only one GROUPBY operation at a time:
GROUPINGSETS Before SQL:1999
SELECTyear,month,sum(revenue)FROMtblGROUPBYyear,month
Monthly revenue Yearly revenue
SELECTyear,sum(revenue)FROMtblGROUPBYyear
![Page 43: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/43.jpg)
GROUPINGSETS Before SQL:1999SELECTyear,month,sum(revenue)FROMtblGROUPBYyear,month
SELECTyear,sum(revenue)FROMtblGROUPBYyear
![Page 44: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/44.jpg)
GROUPINGSETS Before SQL:1999SELECTyear,month,sum(revenue)FROMtblGROUPBYyear,month
SELECTyear,sum(revenue)FROMtblGROUPBYyear
UNIONALL
,null
![Page 45: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/45.jpg)
GROUPINGSETS Since SQL:1999SELECTyear,month,sum(revenue)FROMtblGROUPBYyear,month
SELECTyear,sum(revenue)FROMtblGROUPBYyear
UNIONALL
,null
SELECTyear,month,sum(revenue)FROMtblGROUPBYGROUPINGSETS((year,month),(year))
![Page 46: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/46.jpg)
GROUPINGSETS are multiple GROUPBYs in one go
() (empty parenthesis) build a group over all rows
GROUPING (function) disambiguates the meaning of NULL(was the grouped data NULL or is this column not currently grouped?)
Permutations can be created using ROLLUP and CUBE(ROLLUP(a,b,c) = GROUPINGSETS((a,b,c),(a,b),(a),())
GROUPINGSETS In a Nutshell
![Page 47: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/47.jpg)
GROUPINGSETS Availability19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1[0] MariaDB5.0[1] MySQL
9.5 PostgreSQLSQLite
5 DB2 LUW9iR1 Oracle
2008 SQL Server[0]Only ROLLUP (properitery syntax).[1]Only ROLLUP (properitery syntax). GROUPING function since MySQL 8.0.
![Page 48: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/48.jpg)
SQL:2003
![Page 49: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/49.jpg)
FILTER
![Page 50: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/50.jpg)
FILTER Before we start
In SQL, most aggregate functions* drop null arguments
prior to the aggregation.
*Exceptions: Aggregate functions that return structured data: array_agg, json_objectagg, json_arrayagg, xmlagg
See: http://modern-sql.com/concept/null#aggregates
![Page 51: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/51.jpg)
SELECTYEAR,SUM(CASEWHENMONTH=1THENrevenueELSE0END)JAN,SUM(CASEWHENMONTH=2THENrevenueEND)FEB,…FROMsalesGROUPBYYEAR
FILTER The ProblemPivot table: Years on the Y axis, month on X:
![Page 52: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/52.jpg)
SELECTYEAR,SUM(CASEWHENMONTH=1THENrevenueELSE0END)JAN,SUM(CASEWHENMONTH=2THENrevenueEND)FEB,…FROMsalesGROUPBYYEAR
FILTER The ProblemPivot table: Years on the Y axis, month on X:
Optional:ELSE null is default
![Page 53: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/53.jpg)
SELECTYEAR,SUM(CASEWHENMONTH=1THENrevenueELSE0END)JAN,SUM(CASEWHENMONTH=2THENrevenueEND)FEB,…FROMsalesGROUPBYYEAR
FILTER The ProblemPivot table: Years on the Y axis, month on X:
![Page 54: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/54.jpg)
SELECTYEAR,SUM(revenue)FILTER(WHEREMONTH=1)JAN,SUM(revenue)FILTER(WHEREMONTH=2)FEB,…FROMsalesGROUPBYYEAR;
FILTER Since SQL:2003SQL:2003 allows FILTER(WHERE…) after aggregates:
![Page 55: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/55.jpg)
FILTER Since SQL:2003
Year
2016
2016
2016
2016
2016
Month
1
2
3
...
12
Revenue
1
23
345
...
1234
Year
2016
Jan
1
Feb
23
Mar
345
...
...
Dec
1234
SUM(…) FILTE
R(WHERE …)
SUM(…)
FILTER(WHER
E month=2)
SUM(rev
enue) FILTER(WHERE
month=3)
SUM(rev
enue) FILTER(WHERE mo
nth=…)
SUM(rev
enue) FILTER(WHERE month=
12)
Pivot in SQL1. Use GROUP BY
to combine rows2. Use FILTER to pick
rows per column
See: http://modern-sql.com/use-case/pivot
![Page 56: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/56.jpg)
FILTER Availability19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 MariaDBMySQL
9.4 PostgreSQLSQLite
DB2 LUWOracleSQL Server
![Page 57: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/57.jpg)
BOOLEANTests
![Page 58: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/58.jpg)
Before we start
SQL uses a three-valued logic. Boolean values are either
true, false or unknown(=null).
See: http://modern-sql.com/concept/three-valued-logic
BOOLEANAggregates
![Page 59: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/59.jpg)
BOOLEANTests
Similar to isnull, there are tests for each Boolean value(of which there are three: true, false, unknown/null)
IS[NOT][TRUE|FALSE|UNKNOWN]
Since SQL:2003
![Page 60: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/60.jpg)
CREATETABLEprices(…valid_fromDATENOTNULL,valid_toDATE,--null:openend…CHECK(valid_from<valid_to),);
WHEREvalid_from<CURRENT_DATEAND(valid_to<=CURRENT_DATE)ISNOTFALSE
BOOLEANTests Since SQL:2003
![Page 61: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/61.jpg)
CREATETABLEprices(…valid_fromDATENOTNULL,valid_toDATE,--null:openend…CHECK(valid_from<valid_to),);
WHEREvalid_from<CURRENT_DATEAND(valid_to<=CURRENT_DATE)ISNOTFALSE
BOOLEANTests Since SQL:2003
UNKNOWN ifVALID_TO is NULL
![Page 62: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/62.jpg)
CREATETABLEprices(…valid_fromDATENOTNULL,valid_toDATE,--null:openend…CHECK(valid_from<valid_to),);
WHEREvalid_from<CURRENT_DATEAND(valid_to<=CURRENT_DATE)ISNOTFALSE
BOOLEANTests Since SQL:2003
UNKNOWN ifVALID_TO is NULL Takes TRUE and
UNKNOWN
![Page 63: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/63.jpg)
BOOLEANTests Since SQL:200319
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 MariaDB5.0.51a MySQL8.3 PostgreSQL3.5.7[0] SQLite
DB2 LUWOracleSQL Server
[0]No IS [NOT] UNKNOWN. Use IS [NOT] NULL instead.
![Page 64: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/64.jpg)
OVER and
PARTITIONBY
![Page 65: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/65.jpg)
OVER (PARTITION BY) The ProblemTwo distinct concepts could not be used independently:
‣Merge rows with the same key properties
‣ GROUPBY to specify key properties
‣ DISTINCT to use full row as key
‣ Aggregate data from related rows ‣ Requires GROUPBY to segregate the rows
‣ COUNT, SUM, AVG, MIN, MAX to aggregate grouped rows
![Page 66: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/66.jpg)
SELECTc1,SUM(c2)totFROMtGROUPBYc1
OVER (PARTITION BY) The Problem
Yes ⇠
Mer
ge ro
ws ⇢
No
No ⇠ Aggregate ⇢ Yes
SELECTc1,c2FROMt
SELECTDISTINCTc1,c2FROMt
SELECTc1,c2FROMtJOIN()taON(t.c1=ta.c1)
SELECTc1,SUM(c2)totFROMtGROUPBYc1
,tot
![Page 67: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/67.jpg)
SELECTc1,SUM(c2)totFROMtGROUPBYc1
OVER (PARTITION BY) The Problem
Yes ⇠
Mer
ge ro
ws ⇢
No
No ⇠ Aggregate ⇢ Yes
SELECTc1,c2FROMt
SELECTDISTINCTc1,c2FROMt
SELECTc1,c2FROMtJOIN()taON(t.c1=ta.c1)
SELECTc1,SUM(c2)totFROMtGROUPBYc1
,tot
![Page 68: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/68.jpg)
SELECTc1,SUM(c2)totFROMtGROUPBYc1
OVER (PARTITION BY) Since SQL:2003
Yes ⇠
Mer
ge ro
ws ⇢
No
No ⇠ Aggregate ⇢ Yes
SELECTc1,c2FROMt
SELECTDISTINCTc1,c2FROMt
SELECTc1,c2FROMt
FROMt
,SUM(c2)OVER(PARTITIONBYc1)
![Page 69: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/69.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 70: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/70.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 71: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/71.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 72: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/72.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 73: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/73.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 74: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/74.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 600022 1000 600022 1000 6000333 1000 6000333 1000 6000333 1000 6000
OVER (PARTITION BY) How it works
![Page 75: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/75.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 100022 1000 200022 1000 2000333 1000 3000333 1000 3000333 1000 3000
OVER (PARTITION BY) How it works
)PARTITIONBYdep
![Page 76: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/76.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 100022 1000 200022 1000 2000333 1000 3000333 1000 3000333 1000 3000
OVER (PARTITION BY) How it works
)PARTITIONBYdep
![Page 77: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/77.jpg)
SELECTdep,salary,SUM(salary)OVER()FROMemp
dep salary ts1 1000 100022 1000 200022 1000 2000333 1000 3000333 1000 3000333 1000 3000
OVER (PARTITION BY) How it works
)PARTITIONBYdep
![Page 78: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/78.jpg)
OVER and
ORDERBY(Framing & Ranking)
![Page 79: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/79.jpg)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
OVER (ORDER BY) The Problem
SELECTid,value,FROMtransactionst
![Page 80: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/80.jpg)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
OVER (ORDER BY) The Problem
SELECTid,value,FROMtransactionst
![Page 81: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/81.jpg)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
OVER (ORDER BY) The Problem
SELECTid,value,
(SELECTSUM(value)FROMtransactionst2WHEREt2.id<=t.id)
FROMtransactionst
Range segregation (<=)not possible with
GROUP BY orPARTITION BY
![Page 82: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/82.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
![Page 83: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/83.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYid
![Page 84: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/84.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDING
![Page 85: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/85.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 86: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/86.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 87: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/87.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 88: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/88.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 89: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/89.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 90: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/90.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 91: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/91.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +30
22 3 -10 +20
333 4 +50 +70
333 5 -30 +40
333 6 -20 +20
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
![Page 92: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/92.jpg)
OVER (ORDER BY) Since SQL:2003
SELECTid,value,
FROMtransactionst
SUM(value)OVER(
)
acnt id value balance
1 1 +10 +10
22 2 +20 +20
22 3 -10 +10
333 4 +50 +50
333 5 -30 +20
333 6 -20 .0
ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
PARTITIONBYacnt
![Page 93: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/93.jpg)
OVER (ORDER BY) Since SQL:2003With OVER(ORDERBYn) a new type of functions make sense:
n ROW_NUMBER RANK DENSE_RANK PERCENT_RANK CUME_DIST1 1 1 1 0 0.252 2 2 2 0.33… 0.753 3 2 2 0.33… 0.754 4 4 3 1 1
![Page 94: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/94.jpg)
‣ Aggregates without GROUPBY
‣ Running totals, moving averages
‣ Ranking‣ Top-N per Group
‣ Avoiding self-joins
[… many more …]
Use Cases
SELECT*FROM(SELECTROW_NUMBER()OVER(PARTITIONBY…ORDERBY…)rn,t.*FROMt)numbered_tWHERErn<=3
AVG(…)OVER(ORDERBY…ROWSBETWEEN3PRECEDINGAND3FOLLOWING)moving_avg
OVER (SQL:2003)
![Page 95: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/95.jpg)
OVER may follow any aggregate function
OVER defines which rows are visible at each row
OVER() makes all rows visible at every row
OVER(PARTITIONBY …) segregates like GROUPBY
OVER(ORDERBY…BETWEEN) segregates using <, >
In a NutshellOVER (SQL:2003)
![Page 96: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/96.jpg)
OVER (SQL:2003) Availability19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 10.2 MariaDB8.0 MySQL
8.4 PostgreSQLSQLite
7.0 DB2 LUW8i Oracle
2005 SQL Server
![Page 97: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/97.jpg)
OVER (SQL:2003) Availability19
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 10.2 MariaDB8.0 MySQL
8.4 PostgreSQLSQLite
7.0 DB2 LUW8i Oracle
2005 SQL Server
Hive
ImpalaSpark
NuoDB
![Page 98: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/98.jpg)
SQL:2011
![Page 99: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/99.jpg)
OVER
![Page 100: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/100.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt
![Page 101: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/101.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
![Page 102: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/102.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
![Page 103: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/103.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
![Page 104: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/104.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
prevbalance … rn
50 … 190 … 270 … 330 … 4
![Page 105: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/105.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
prevbalance … rn
50 … 190 … 270 … 330 … 4
![Page 106: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/106.jpg)
WITHnumbered_tAS(SELECT*)
SELECTcurr.*,curr.balance-COALESCE(prev.balance,0)FROMnumbered_tcurrLEFTJOINnumbered_tprevON(curr.rn=prev.rn+1)
OVER (SQL:2011) The ProblemDirect access of other rows of the same window is not possible.
(E.g., calculate the difference to the previous rows)
currbalance … rn
50 … 190 … 270 … 330 … 4
FROMt,ROW_NUMBER()OVER(ORDERBYx)rn
prevbalance … rn
50 … 190 … 270 … 330 … 4
+50+40-20-40
![Page 107: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/107.jpg)
SELECT*,balance-COALESCE(LAG(balance)OVER(ORDERBYx),0)FROMt
Available functions:LEAD/LAGFIRST_VALUE/LAST_VALUENTH_VALUE(col,n)FROMFIRST/LASTRESPECT/IGNORENULLS
OVER (SQL:2011) Since SQL:2011SQL:2011 introduced LEAD, LAG, NTH_VALUE, … for that:
![Page 108: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/108.jpg)
OVER (LEAD, LAG, …) Since SQL:201119
9920
0120
0320
0520
0720
0920
1120
1320
1520
17
5.1 10.2[0] MariaDB8.0[0] MySQL
8.4[0] PostgreSQLSQLite
9.5[1] 11.1 DB2 LUW8i[1] 11gR2 Oracle
2012[1] SQL Server[0]No IGNORENULLS and FROMLAST[1]No NTH_VALUE
![Page 109: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/109.jpg)
System Versioning (Time Traveling)
![Page 110: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/110.jpg)
INSERTUPDATEDELETE
are DESTRUCTIVE
System Versioning The Problem
![Page 111: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/111.jpg)
ID Data start_ts end_ts1 X 10:00:00
UPDATE...SETDATA='Y'...
ID Data start_ts end_ts1 X 10:00:00 11:00:001 Y 11:00:00
DELETE...WHEREID=1
INSERT...(ID,DATA)VALUES(1,'X')
System Versioning Since SQL:2011
![Page 112: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/112.jpg)
ID Data start_ts end_ts1 X 10:00:00
UPDATE...SETDATA='Y'...
ID Data start_ts end_ts1 X 10:00:00 11:00:001 Y 11:00:00
DELETE...WHEREID=1
ID Data start_ts end_ts1 X 10:00:00 11:00:001 Y 11:00:00 12:00:00
INSERT...(ID,DATA)VALUES(1,'X')
System Versioning Since SQL:2011
![Page 113: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/113.jpg)
Although multiple versions exist, only the “current” one is visible per default.
After 12:00:00, SELECT*FROMt doesn’t return anything anymore.
ID Data start_ts end_ts1 X 10:00:00 11:00:001 Y 11:00:00 12:00:00
System Versioning Since SQL:2011
![Page 114: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/114.jpg)
ID Data start_ts end_ts1 X 10:00:00 11:00:001 Y 11:00:00 12:00:00
With FOR…ASOF you can query anything you like: SELECT*FROMtFORSYSTEM_TIMEASOFTIMESTAMP'2015-04-0210:30:00'
ID Data start_ts end_ts
1 X 10:00:00 11:00:00
System Versioning Since SQL:2011
![Page 115: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/115.jpg)
1999
2001
2003
2005
2007
2009
2011
2013
2015
2017
5.1 MariaDB[0]
MySQLPostgreSQLSQLite
10.1[1] DB2 LUW10gR1[2] Oracle
2016 SQL Server[0]Available in MariaDB 10.3 beta.[1]Third column required (tx id), history table required.[2]Functionality available using Flashback
System Versioning Since SQL:2011
![Page 116: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/116.jpg)
SQL:2016 (released: 2016-12-15)
![Page 117: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/117.jpg)
New in SQL:2016 JSON
ROW PATTERN MATCHING https://www.slideshare.net/MarkusWinand/row-pattern-matching-in-sql2016
DATE FORMAT
POLYMORPHIC TABLE FUNCTIONS ➔ https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
![Page 118: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/118.jpg)
SQL has evolved beyond the relational idea.
Modern SQL? @MarkusWinand
![Page 119: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/119.jpg)
SQL has evolved beyond the relational idea.
If you are using SQL like 25 years ago,you are doing it wrong!
Modern SQL? @MarkusWinand
![Page 120: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/120.jpg)
SQL has evolved beyond the relational idea.
If you are using SQL like 25 years ago,you are doing it wrong!
Don’t think SQL cannot do this or that.Act professionally: check it out before complaining.
Modern SQL? @MarkusWinand
Act professionally: check it out
![Page 121: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/121.jpg)
SQL has evolved beyond the relational idea.
If you are using SQL like 25 years ago,you are doing it wrong!
Don’t think SQL cannot do this or that.Act professionally: check it out before complaining.
Modern SQL? @MarkusWinand
Act professionally: check it out
![Page 122: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/122.jpg)
About @MarkusWinand
‣Training for Developers ‣ SQL Performance (Indexing) ‣ Modern SQL ‣ On-Site or Online
‣SQL Tuning ‣ Index-Redesign ‣ Query Improvements ‣ On-Site or Online
https://winand.at/
![Page 123: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/123.jpg)
About @MarkusWinand€0,-
€10-30‣Training for Developers ‣ SQL Performance (Indexing) ‣ Modern SQL ‣ On-Site or Online
‣SQL Tuning ‣ Index-Redesign ‣ Query Improvements ‣ On-Site or Online
sql-performance-explained.com
![Page 124: Still using Windows 3.1?...‣Literate SQL Organize SQL code to improve maintainability ‣Assign column names to tables produced by values or unnest. ‣Overload tables (for testing)](https://reader033.vdocuments.us/reader033/viewer/2022060801/6084ef0739d7a74ebe27b3ea/html5/thumbnails/124.jpg)
About @MarkusWinand@ModernSQL
https://modern-sql.com‣Training for Developers ‣ SQL Performance (Indexing) ‣ Modern SQL ‣ On-Site or Online
‣SQL Tuning ‣ Index-Redesign ‣ Query Improvements ‣ On-Site or Online