more than a query language: sql in the 21 century€¦ · is like saying that an intercontinental...

More Than a Query Language: SQL in the 21st Century

@MarkusWinand • @ModernSQL

http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf

http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf

1974 1992

SQL-92 — Tied to the Relational Idea


Relational Data Model‣ “Atomic” types (domain)



Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png



A B C

Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png



A B C



‣Schema independent ofprocessing purposes ‣ “Normalization”

A B C




A B C C D B E




Relational Operations ‣Transform data for

each particularprocessing purposes ‣JOIN, UNION, nesting, …

A B C C D B E A B C D E






A B C C D B EA B C D E

A B E






A B C C D B EA B C D E

A B E

C D E

1992 1999

https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf


SQL:1999 — Escaping the Relational Cage

To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data modelis like saying that an intercontinental ballistic missile is

merely an “extended interpretation” of a spear.




To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data modelis like saying that an intercontinental ballistic missile is

merely an “extended interpretation” of a spear.

With SQL/99 you can get the best of both worlds and

of course, you can get the worst of both worlds.It’s up to the database practitioners to do the right thing.



Relational Model?


Relational Model?

Chris DateDate on Database: Writings 2000-2006


I was as confused as anyone else

Relational Model?



?I was as confused as anyone else

Relational Model?



?I was as confused as anyone else By the early 1990s, however,

I’d seen the light

Relational Model?




I’d seen the lightDomains Can Contain Anything!

Relational Model?‣ Introduced rich types





Relational Model?‣ Introduced rich types


SQL:1999 — Escaping the Relational CageA



Relational Model?‣ Introduced rich types‣arrays


SQL:1999 — Escaping the Relational CageA B

[ , ][ ][]



Relational Model?‣ Introduced rich types‣arrays‣Nested tables (multiset)


SQL:1999 — Escaping the Relational CageA B

[ , ][ ][]

CC D

C D

C D




‣composite types (objects)


SQL:1999 — Escaping the Relational CageA B C D

[ , ] {x: , y: }

[ ] {x: , y: }

[] {x: , y: }

C D

C D

C D




‣composite types (objects)

Non-Relational Operations ‣ Introduced recursive

queries that processtheir own output ‣Transitive closure





SQL:1999 — Recursion CREATETABLEt(idINTEGER,parentINTEGER,)

SQL:1999 — Recursion


SELECTt.id,t.parentFROMtWHEREt.id=?


SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtWHEREt.parent=?


WITHRECURSIVEprev(id,parent)AS(

)

SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtJOINprevONt.parent=prev.id

SELECT*FROMprev


1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 10.2 MariaDB8.0 MySQL

8.4 PostgreSQL1.0 3.8.3 SQLite

7.0 DB2 LUW11gR2 Oracle

2005 SQL Server

LATERAL

SQL:1999 — LATERAL

Select-list sub-queries must be scalar[0]:

SELECT…,(SELECTcolumn_1FROMt1WHEREt1.x=t2.y)AScFROMt2…

(return no more than one domain value)





✗,column_2

More thanone column? ⇒Syntax error





✗,column_2

More thanone column? ⇒Syntax error

}More thanone row?

⇒Runtime error!


Lateral derived tables lift both limitations and can be correlated:

SELECT…,ldt.*FROMt2CROSSJOINLATERAL(SELECTcolumn_1,column_2FROMt1WHEREt1.x=t2.y)ASldt…




“Derived table” meansit’s in the

FROM/JOIN clause





FROM/JOIN clause

Still “correlated”





FROM/JOIN clause

Still “correlated”

Regular join semantics


‣ Top-N per group

inside a lateral derived tableFETCHFIRST (or LIMIT, TOP) applies per row from left tables.


‣ Top-N per group


FROMtJOINLATERAL(SELECT…FROM…WHEREt.c=…ORDERBY…LIMIT10)derived_table


‣ Top-N per group


Add proper indexfor Top-N query

https://use-the-index-luke.com/sql/partial-results/top-n-queries



‣ Top-N per group


‣ Also useful to find most recent news from several subscribed topics (“multi-source top-N”).





‣ Top-N per group


‣ Also useful to find most recent news from several subscribed topics (“multi-source top-N”).

‣ Table function arguments

(TABLE often implies LATERAL)



FROMtJOINTABLE(your_func(t.c))


SQL:1999 — LATERAL1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 MariaDB8.0.14 MySQL

9.3 PostgreSQL1.0 SQLite

9.1 DB2 LUW11gR1[0] 12cR1 Oracle

