Old 

While SQL has turned 50 this year, it is by no means the same as 50 years ago. The latest update was mid 2023.

Relational 

Depends on how you understand relational: It is not about atomicity. It is fine to have JSON, XML and even arrays in single fields. Yet, in the persistent model simpler types have their merits.

 Query Language 

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

While SQL has turned 50 this year, it is by no means the same as 50 years ago. The latest update was mid 2023.

Relational 

Depends on how you understand relational: It is not about atomicity. It is fine to have JSON, XML and even arrays in single fields. Yet, in the persistent model simpler types have their merits.

 Query Language 

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

While SQL has turned 50 this year, it is by no means the same as 50 years ago. The latest update was mid 2023.

Depends on how you understand relational: It is not about atomicity. It is fine to have JSON, XML and even arrays in single fields. Yet, in the persistent model simpler types have their merits.

 Query Language 

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

While SQL has turned 50 this year, it is by no means the same as 50 years ago. The latest update was mid 2023.

Depends on how you understand relational: It is not about atomicity. It is fine to have JSON, XML and even arrays in single fields. Yet, in the persistent model simpler types have their merits.

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

Depends on how you understand relational: It is not about atomicity. It is fine to have JSON, XML and even arrays in single fields. Yet, in the persistent model simpler types have their merits.

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

It is rather a query and transformation language. A pure query language (XPath, CSS selectors) just picks items out of something. SQL can do so much more — it is actually Turning complete.

 Multi-paradigm 

Early SQL focused on relational concepts. In 1999, SQL broke free from the relational cage: Recursion, window-functions, composite types, XML/JSON, etc.

 Domain Specific 

While SQL is Turing complete, it is not a general purpose language. SQL complements other languages to manage [*] data more easily.

[*]: large amounts of, shared, persistent, etc.

 Extensible Industry Standard 

Since 1987, SQL is defined in international standards: ISO/IEC 9075-1 to -16. They define syntax and the semantics of statements using this syntax. They explicitly allow SQL implementations to accept non-standard syntax.

 Domain Specific 

While SQL is Turing complete, it is not a general purpose language. SQL complements other languages to manage [*] data more easily.

[*]: large amounts of, shared, persistent, etc.

 Extensible Industry Standard 

Since 1987, SQL is defined in international standards: ISO/IEC 9075-1 to -16. They define syntax and the semantics of statements using this syntax. They explicitly allow SQL implementations to accept non-standard syntax.

 Extensible Industry Standard 

Since 1987, SQL is defined in international standards: ISO/IEC 9075-1 to -16. They define syntax and the semantics of statements using this syntax. They explicitly allow SQL implementations to accept non-standard syntax.

 Declarative 

SQL is generally referred to as being declarative… …but it also embraces ideas from the functional programming paradigm without enforcing its limitations. Functions may have side effects: NON DETERMINISTIC • MODIFIES SQL DATA

 Strictly Typed 

Although SQL is strictly typed[*] it has some flexible types:

[*] SQLite being the famous exception (but see STRICT tables).

SQL is generally referred to as being declarative… …but it also embraces ideas from the functional programming paradigm without enforcing its limitations. Functions may have side effects: NON DETERMINISTIC • MODIFIES SQL DATA

 Strictly Typed 

Although SQL is strictly typed[*] it has some flexible types:

[*] SQLite being the famous exception (but see STRICT tables).

SQL is generally referred to as being declarative… …but it also embraces ideas from the functional programming paradigm without enforcing its limitations. Functions may have side effects: NON DETERMINISTIC • MODIFIES SQL DATA

Although SQL is strictly typed[*] it has some flexible types:

[*] SQLite being the famous exception (but see STRICT tables).

Although SQL is strictly typed[*] it has some flexible types:

[*] SQLite being the famous exception (but see STRICT tables).

CREATE TABLE t (
   a INTEGER ARRAY[1000], -- maximum length
   x XML,
   j JSON
)

CREATE TABLE t (
   a INTEGER ARRAY[1000], -- maximum length
   x XML,
   j JSON
)

CREATE TABLE t (
   a INTEGER ARRAY[1000], -- maximum length
   x XML,
   j JSON
)

