[PPT] - Modern SQL: Evolution of a dinosaur Markus Winand Krakw, 9-11 May PowerPoint Presentation

SLIDE 1

Modern SQL: 

Evolution of a dinosaur

Markus Winand

Kraków, 9-11 May 2018

SLIDE 2

Still using

Windows 3.1?

So why stick with

SQL-92?

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

SLIDE 3

SQL:1999

SLIDE 4

WITH

(Common Table Expressions)

SLIDE 5

Understand this first

WITH (non-recursive) The Problem

Nested queries are hard to read: SELECT … FROM (SELECT … FROM t1 JOIN (SELECT … FROM … ) a ON (…) ) b JOIN (SELECT … FROM … ) c ON (…)

SLIDE 6

Then this...

WITH (non-recursive) The Problem

Nested queries are hard to read: SELECT … FROM (SELECT … FROM t1 JOIN (SELECT … FROM … ) a ON (…) ) b JOIN (SELECT … FROM … ) c ON (…)

SLIDE 7

Then this...

WITH (non-recursive) The Problem

Nested queries are hard to read: SELECT … FROM (SELECT … FROM t1 JOIN (SELECT … FROM … ) a ON (…) ) b JOIN (SELECT … FROM … ) c ON (…)

SLIDE 8

F i n a l l y t h e f i r s t l i n e m a k e s s e n s e

WITH (non-recursive) The Problem

Nested queries are hard to read: SELECT … FROM (SELECT … FROM t1 JOIN (SELECT … FROM … ) a ON (…) ) b JOIN (SELECT … FROM … ) c ON (…)

SLIDE 9

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…)

K e y w

r

d

WITH (non-recursive) Since SQL:1999

SLIDE 10

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…)

Name of CTE and (here

ptional) column names

WITH (non-recursive) Since SQL:1999

SLIDE 11

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…)

Definition

WITH (non-recursive) Since SQL:1999

SLIDE 12

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…)

Introduces another CTE Don't repeat WITH

WITH (non-recursive) Since SQL:1999

SLIDE 13

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…)

May refer to  previous CTEs

WITH (non-recursive) Since SQL:1999

SLIDE 14

WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…) AS (SELECT … FROM …) SELECT … FROM b JOIN c ON (…)

Third CTE

WITH (non-recursive) Since SQL:1999

SLIDE 15

WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…) AS (SELECT … FROM …) SELECT … FROM b JOIN c ON (…)

No comma!

WITH (non-recursive) Since SQL:1999

SLIDE 16

WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…) AS (SELECT … FROM …) SELECT … FROM b JOIN c ON (…)

Main query

WITH (non-recursive) Since SQL:1999

SLIDE 17

CTEs are statement-scoped views: WITH a (c1, c2, c3) AS (SELECT c1, c2, c3 FROM …), b (c4, …) AS (SELECT c4, … FROM t1 JOIN a ON (…) ), c (…) AS (SELECT … FROM …) SELECT … FROM b JOIN c ON (…)

Read top down

WITH (non-recursive) Since SQL:1999

SLIDE 18

Literate SQL

Organize SQL code to  improve maintainability

Assign column names

to tables produced by values 

r unnest.
Overload tables (for testing)

with queries hide tables 

f the same name.

