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

SLIDE 1

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

@MarkusWinand • @ModernSQL

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

SLIDE 2

SLIDE 3

1974 1992

SLIDE 4

SQL-92 — Tied to the Relational Idea

SLIDE 5

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)

SLIDE 6

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

SLIDE 7

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)

A B C

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

SLIDE 8

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)

A B C

SLIDE 9

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

A B C

SLIDE 10

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

A B C C D B E

SLIDE 11

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

Relational Operations

Transform data for

each particular  processing purposes

JOIN, UNION, nesting, …

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

SLIDE 12

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

Relational Operations

Transform data for

each particular  processing purposes

JOIN, UNION, nesting, …

A B C C D B E

A B C D E

A B E

SLIDE 13

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

Relational Operations

Transform data for

each particular  processing purposes

JOIN, UNION, nesting, …

A B C C D B E

A B C D E A B E

C D E

SLIDE 14

SQL-92 — Tied to the Relational Idea

Relational Data Model

“Atomic” types (domain)
Schema independent of

processing purposes

“Normalization”

Relational Operations

Transform data for

each particular  processing purposes

JOIN, UNION, nesting, …

A B C C D B E

A B C D E A B E C D E

SLIDE 15

1992 1999

SLIDE 16

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

SLIDE 17

SQL:1999 — Escaping the Relational Cage

To say that these SQL:1999 extensions are mere   “extended interpretations” of the relational data model  is like saying that an intercontinental ballistic missile is  merely an “extended interpretation” of a spear.

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

SLIDE 18

SQL:1999 — Escaping the Relational Cage

To say that these SQL:1999 extensions are mere   “extended interpretations” of the relational data model  is 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 

f course, you can get the worst of both worlds.

It’s up to the database practitioners to do the right thing.

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

SLIDE 19

SQL:1999 — Escaping the Relational Cage

SLIDE 20

Relational Model?

SQL:1999 — Escaping the Relational Cage

SLIDE 21

Relational Model?

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

I was as confused as anyone else

SLIDE 22

Relational Model?

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

?

I was as confused as anyone else

SLIDE 23

Relational Model?

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

SLIDE 24

Relational Model?

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 25

Relational Model?

Introduced rich types

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 26

Relational Model?

Introduced rich types

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

A

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 27

Relational Model?

Introduced rich types
arrays

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

A B

[ , ] [ ] []

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 28

Relational Model?

Introduced rich types
arrays
Nested tables (multiset)

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

A B

[ , ] [ ] []

C

C D C D C D

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 29

Relational Model?

Introduced rich types
arrays
Nested tables (multiset)
composite types (objects)

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

A B C D

[ , ]

{x: , y: }

[ ]

{x: , y: }

[]

{x: , y: }

C D C D C D

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 30

Relational Model?

Introduced rich types
arrays
Nested tables (multiset)
composite types (objects)

Non-Relational Operations

Introduced recursive

queries that process  their own output

Transitive closure

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

Domains Can Contain Anything!

SLIDE 31

SQL:1999 — Recursion

CREATE TABLE t ( id INTEGER, parent INTEGER, )

SLIDE 32

SQL:1999 — Recursion

CREATE TABLE t ( id INTEGER, parent INTEGER, )

SLIDE 33

SQL:1999 — Recursion

CREATE TABLE t ( id INTEGER, parent INTEGER, )

SLIDE 34

SQL:1999 — Recursion

SLIDE 35

SQL:1999 — Recursion

SLIDE 36

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ?

SLIDE 37

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SLIDE 38

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SLIDE 39

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SLIDE 40

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SLIDE 41

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SLIDE 42

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SLIDE 43

SQL:1999 — Recursion

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL

1.0 3.8.3

SQLite

7.0

DB2 LUW

11gR2

Oracle

2005

SQL Server

SLIDE 44

1999 2003

SLIDE 45

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

SLIDE 46

SQL:2003 — Schemaless & Analytical

Schemaless

Introduced XML
Non-uniform

documents in  a single column

SLIDE 47

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