SELECT …
  FROM …
 WHERE id = 0b101010 -- binary literal 

SELECT …
  FROM …
 WHERE id = 0b101010 -- binary literal 

SELECT …
  FROM …
 WHERE id = 0o52 -- octal literal      

SELECT …
  FROM …
 WHERE id = 0b101010 -- binary literal 

SELECT …
  FROM …
 WHERE id = 0o52 -- octal literal      

SELECT …
  FROM …
 WHERE id = 0x2A -- hexadecimal literal

SELECT …
  FROM …
 WHERE id = 0b101010 -- binary literal 

SELECT …
  FROM …
 WHERE id = 0o52 -- octal literal      

SELECT …
  FROM …
 WHERE id = 0x2A -- hexadecimal literal

SELECT …
  FROM …
 WHERE id = 42 -- decimal              

SELECT …
  FROM …
 WHERE id = 1_000_000

CREATE TABLE … (
  x VARCHAR(/* what should I put here? */)       
)

CREATE TABLE … (
  x VARCHAR(/* what should I put here? */)       
)

CREATE TABLE … (
  x VARCHAR -- Since SQL:2023, it's OK to skip it
)

CREATE TABLE … (
  x BIGINT UNIQUE -- NULLs are allowed                          
)

CREATE TABLE … (
  x BIGINT UNIQUE -- NULLs are allowed                          
)

INSERT INTO … (x) VALUES (NULL) -- First NULL, no doubt         

CREATE TABLE … (
  x BIGINT UNIQUE -- NULLs are allowed                          
)

INSERT INTO … (x) VALUES (NULL) -- First NULL, no doubt         

INSERT INTO … (x) VALUES (NULL) -- Should it work?
                                -- According to my understanding
                                -- of the standard before 2023
                                -- YES!

CREATE TABLE … (
  x BIGINT UNIQUE NULLS DISTINCT    
)

INSERT INTO … (x) VALUES (NULL) -- first one works anyway           

INSERT INTO … (x) VALUES (NULL) -- Works if NULLS DISTINCT
                                -- Rejected if NULLS NOT DISTINCT
                                -- Default: “Implementation defined”

CREATE TABLE … (
  x BIGINT UNIQUE NULLS NOT DISTINCT
)

INSERT INTO … (x) VALUES (NULL) -- first one works anyway           

INSERT INTO … (x) VALUES (NULL) -- Works if NULLS DISTINCT
                                -- Rejected if NULLS NOT DISTINCT
                                -- Default: “Implementation defined”

CREATE TABLE … (
  x BIGINT UNIQUE                   
)

INSERT INTO … (x) VALUES (NULL) -- first one works anyway           

INSERT INTO … (x) VALUES (NULL) -- Works if NULLS DISTINCT
                                -- Rejected if NULLS NOT DISTINCT
                                -- Default: “Implementation defined”

CREATE TABLE … (
  x BIGINT -- A NULL-able column
)

SELECT x
  FROM …
 ORDER BY x -- Where should the NULLs appear?
            -- It is “Implementation defined”
            -- Eighter before non-NULL values
            -- or after them, but not inbetween

CREATE TABLE … (
  x BIGINT -- A NULL-able column
)

SELECT x
  FROM …
 ORDER BY x -- Where should the NULLs appear?
            -- It is “Implementation defined”
            -- Eighter before non-NULL values
            -- or after them, but not inbetween

CREATE TABLE … (
  x BIGINT -- A NULL-able column
)

SELECT x
  FROM …
 ORDER BY x [ASC|DESC] NULLS FIRST
            -- Since 2003, the NULLS FIRST|LAST
            -- option is standardized

SELECT GREATEST(a, b, c)
     , LEAST(a, b, c)
  FROM …

SELECT g
     , x -- selecting non-grouping column is not allowed
  FROM …
 GROUP BY g

SELECT g
     , x -- selecting non-grouping column is not allowed
  FROM …
 GROUP BY g

SELECT g
     , ANY_VALUE(x) -- just take any non-null value     
  FROM …
 GROUP BY g