Use-Cases WITH (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

SLIDE 19

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

SLIDE 20

Availability WITH (non-recursive)

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL

3.8.3

[0]

SQLite

7.0

DB2 LUW

9iR2

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.

SLIDE 21

WITH RECURSIVE

(Common Table Expressions)

SLIDE 22

(This page is intentionally left blank)

WITH RECURSIVE The Problem

SLIDE 23

CREATE TABLE t ( id NUMERIC NOT NULL, parent_id NUMERIC, … PRIMARY KEY (id) )

Coping with hierarchies in the Adjacency List Model[0]

WITH RECURSIVE The Problem

[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”

SLIDE 24

SELECT * FROM t AS d0 LEFT JOIN t AS d1 ON (d1.parent_id=d0.id) LEFT JOIN t AS d2 ON (d2.parent_id=d1.id)

Coping with hierarchies in the Adjacency List Model[0]

WITH RECURSIVE The Problem

WHERE d0.id = ?

[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”

SLIDE 25

SELECT * FROM t AS d0 LEFT JOIN t AS d1 ON (d1.parent_id=d0.id) LEFT JOIN t AS d2 ON (d2.parent_id=d1.id)

Coping with hierarchies in the Adjacency List Model[0]

WITH RECURSIVE The Problem

WHERE d0.id = ?

[0] Hierarchies implemented using a “parent id” — see “Joe Celko’s Trees and Hierarchies in SQL for Smarties”

SLIDE 26

SELECT * FROM t AS d0 LEFT JOIN t AS d1 ON (d1.parent_id=d0.id) LEFT JOIN t AS d2 ON (d2.parent_id=d1.id)

WITH RECURSIVE Since SQL:1999

WHERE d0.id = ? WITH RECURSIVE d (id, parent, …) AS  (SELECT id, parent, … FROM tbl WHERE id = ? UNION ALL SELECT id, parent, … FROM d JOIN tbl  ON (tbl.parent=d.id) ) SELECT * FROM subtree

SLIDE 27

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte

K e y w

r

d

Since SQL:1999 WITH RECURSIVE

SLIDE 28

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte

Column list mandatory here

Since SQL:1999 WITH RECURSIVE

SLIDE 29

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte

E x e c u t e d f i r s t

Since SQL:1999 WITH RECURSIVE

SLIDE 30

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte

Result sent there

Since SQL:1999 WITH RECURSIVE

SLIDE 31

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte

Result visible twice

Since SQL:1999 WITH RECURSIVE

SLIDE 32

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

Once it becomes part of  the final  result

Since SQL:1999 WITH RECURSIVE

SLIDE 33

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

Second  leg of UNION   is executed

Since SQL:1999 WITH RECURSIVE

SLIDE 34

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

It's a loop!

Since SQL:1999 WITH RECURSIVE

SLIDE 35

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

It's a loop!

Since SQL:1999 WITH RECURSIVE

SLIDE 36

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

It's a loop!

Since SQL:1999 WITH RECURSIVE

SLIDE 37

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

n=3  doesn't match

Since SQL:1999 WITH RECURSIVE

SLIDE 38

Recursive common table expressions may refer to themselves in a leg of a UNION [ALL]: WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 3) SELECT * FROM cte n

1

2 3 (3 rows)

n=3  doesn't match Loop terminates

Since SQL:1999 WITH RECURSIVE

SLIDE 39

Availability WITH RECURSIVE

1 9 9 9 2 1 2 3 2 5 2 7 2 9 2 1 1 2 1 3 2 1 5 2 1 7

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL

3.8.3

[0]

SQLite

7.0

DB2 LUW

11gR2

Oracle

2005

SQL Server

[0]Only for top-level SELECT statements

SLIDE 40

SQL:2003

SLIDE 41

OVER

and

PARTITION BY

SLIDE 42

OVER (PARTITION BY) The Problem

Two distinct concepts could not be used independently:

Merge rows with the same key properties
GROUP BY to specify key properties
DISTINCT to use full row as key
Aggregate data from related rows
Requires GROUP BY to segregate the rows
COUNT, SUM, AVG, MIN, MAX to aggregate grouped rows

SLIDE 43

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

OVER (PARTITION BY) The Problem

Yes ⇠ Merge rows ⇢ No No ⇠ Aggregate ⇢ Yes SELECT c1 , c2 FROM t SELECT DISTINCT c1 , c2 FROM t SELECT c1 , c2 FROM t SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

SLIDE 44

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

OVER (PARTITION BY) The Problem

Yes ⇠ Merge rows ⇢ No No ⇠ Aggregate ⇢ Yes SELECT c1 , c2 FROM t SELECT DISTINCT c1 , c2 FROM t SELECT c1 , c2 FROM t

JOIN ( ) ta ON (t.c1=ta.c1)

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

, tot

SLIDE 45

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

OVER (PARTITION BY) The Problem

Yes ⇠ Merge rows ⇢ No No ⇠ Aggregate ⇢ Yes SELECT c1 , c2 FROM t SELECT DISTINCT c1 , c2 FROM t SELECT c1 , c2 FROM t

JOIN ( ) ta ON (t.c1=ta.c1)

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

, tot

SLIDE 46

SELECT c1 , SUM(c2) tot FROM t GROUP BY c1

OVER (PARTITION BY) Since SQL:2003

Yes ⇠ Merge rows ⇢ No No ⇠ Aggregate ⇢ Yes SELECT c1 , c2 FROM t SELECT DISTINCT c1 , c2 FROM t SELECT c1 , c2 FROM t FROM t , SUM(c2) OVER (PARTITION BY c1)

SLIDE 47

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 6000 22 1000 6000 22 1000 6000 333 1000 6000 333 1000 6000 333 1000 6000

OVER (PARTITION BY) How it works

SLIDE 48

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 6000 22 1000 6000 22 1000 6000 333 1000 6000 333 1000 6000 333 1000 6000

OVER (PARTITION BY) How it works

SLIDE 49

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 6000 22 1000 6000 22 1000 6000 333 1000 6000 333 1000 6000 333 1000 6000

OVER (PARTITION BY) How it works

SLIDE 50

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 6000 22 1000 6000 22 1000 6000 333 1000 6000 333 1000 6000 333 1000 6000

OVER (PARTITION BY) How it works

SLIDE 51

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 6000 22 1000 6000 22 1000 6000 333 1000 6000 333 1000 6000 333 1000 6000

OVER (PARTITION BY) How it works

SLIDE 52

SELECT dep, salary, SUM(salary) OVER() FROM emp dep salary ts 1 1000 1000 22 1000 2000 22 1000 2000 333 1000 3000 333 1000 3000 333 1000 3000

OVER (PARTITION BY) How it works

) PARTITION BY dep

SLIDE 53

OVER

and

ORDER BY

(Framing & Ranking)

SLIDE 54

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

SELECT id, value, FROM transactions t

SLIDE 55

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

SELECT id, value, (SELECT SUM(value) FROM transactions t2 WHERE t2.id <= t.id) FROM transactions t

SLIDE 56

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

SELECT id, value, (SELECT SUM(value) FROM transactions t2 WHERE t2.id <= t.id) FROM transactions t

Range segregation (<=)  not possible with  GROUP BY or  PARTITION BY

SLIDE 57

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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

SLIDE 58

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id

SLIDE 59

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING

SLIDE 60

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 61

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 62

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 63

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 64

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 65

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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 ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 66

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t SUM(value) OVER ( ) acnt id value balance 1 1 +10 22 2 +20 22 3

10

333 4 +50 333 5

30

333 6

20

ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW

SLIDE 67

OVER (ORDER BY) Since SQL:2003

SELECT id, value, FROM transactions t 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

ORDER BY id ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW PARTITION BY acnt

SLIDE 68

OVER (ORDER BY) Since SQL:2003

With OVER (ORDER BY n) a new type of functions make sense:

n ROW_NUMBER RANK DENSE_RANK PERCENT_RANK CUME_DIST 1 1 1 1 0.25 2 2 2 2 0.33… 0.75 3 3 2 2 0.33… 0.75 4 4 4 3 1 1

SLIDE 69

Aggregates without GROUP BY
Running totals,

moving averages

Ranking
Top-N per Group
Avoiding self-joins

[… many more …]

Use Cases

SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) rn , t.* FROM t) numbered_t WHERE rn <= 3 AVG(…) OVER(ORDER BY … ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) moving_avg