2005[1] SQL Server[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.

SQL:1999 — LATERAL1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 MariaDB8.0.14 MySQL


9.1 DB2 LUW11gR1[0] 12cR1 Oracle

2005[1] SQL Server[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.

MDEV-6373: Fix Version: none

1999 2003

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

www.acm.org:80/sigmod/record/issues/0206/standard.pdf

SQL:2003 — Schemaless & Analytical

Schemaless ‣ Introduced XML ‣Non-uniform

documents ina single column




Later: ‣ JSON added with SQL:2016 ‣ Proprietary JSON support: ‣ 2012: PostgreSQL ‣ 2014: Oracle ‣ 2015: MySQL ‣ 2016: SQL Server


Analytical ‣ Introduced

window functions ‣Accessing other rows

of the current result





Analytical ‣ Introduced

window functions ‣Accessing other rows

of the current result



Later: ‣ Extended in SQL:2011 ‣ Popular among “New SQLs” ‣ 2013: BigQuery, Hive ‣ 2014: Impala ‣ 2015: Spark SQL ‣ 2016: NuoDB, MemSQL,

Cockroach DB, VoltDB


SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

SQL:2003 — Analytical

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


+10

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


+10

+30

+20

+70

+40

+20

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


,SUM(value)OVER(

)bal

+10

+30

+20

+70

+40

+20

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


ORDERBYid

,SUM(value)OVER(

)bal

+10

+30

+20

+70

+40

+20

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


ORDERBYidROWSBETWEENUNBOUNDEDPRECEDING

,SUM(value)OVER(

)bal

+10

+30

+20

+70

+40

+20

SELECTid,value

FROMt

id

1

2

3

4

5

6

value

+10

+20

-10

+50

-30

-20

bal


ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW

,SUM(value)OVER(

)bal

+10

+30

+20

+70

+40

+20

SELECTid,value

FROMt

id value bal

1 +10

2 +20

3 -10

4 +50

5 -30

6 -20


ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW

,SUM(value)OVER(

)bal

+10

+30

+20

+70

+40

+20

SQL:2003 — Analytical1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 10.2 MariaDB8.0 MySQL

8.4 PostgreSQL1.0 3.25.0 SQLite

7.0 DB2 LUW8i Oracle

2005[0] 2012 SQL Server[0]Without framing

Inverse Distribution Functions (percentiles)

SQL:2003 — Analytical (Median)

SELECTd1.valFROMdatad1JOINdatad2ON(d1.val<d2.valOR(d1.val=d2.valANDd1.id<d2.id))GROUPBYd1.valHAVINGcount(*)=(SELECTFLOOR(COUNT(*)/2)FROMdatad3)



Number rows



Number rows

Pick middle one



Number rows

Pick middle one

SQL:2003 — Analytical (Median)Notice

All employees mustwash hands

after usingself-joins


Number rows

Pick middle one

SQL:2003 — Analytical (Median)Notice

All employees mustwash hands

after usingself-joins

Grab stickersand coasters!

SELECTPERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYval)FROMdata



MedianSQL:2003 — Analytical (Median)


Median

Which value?



Two variants:

‣for discrete values(categories)

‣for continuous values(linear interpolation)


1

2

3

4

0 0.25 0.5 0.75 11

2

3

4


Two variants:




1

2

3

4

0 0.25 0.5 0.75 11

2

3

4

0 0.25 0.5 0.75 11

2

3

4

PERCENTILE_DISC


Two variants:




1

2

3

4

0 0.25 0.5 0.75 11

2

3

4

0 0.25 0.5 0.75 11

2

3

4

PERCENTILE_DISC0 0.25 0.5 0.75 1

1

2

3

4

PERCENTILE_DISC


Two variants:




1

2

3

4

0 0.25 0.5 0.75 11

2

3

4

0 0.25 0.5 0.75 11

2

3

4


1

2

3

4


1

2

3

4

PERCENTILE_DISC(0.5)


Two variants:




1

2

3

4

0 0.25 0.5 0.75 11

2

3

4

0 0.25 0.5 0.75 11

2

3

4


1

2

3

4


1

2

3

4

PERCENTILE_DISC(0.5)0 0.25 0.5 0.75 1

1

2

3

4

PERCENTILE_CONT



Two variants:




1

2

3

4

0 0.25 0.5 0.75 11

2

3

4

0 0.25 0.5 0.75 11

2

3

4


1

2

3

4


1

2

3

4

PERCENTILE_DISC(0.5)0 0.25 0.5 0.75 1

1

2

3

4

PERCENTILE_CONT

PERCENTILE_DISC(0.5)0 0.25 0.5 0.75 1

1

2

3

4

PERCENTILE_CONT(0.5)



Two variants:




SQL:2003 — PERCENTILE_DISC1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 10.3.7[0] MariaDBMySQL


DB2 LUW9iR1 Oracle

2012[0] SQL Server[0]Only as window function (OVER required).

2003 2016

SQL:2016 — JSON

http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip


SQL:2016 — JSON

[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]



SQL:2016 — JSON

id a1

42 foo

43 bar

[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]



SELECT*FROMJSON_TABLE(?,'$[*]'COLUMNS(idINTPATH'$.id',a1VARCHAR(…)PATH'$.a1'))r

SQL:2016 — JSON_TABLE

[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]

id a142 foo43 bar



[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]

id a142 foo43 bar

Bind Parameter



[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]

id a142 foo43 bar

SQL/JSON Path ‣ Query language to

select elements from a JSON document ‣Defined in the

SQL standardBind

Parameter

SQL:2016 — JSON_TABLE — Use Case


[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]

id a142 foo43 bar

SQL:2016 — JSON_TABLE — Use Case


INSERTINTOtarget_table

[{"id":42,"a1":"foo"},{"id":43,"a1":"bar"}]

id a142 foo43 bar

SQL:2016 — JSON_TABLE1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 MariaDB8.0 MySQL

PostgreSQL1.0 SQLite

11.1.4.4 DB2 LUW12cR1 Oracle

2016[0] SQL Server[0]OPENJSON provides similar functionality

SQL:2016 — JSON_TABLE1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 MariaDB8.0 MySQL

PostgreSQL1.0 SQLite

11.1.4.4 DB2 LUW12cR1 Oracle

2016[0] SQL Server[0]OPENJSON provides similar functionality

MDEV-17399: Fix Version: 10.5

2011 2016

SQL:2011 — Time Travelling

http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf



Application Versioning ‣Dedicated syntax added ‣When did something

happen in thereal world?






New syntax (excerpt) ‣ FORPORTIONOF inUPDATE and DELETE ‣ WITHOUTOVERLAPS in UNIQUE

constraints & PRIMARYKEYS‣ [IMMEDIATELY]PRECEDES,OVERLAPS in WHERE,HAVING,…


System Versioning ‣Fully automatic and

(almost) transparent ‣When did we learn

about something






System Versioning ‣Fully automatic and

(almost) transparent ‣When did we learn

about something





Transparent changes, new syntax for queries ‣ INSERT, UPDATE & DELETE

use the system time automatically ‣ SELECT can use FORSYSTEM_TIMEASOF

SQL:2011 — System VersioningCREATETABLEt(...,fromTIMESTAMP(9)GENERATEDALWAYSASROWSTART,tillTIMESTAMP(9)GENERATEDALWAYSASROWEND

,PERIODFORSYSTEM_TIME(from,till))WITHSYSTEMVERSIONING

SQL:2011 — System Versioning

SQL:2011 — System VersioningINSERTINTOt(id,data)VALUES(1,'X')

id data from till1 X 10:00



UPDATEtSETdata='Y'WHEREid=1

id data from till1 X 10:00 11:001 Y 11:00



UPDATEtSETdata='Y'WHEREid=1

id data from till1 X 10:00 11:001 Y 11:00

DELETEFROMtWHEREid=1

id data from till1 X 10:00 11:001 Y 11:00 12:00

SQL:2011 — System Versioningid data from till1 X 10:00 11:001 Y 11:00 12:00


SELECT*FROMt id data from till


SELECT*FROMt id data from till

SELECT*FROMtFORSYSTEM_TIMEASOFTIMESTAMP'…10:30:00'

id data from till1 X 10:00 11:00

SQL:2011 — System Versioning1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 10.3 MariaDBMySQLPostgreSQL

1.0 SQLite

10.1 DB2 LUW10gR1[0] 11gR1[1] Oracle

2016 SQL Server[0]Short term using Flashback.[1]Flashback Archive. Proprietery syntax.

SQL:2011 — Application Time Periods


CREATETABLEt(...,fromTIMESTAMP(9),tillTIMESTAMP(9),PERIODFORa(from,till))


ID Data From Till1 X 10:00:00 12:00:00

INSERTt(id,data,from,till) VALUES(1,'X','10:00:00','12:00:00')


UPDATEtFORPORTIONOFaFROM'10:30:00'TO'11:30:00'SETDATA='Y'

ID Data From Till1 X 10:00:00 10:30:001 Y 10:30:00 11:30:001 X 11:30:00 12:00:00

ID Data From Till1 X 10:00:00 12:00:00

INSERTt(id,data,from,till) VALUES(1,'X','10:00:00','12:00:00')

SQL:2011 — Application Time Periods1999

2001

2003

2005

2007

2009

2011

2013

2015

2017

5.1 10.4 MariaDBMySQLPostgreSQL

1.0 SQLite

10.5 DB2 LUWOracleSQL Server

https://webstore.iec.ch/publication/59685

https://webstore.iec.ch/publication/59685

A lot has happened

since SQL-92

SQL has evolved

beyond the relational idea

A lot has happened

since SQL-92

SQL has evolved


If you use SQL for

CRUD operations only, you are doing it wrong

A lot has happened

since SQL-92

SQL has evolved


If you use SQL for

CRUD operations only, you are doing it wrong

A lot has happened

since SQL-92

https://modern-sql.com@ModernSQL by @MarkusWinand

Training:https://winand.at/

more than a query language: sql in the 21 century€¦ · is like saying that an intercontinental...

Documents