SLIDE 48

SQL:2003 — Schemaless & Analytical

Analytical

Introduced

window functions

Accessing other rows 
f the current result

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

SLIDE 49

SQL:2003 — Schemaless & Analytical

Analytical

Introduced

window functions

Accessing other rows 
f the current result

Schemaless

Introduced XML
Non-uniform

documents in  a single column

Later:

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

Cockroach DB, VoltDB

Later:

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

SLIDE 50

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

SQL:2003 — Analytical

SLIDE 51

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

SLIDE 52

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

+10

SLIDE 53

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

+10 +30 +20 +70 +40 +20

SLIDE 54

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

, SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 55

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 56

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 57

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 58

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 59

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 60

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 61

SELECT id, value FROM t

id 1 2 3 4 5 6 value +10 +20

10

+50

30
20

bal

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 62

SELECT id, value FROM t

id value bal 1 +10 2 +20 3

10

4 +50 5

30

6

20

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 63

SELECT id, value FROM t

id value bal 1 +10 2 +20 3

10

4 +50 5

30

6

20

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 64

SELECT id, value FROM t

id value bal 1 +10 2 +20 3

10

4 +50 5

30

6

20

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 65

SELECT id, value FROM t

id value bal 1 +10 2 +20 3

10

4 +50 5

30

6

20

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 66

SELECT id, value FROM t

id value bal 1 +10 2 +20 3

10

4 +50 5

30

6

20

SQL:2003 — Analytical

ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal

+10 +30 +20 +70 +40 +20

SLIDE 67

SQL:2003 — Analytical

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL

1.0 3.25.0

SQLite

7.0

DB2 LUW

8i

Oracle

2005[0] 2012

SQL Server

[0]Without framing

SLIDE 68

SQL:2003 — Analytical

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL

1.0 3.25.0

SQLite

7.0

DB2 LUW

8i

Oracle

2005[0] 2012

SQL Server

[0]Without framing

Impala Spark NuoDB BigQuery Hive

SLIDE 69

Inverse Distribution Functions (percentiles)

SLIDE 70

SQL:2003 — Analytical (Median)

SLIDE 71

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

SQL:2003 — Analytical (Median)

SLIDE 72

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows

SQL:2003 — Analytical (Median)

SLIDE 73

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows Pick middle one

SQL:2003 — Analytical (Median)

SLIDE 74

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows Pick middle one

SQL:2003 — Analytical (Median)

SLIDE 75

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows Pick middle one

SQL:2003 — Analytical (Median)

SLIDE 76

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows Pick middle one

SQL:2003 — Analytical (Median)

Notice

All employees must

wash hands

after using

self-joins

SLIDE 77

SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)

Number rows Pick middle one

SQL:2003 — Analytical (Median)

Notice

All employees must

wash hands

after using

self-joins

Grab stickers  and coasters!

SLIDE 78

SQL:2003 — Analytical (Median)

SLIDE 79

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

SQL:2003 — Analytical (Median)

SLIDE 80

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Median

SQL:2003 — Analytical (Median)

SLIDE 81

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Median Which value?

SQL:2003 — Analytical (Median)

SLIDE 82

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

SQL:2003 — Analytical (Median)

SLIDE 83

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 84

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 85

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 86

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 87

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC(0.5)

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 88

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC(0.5)

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_CONT PERCENTILE_DISC(0.5)

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 89

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_DISC(0.5)

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_CONT PERCENTILE_DISC(0.5)

0.25 0.5 0.75 1

1 2 3 4 PERCENTILE_CONT(0.5) PERCENTILE_DISC(0.5)

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

Two variants:

for discrete values

(categories)

for continuous values

(linear interpolation)

SQL:2003 — Analytical (Median)

SLIDE 90

SQL:2003 — PERCENTILE_DISC

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.3.7

[0]

MariaDB MySQL

9.4

PostgreSQL

1.0

SQLite DB2 LUW

9iR1

Oracle

2012[0]

SQL Server

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

SLIDE 91