OVER (SQL:2003)

SLIDE 70

OVER may follow any aggregate function OVER defines which rows are visible at each row OVER() makes all rows visible at every row OVER(PARTITION BY …) segregates like GROUP BY OVER(ORDER BY … BETWEEN) segregates using <, >

In a Nutshell OVER (SQL:2003)

SLIDE 71

OVER (SQL:2003) Availability

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

MariaDB

8.0 MySQL 8.4

PostgreSQL SQLite

7.0

DB2 LUW

8i

Oracle

2005

SQL Server

Hive Impala Spark NuoDB

SLIDE 72

SQL:2006

SLIDE 73

XMLTABLE

SLIDE 74

SELECT id , c1 , n FROM tbl , XMLTABLE( '/d/e' PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r

XMLTABLE Since SQL:2006

Stored in tbl.x: <d> <e id="42"> <c1>…</c1> </e> </d> XPath* expression to identify rows

*Standard SQL allows XQuery

SLIDE 75

SELECT id , c1 , n FROM tbl , XMLTABLE( '/d/e' PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r

XMLTABLE Since SQL:2006

Stored in tbl.x: <d> <e id="42"> <c1>…</c1> </e> </d>

*Standard SQL allows XQuery

SLIDE 76

SELECT id , c1 , n FROM tbl , XMLTABLE( '/d/e' PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r

XMLTABLE Since SQL:2006

Stored in tbl.x: <d> <e id="42"> <c1>…</c1> </e> </d>

*Standard SQL allows XQuery

XPath* expressions to extract data

SLIDE 77

SELECT id , c1 , n FROM tbl , XMLTABLE( '/d/e' PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r

XMLTABLE Since SQL:2006

Stored in tbl.x: <d> <e id="42"> <c1>…</c1> </e> </d>

*Standard SQL allows XQuery

Row number (like for unnest)

SLIDE 78

SELECT id , c1 , n FROM tbl , XMLTABLE( '/d/e' PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r

XMLTABLE Since SQL:2006

Stored in tbl.x: <d> <e id="42"> <c1>…</c1> </e> </d>

*Standard SQL allows XQuery

Result id | c1 | n

---+----+---

42 | … | 1

SLIDE 79

XMLTABLE Availability

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

MariaDB MySQL

10[0] PostgreSQL

SQLite

9.7

DB2 LUW

11gR1

Oracle SQL Server

[0]No XQuery (only XPath). No default namespace declaration.

SLIDE 80

SQL:2008

SLIDE 81

FETCH FIRST

SLIDE 82

SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY x) rn FROM data) numbered_data WHERE rn <=10

FETCH FIRST The Problem

Limit the result to a number of rows.

(LIMIT, TOP and ROWNUM are all proprietary)

SQL:2003 introduced ROW_NUMBER() to number rows.  But this still requires wrapping to limit the result. And how about databases not supporting ROW_NUMBER()?

SLIDE 83

SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY x) rn FROM data) numbered_data WHERE rn <=10

FETCH FIRST The Problem

Limit the result to a number of rows.

(LIMIT, TOP and ROWNUM are all proprietary)

SQL:2003 introduced ROW_NUMBER() to number rows.  But this still requires wrapping to limit the result. And how about databases not supporting ROW_NUMBER()?

Dammit! Let's take  LIMIT

SLIDE 84

SELECT * FROM data ORDER BY x FETCH FIRST 10 ROWS ONLY

FETCH FIRST Since SQL:2008

SQL:2008 introduced the FETCH FIRST … ROWS ONLY clause:

SLIDE 85

FETCH FIRST Availability

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1

MariaDB

3.19.3

[0]

MySQL

6.5

[1]

8.4

PostgreSQL

2.1.0

[1]

SQLite

7.0

DB2 LUW

12cR1

Oracle

7.0

[2]

2012

SQL Server

[0]Earliest mention of LIMIT. Probably inherited from mSQL [1]Functionality available using LIMIT [2]SELECT TOP n ... SQL Server 2000 also supports expressions and bind parameters

SLIDE 86

SQL:2011

SLIDE 87

OFFSET

SLIDE 88

SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY x) rn FROM data) numbered_data WHERE rn > 10 and rn <= 20

OFFSET The Problem

How to fetch the rows after a limit? 

(pagination anybody?)

SLIDE 89

SELECT * FROM data ORDER BY x OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

OFFSET Since SQL:2011

SQL:2011 introduced OFFSET, unfortunately!

SLIDE 90

SELECT * FROM data ORDER BY x OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

OFFSET Since SQL:2011

SQL:2011 introduced OFFSET, unfortunately!

OFFSET

G r a b c

a

s t e r s & s t i c k e r s !

https://use-the-index-luke.com/no-offset

SLIDE 91

OFFSET Since SQL:2011

1999 2001 2003 2005 2007 2009 2011 2013 2015

5.1

MariaDB

3.20.3

[0]

4.0.6

[1]

MySQL

6.5

PostgreSQL

2.1.0

SQLite

9.7

[2]

11.1

DB2 LUW

12c

Oracle

2012

SQL Server

[0]LIMIT [offset,] limit: "With this it's easy to do a poor man's next page/previous page WWW application." [1]The release notes say "Added PostgreSQL compatible LIMIT syntax" [2]Requires enabling the MySQL compatibility vector: db2set DB2_COMPATIBILITY_VECTOR=MYS

SLIDE 92

OVER

SLIDE 93

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t

SLIDE 94

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t , ROW_NUMBER() OVER(ORDER BY x) rn