[ {"product": 42}, {"product": 123, "qty": 2} ]

[ {"product": 42}, {"product": 123, "qty": 2} ]

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit

         COLUMNS ()

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit

         COLUMNS ()

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS ( /* after specifying the rows to produce,
                      let's declare the columns */           )

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
--[column name]---^^^^^^^                                    )

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
----------[column type]---^^^                                )

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
----------[Path: Which JSON item]---^^^^^^^^^                )

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
----[What if the path doesn't hit anything?]---^^^^^^^^^^^^^^)

       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
                  qty     INT PATH '$.qty' DEFAULT 1 ON EMPTY)
-------------[If "qty" is missing, use 1]--^^^^^^^^^^^^^^^^^^
       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
                  qty     INT PATH '$.qty' DEFAULT 1 ON EMPTY)
         ERROR ON ERROR -- remaining errors abort
       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

[ {"product": 42}, {"product": 123, "qty": 2} ]

INSERT INTO order_lines (product, qty) -- directly load into table
SELECT product, qty
  FROM JSON_TABLE(?,    -- bind parameter for the JSON document
                 '$[*]' -- SQL JSON/Path: one row per hit
         COLUMNS (product INT PATH '$.product' ERROR ON EMPTY,
                  qty     INT PATH '$.qty' DEFAULT 1 ON EMPTY)
         ERROR ON ERROR -- remaining errors abort
       ) t

| product | qty |
+---------+-----+
|      42 |   1 |
|     123 |   2 | 

CREATE JSON DUALITY VIEW v AS 
SELECT JSON {'_id': {'cid': cid, 'ts': ts},
             'items': [ SELECT JSON {'product': product
                                    ,'qty'    : qty
                                    }
                          FROM order_items
                          WITH INSERT UPDATE DELETE
                         WHERE ol.cid=o.cid AND ol.ts=o.ts
                      ]
            }
  FROM orders WITH INSERT UPDATE DELETE

CREATE JSON DUALITY VIEW v AS 
SELECT JSON {'_id': {'cid': cid, 'ts': ts},
             'items': [ SELECT JSON {'product': product
                                    ,'qty'    : qty
                                    }
                          FROM order_items
                          WITH INSERT UPDATE DELETE
                         WHERE ol.cid=o.cid AND ol.ts=o.ts
                      ]
            }
  FROM orders WITH INSERT UPDATE DELETE

INSERT INTO v VALUES ('{"_id":{"cid": 1}
                       ,"items": [{"product": 42}
                                 ,{"product": 123, "qty":2}
                                 ]
                       }')

INSERT INTO v VALUES ('{"_id":{"cid": 1}
                       ,"items": [{"product": 42}
                                 ,{"product": 123, "qty":2}
                                 ]
                       }')

SELECT * FROM orders

   | cid | ts  |
   +-----+-----+
   |   1 | ... |
   |   1 | ... |

  SELECT * FROM order_items

| cid | ts  | product | qty |
+-----+-----+---------+-----+
|   1 | ... |      42 |   1 |
|   1 | ... |     123 |   2 |

ORA-01795: maximum number of expressions in a list is 1000

SELECT tbl.i
  FROM tbl
 WHERE i IN (SELECT x        -- vvvvv--[in practice: bind param]
               FROM JSON_TABLE('[1,3]'
                              ,'$[*]'
                      COLUMNS (x INTEGER PATH '$')
                      ERROR ON ERROR
                    ) jt
            )

SELECT tbl.i
  FROM tbl
 WHERE i IN (SELECT x        -- vvvvv--[in practice: bind param]
               FROM JSON_TABLE('[1,3]'
                              ,'$[*]'
                      COLUMNS (x INTEGER PATH '$')
                      ERROR ON ERROR
                    ) jt
            )

PostgreSQL: SELECT i::INT FROM json_array_elements_text(?) t(i)
    SQLite: SELECT value  FROM json_each(?)
SQL Server: SELECT value  FROM openjson(?)

SELECT SUM(revenue) FILTER(WHERE year = 2024) AS revenue_2024
     , SUM(revenue) FILTER(WHERE year = 2023) AS revenue_2023
     , …
  FROM sales
 GROUP BY year

