The Mother of All Query Languages: SQL in Modern Times - - PowerPoint PPT Presentation

▶
the mother of all query languages sql in modern times
SMART_READER_LITE
LIVE PREVIEW

The Mother of All Query Languages: SQL in Modern Times - - PowerPoint PPT Presentation

Feb 15, 2024 156 likes •522 views

The Mother of All Query Languages: SQL in Modern Times @MarkusWinand @ModernSQL 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

slide-1
SLIDE 1

The Mother of All Query Languages: SQL in Modern Times

@MarkusWinand • @ModernSQL

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

slide-2
SLIDE 2

1974 1992

slide-3
SLIDE 3

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-4
SLIDE 4

SQL-92 — Tied to the Relational Idea

Relational Data Model

  • “Atomic” types (domain)

A B C

slide-5
SLIDE 5

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-6
SLIDE 6

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-7
SLIDE 7

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-8
SLIDE 8

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-9
SLIDE 9

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-10
SLIDE 10

1992 1999

slide-11
SLIDE 11

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

slide-12
SLIDE 12

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-13
SLIDE 13

Relational Model?

Chris Date

Date on Database: Writings 2000-2006

SQL:1999 — Escaping the Relational Cage

?

I was as confused as anyone else

slide-14
SLIDE 14

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-15
SLIDE 15

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-16
SLIDE 16

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-17
SLIDE 17

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-18
SLIDE 18

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-19
SLIDE 19

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-20
SLIDE 20

SQL:1999 — Recursion

slide-21
SLIDE 21

SQL:1999 — Recursion

slide-22
SLIDE 22

SQL:1999 — Recursion

slide-23
SLIDE 23

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-24
SLIDE 24

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-25
SLIDE 25

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-26
SLIDE 26

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-27
SLIDE 27

SQL:1999 — Recursion

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-28
SLIDE 28

1999 2016

slide-29
SLIDE 29

SQL:2016 — JSON

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

slide-30
SLIDE 30

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-31
SLIDE 31

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

SQL:2016 — JSON

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

id a1 42 foo 43 bar

slide-32
SLIDE 32

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

SQL:2016 — JSON

[ { "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

slide-33
SLIDE 33

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

SQL:2016 — JSON

[ { "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

slide-34
SLIDE 34

SQL:2016 — JSON

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017

MariaDB

8.0

MySQL PostgreSQL SQLite DB2 LUW

12cR1

Oracle SQL Server

slide-35
SLIDE 35

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