SLIDE 95

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t , ROW_NUMBER() OVER(ORDER BY x) rn

SLIDE 96

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t , ROW_NUMBER() OVER(ORDER BY x) rn

prev balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

SLIDE 97

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t , ROW_NUMBER() OVER(ORDER BY x) rn

prev balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

SLIDE 98

WITH numbered_t AS (SELECT * ) SELECT curr.* , curr.balance

COALESCE(prev.balance,0)

FROM numbered_t curr LEFT JOIN numbered_t prev ON (curr.rn = prev.rn+1)

OVER (SQL:2011) The Problem

Direct access of other rows of the same window is not possible.

(E.g., calculate the difference to the previous rows)

curr balance … rn 50 … 1 90 … 2 70 … 3 30 … 4

FROM t , ROW_NUMBER() OVER(ORDER BY x) rn

prev balance … rn 50 … 1 90 … 2 70 … 3 30 … 4 +50 +40

20
40

SLIDE 99

SELECT *, balance  

COALESCE( LAG(balance)

OVER(ORDER BY x)  , 0)  FROM t

Available functions:

LEAD / LAG  FIRST_VALUE / LAST_VALUE  NTH_VALUE(col, n) FROM FIRST/LAST  RESPECT/IGNORE NULLS

OVER (SQL:2011) Since SQL:2011

SQL:2011 introduced LEAD, LAG, NTH_VALUE, … for that:

SLIDE 100

OVER (LEAD, LAG, …) Since SQL:2011

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1 10.2

[0]

MariaDB

8.0

[0] MySQL

8.4

[0]

PostgreSQL SQLite

9.5

[1]

11.1

DB2 LUW

8i

[1]

11gR2

Oracle

2012

[1]

SQL Server

[0]No IGNORE NULLS and FROM LAST [1]No NTH_VALUE

SLIDE 101

System Versioning

(Time Traveling)

SLIDE 102

INSERT UPDATE DELETE are

DESTRUCTIVE

System Versioning The Problem

SLIDE 103

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

System Versioning Since SQL:2011

Table can be system versioned, application versioned or both.

SLIDE 104