SELECT SUM(revenue) FILTER(WHERE year = 2024) AS revenue_2024
     , SUM(revenue) FILTER(WHERE year = 2023) AS revenue_2023
     , …
  FROM sales
 GROUP BY year

SELECT JSON_OBJECTAGG(year VALUE revenue)
  FROM (SELECT year, SUM(revenue) revenue
          FROM sales
         GROUP BY year
       ) t

| year | revenue |
+------+---------+
| 2024 |   ...   |
| 2023 |   ...   |
| ...  |   ...   |

SELECT SUM(revenue) FILTER(WHERE year = 2024) AS revenue_2024
     , SUM(revenue) FILTER(WHERE year = 2023) AS revenue_2023
     , …
  FROM sales
 GROUP BY year

SELECT JSON_OBJECTAGG(year VALUE revenue)
  FROM (SELECT year, SUM(revenue) revenue
          FROM sales
         GROUP BY year
       ) t

{"2024": ...      
,"2023": ...
...
}

SELECT SUM(revenue) FILTER(WHERE year = 2024) AS revenue_2024
     , SUM(revenue) FILTER(WHERE year = 2023) AS revenue_2023
     , …
  FROM sales
 GROUP BY year

SELECT JSON_OBJECTAGG(year VALUE revenue)
  FROM (SELECT year, SUM(revenue) revenue
          FROM sales
         GROUP BY year
       ) t

{"2024": ...      
,"2023": ...
...
}

WITH RECURSIVE prev (id, parent) AS (
   SELECT t.id, t.parent
     FROM t
    WHERE t.id = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
    WHERE t.parent = 42

       ON t.parent = prev.id

)
SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS (
   SELECT t.id, t.parent
     FROM t
    WHERE t.id = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
    WHERE t.parent = 42

       ON t.parent = prev.id

)
SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS (
   SELECT t.id, t.parent
     FROM t
    WHERE t.id = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
    WHERE t.parent = 42

       ON t.parent = prev.id

)
SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS (
   SELECT t.id, t.parent
     FROM t
    WHERE t.id = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
    WHERE t.parent = 42

       ON t.parent = prev.id

)
SELECT * FROM prev

WITH RECURSIVE prev (id, parent) AS (
   SELECT t.id, t.parent
     FROM t
    WHERE t.id = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
 /* WHERE t.parent = 42 */
     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 = 42
UNION ALL
   SELECT t.id, t.parent
     FROM t
 /* WHERE t.parent = 42 */
     JOIN prev
    /* ON t.parent = prev.id */
       ON t.id     = prev.parent
)
SELECT * FROM prev

CREATE PROPERTY GRAPH grph
 VERTEX TABLES (
  t AS item KEY (id)
 )
 EDGE TABLES (
  t AS parent KEY (id)
       SOURCE KEY (id)     REFERENCES item (id)
  DESTINATION KEY (parent) REFERENCES item (id)
 )

CREATE PROPERTY GRAPH grph
 VERTEX TABLES (
  t AS item KEY (id)
 )
 EDGE TABLES (
  t AS parent KEY (id)
       SOURCE KEY (id)     REFERENCES item (id)
  DESTINATION KEY (parent) REFERENCES item (id)
 )

SELECT *
  FROM GRAPH_TABLE (grph
        MATCH (i0) <-[]- {,10} (ix)   

        WHERE  i0.id = 42
        COLUMNS (ix.*) 
       ) gt
 ORDER BY id

CREATE PROPERTY GRAPH grph
 VERTEX TABLES (
  t AS item KEY (id)
 )
 EDGE TABLES (
  t AS parent KEY (id)
       SOURCE KEY (id)     REFERENCES item (id)
  DESTINATION KEY (parent) REFERENCES item (id)
 )

SELECT *
  FROM GRAPH_TABLE (grph
     /* MATCH (i0) <-[]- {,10} (ix) */
        MATCH (i0)  -[]->{,10} (ix)
        WHERE  i0.id = 42
        COLUMNS (ix.*) 
       ) gt
 ORDER BY id