SQL:2003 — PERCENTILE_DISC

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.3.7

[0]

MariaDB MySQL

9.4

PostgreSQL

1.0

SQLite DB2 LUW

9iR1

Oracle

2012[0]

SQL Server

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

BigQuery Hive-16255 RESOLVED Fix Version: 4.0.0

SLIDE 92

2003 2016

SLIDE 93

SQL:2016 — JSON

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

SLIDE 94

SQL:2016 — JSON

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

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

SLIDE 95

SQL:2016 — JSON

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

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

SLIDE 96

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

SQL:2016 — JSON_TABLE

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

id a1 42 foo 43 bar

SLIDE 97

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

SQL:2016 — JSON_TABLE

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

id a1 42 foo 43 bar

Bind Parameter

SLIDE 98

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

SQL:2016 — JSON_TABLE

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

id a1 42 foo 43 bar

SQL/JSON Path

Query language to

select elements from a JSON document

Defined in the

SQL standard

Bind Parameter

SLIDE 99

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

SQL:2016 — JSON_TABLE

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

id a1 42 foo 43 bar

SQL/JSON Path

Query language to

select elements from a JSON document

Defined in the

SQL standard

Bind Parameter

SLIDE 100

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

SQL:2016 — JSON_TABLE

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

id a1 42 foo 43 bar

SQL/JSON Path

Query language to

select elements from a JSON document

Defined in the

SQL standard

Bind Parameter

SLIDE 101

SQL:2016 — JSON_TABLE — Use Case

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r

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

id a1 42 foo 43 bar

SLIDE 102

SQL:2016 — JSON_TABLE — Use Case

SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r INSERT INTO target_table

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

id a1 42 foo 43 bar

SLIDE 103

SQL:2016 — JSON_TABLE

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1

MariaDB

8.0 MySQL

PostgreSQL

1.0

SQLite

11.1.4.4

[0]

DB2 LUW

12cR1

Oracle

2016[1]

SQL Server

[0]Ridicoulus limitations: only 'strict $' as row expression [1]OPENJSON provides similar functionality

SLIDE 104

SQL:2016 — JSON_TABLE

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1

MariaDB

8.0 MySQL

PostgreSQL

1.0

SQLite

11.1.4.4

[0]

DB2 LUW

12cR1

Oracle

2016[1]

SQL Server

[0]Ridicoulus limitations: only 'strict $' as row expression [1]OPENJSON provides similar functionality

MDEV-17399 Tagged with  fix/Version: 10.5 Patch available

SLIDE 105

2011 2016

SLIDE 106

SQL:2011 — Time Travelling

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

SLIDE 107

SQL:2011 — Time Travelling

Application Versioning

Dedicated syntax added
When did something

happen in the  real world?

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

SLIDE 108

SQL:2011 — Time Travelling

Application Versioning

Dedicated syntax added
When did something

happen in the  real world?

New syntax (excerpt)

FOR PORTION OF in

UPDATE and DELETE

WITHOUT OVERLAPS in UNIQUE

constraints & PRIMARY KEYS

[IMMEDIATELY] PRECEDES,

OVERLAPS in WHERE,HAVING,…

SLIDE 109

SQL:2011 — Time Travelling

System Versioning

Fully automatic and

(almost) transparent

When did we learn

about something Application Versioning

Dedicated syntax added
When did something

happen in the  real world?

New syntax (excerpt)

FOR PORTION OF in

UPDATE and DELETE

WITHOUT OVERLAPS in UNIQUE

constraints & PRIMARY KEYS

[IMMEDIATELY] PRECEDES,

OVERLAPS in WHERE,HAVING,…

SLIDE 110

SQL:2011 — Time Travelling

System Versioning

Fully automatic and

(almost) transparent

When did we learn

about something Application Versioning

Dedicated syntax added
When did something

happen in the  real world?

New syntax (excerpt)

FOR PORTION OF in

UPDATE and DELETE

WITHOUT OVERLAPS in UNIQUE

constraints & PRIMARY KEYS