ID Data start_ts end_ts 1 X 10:00:00

UPDATE ... SET DATA = 'Y' ...

ID Data start_ts end_ts 1 X 10:00:00 11:00:00 1 Y 11:00:00

DELETE ... WHERE ID = 1 INSERT ... (ID, DATA) VALUES (1, 'X')

System Versioning Since SQL:2011

SLIDE 105

ID Data start_ts end_ts 1 X 10:00:00

UPDATE ... SET DATA = 'Y' ...

ID Data start_ts end_ts 1 X 10:00:00 11:00:00 1 Y 11:00:00

DELETE ... WHERE ID = 1

ID Data start_ts end_ts 1 X 10:00:00 11:00:00 1 Y 11:00:00 12:00:00

System Versioning Since SQL:2011

SLIDE 106

Although multiple versions exist, only the “current”

ne is visible per default.

After 12:00:00, SELECT * FROM t doesn’t return anything anymore.

ID Data start_ts end_ts 1 X 10:00:00 11:00:00 1 Y 11:00:00 12:00:00

System Versioning Since SQL:2011

SLIDE 107

ID Data start_ts end_ts 1 X 10:00:00 11:00:00 1 Y 11:00:00 12:00:00

With FOR … AS OF you can query anything you like: SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2015-04-02 10:30:00'

ID Data start_ts end_ts 1 X 10:00:00 11:00:00

System Versioning Since SQL:2011

SLIDE 108

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

5.1

MariaDB

[0]

MySQL PostgreSQL SQLite

10.1

[1]

DB2 LUW

10gR1

[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

SLIDE 109

SQL:2016

(released: 2016-12-15)

SLIDE 110

LISTAGG

SLIDE 111

Since SQL:2016

grp val 1 B 1 A 1 C 2 X grp val 1 A, B, C 2 X SELECT grp , LISTAGG(val, ', ')  WITHIN GROUP (ORDER BY val) FROM t GROUP BY grp LISTAGG(val, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT) ➔ 'A, B, ...(1)' LISTAGG(val, ', ' ON OVERFLOW ERROR)

Default

LISTAGG

LISTAGG(val, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) ➔ 'A, B, ...'

Default

SLIDE 112

1999 2001 2003 2005 2007 2009 2011 2013 2015

5.1

[0]

MariaDB

4.1

[0]

MySQL

7.4

[1]

8.4

[2]9.0 [3]

PostgreSQL

3.5.4

[4]

SQLite

10.5

[5]

DB2 LUW

11gR1 12cR2

Oracle SQL Server

[6]

[0]group_concat [1]array_to_string [2]array_agg [3]string_agg

LISTAGG

Availability

[0] group_concat [1] array_to_string [2] array_agg [3] string_agg [4] group_concat w/o ORDER BY [5] No ON OVERFLOW clause [6] string_agg announced for vNext

SLIDE 113

New in SQL:2016

JSON LISTAGG 

https://modern-sql.com/feature/listagg

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

SLIDE 114

SQL has evolved beyond the relational idea.

Modern SQL?

@MarkusWinand

SLIDE 115

SQL has evolved beyond the relational idea. If you are using SQL like 25 years ago, 

you are doing it wrong! Modern SQL?

@MarkusWinand

SLIDE 116

SQL has evolved beyond the relational idea. If you are using SQL like 25 years ago, 

you are doing it wrong!

A lot has happened since SQL-92.

Modern SQL?

@MarkusWinand

SLIDE 117

https://www.flickr.com/photos/mfoubister/25367243054/

SLIDE 118

I have shown you a few features today

https://www.flickr.com/photos/mfoubister/25367243054/

SLIDE 119

I have shown you a few features today

https://www.flickr.com/photos/mfoubister/25367243054/

There are hundreds more to discover

SLIDE 120

@ModernSQL modern-sql.com

My other website: https://use-the-index-luke.com

Training & co: https://winand.at/