[IMMEDIATELY] PRECEDES,

OVERLAPS in WHERE,HAVING,… Transparent changes, new syntax for queries

INSERT, UPDATE & DELETE

use the system time automatically

SELECT can use FOR

SYSTEM_TIME AS OF

SLIDE 111

SQL:2011 — System Versioning

CREATE TABLE t ( ... , from TIMESTAMP(9) GENERATED ALWAYS  AS ROW START , till TIMESTAMP(9) GENERATED ALWAYS  AS ROW END  , PERIOD FOR SYSTEM_TIME (from, till) ) WITH SYSTEM VERSIONING

SLIDE 112

SQL:2011 — System Versioning

SLIDE 113

SQL:2011 — System Versioning

INSERT INTO t (id, data)  VALUES (1 , 'X' )

id data from till 1 X 10:00

SLIDE 114

SQL:2011 — System Versioning

INSERT INTO t (id, data)  VALUES (1 , 'X' )

id data from till 1 X 10:00

UPDATE t SET data = 'Y' WHERE id = 1

id data from till 1 X 10:00 11:00 1 Y 11:00

SLIDE 115

SQL:2011 — System Versioning

INSERT INTO t (id, data)  VALUES (1 , 'X' )

id data from till 1 X 10:00

UPDATE t SET data = 'Y' WHERE id = 1

id data from till 1 X 10:00 11:00 1 Y 11:00

DELETE FROM t WHERE id = 1

id data from till 1 X 10:00 11:00 1 Y 11:00 12:00

SLIDE 116

SQL:2011 — System Versioning

id data from till 1 X 10:00 11:00 1 Y 11:00 12:00

SLIDE 117

SQL:2011 — System Versioning

id data from till 1 X 10:00 11:00 1 Y 11:00 12:00

SELECT * FROM t

id data from till

SLIDE 118

SQL:2011 — System Versioning

id data from till 1 X 10:00 11:00 1 Y 11:00 12:00

SELECT * FROM t

id data from till

SELECT * FROM t FOR SYSTEM_TIME AS OF  TIMESTAMP'…10:30:00'

id data from till 1 X 10:00 11:00

SLIDE 119

SQL:2011 — System Versioning

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.3

MariaDB MySQL PostgreSQL

1.0

SQLite

10.1

DB2 LUW

10gR1[0] 11gR1[1]

Oracle

2016

SQL Server

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

SLIDE 120

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

SLIDE 121

A lot has  happened since SQL-92

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

SLIDE 122

SQL has evolved  beyond the relational idea A lot has  happened since SQL-92

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

SLIDE 123

SQL has evolved  beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has  happened since SQL-92

SLIDE 124

SQL has evolved  beyond the relational idea 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

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

@MarkusWinand • @ModernSQL

1974 1992

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

SQL-92 — Tied to the Relational Idea

1992 1999

SQL:1999 — Escaping the Relational Cage

SQL:1999 — Escaping the Relational Cage

SQL:1999 — Escaping the Relational Cage

SQL:1999 — Escaping the Relational Cage

SQL:1999 — Escaping the Relational Cage

I was as confused as anyone else

SQL:1999 — Escaping the Relational Cage

?

I was as confused as anyone else

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

[ , ] [ ] []

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

[ , ] [ ] []

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

[ , ]

[ ]

[]

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Escaping the Relational Cage

? I was as confused as anyone else By the early 1990s, however, I’d seen the light

Domains Can Contain Anything!

SQL:1999 — Recursion

SQL:1999 — Recursion

SQL:1999 — Recursion

SQL:1999 — Recursion

SQL:1999 — Recursion

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ?

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SQL:1999 — Recursion

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SQL:1999 — Recursion

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

SQL:1999 — Recursion

MariaDB

PostgreSQL

SQLite

DB2 LUW

Oracle

SQL Server

1999 2003

SQL:2003 — Schemaless & Analytical

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

? I was as confused as anyone else By the early 1990s, however,  I’d seen the light

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS ( )  SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev