Query Language Comparison

Here are some examples of database query languages that have at least one working implementation, for comparison:

See ExperimentalQueryLanguageComparison for examples in potential future languages.

Example 1: Retrieve all columns/attributes and rows/tuples from a table/RelVar

SQL:

 SELECT * FROM myThings

Dataphor:

 select myThings;

Tutorial D:

 myThings

Datalog:

 ?- myThings(A,B,C,Name) // browse the results
 myThings(A,B,C,Name) // if used in another query

Example 2: Restriction

SQL:

 SELECT * FROM myThings WHERE name = 'blah'

Dataphor:

 select myThings WHERE name = 'blah'

Tutorial D:

 myThings WHERE name = 'blah'

Datalog:

 ?- myThings(A,B,C,blah)

Example 3: Restriction and projection with unique result

SQL:

 SELECT DISTINCT a, b, c FROM myThings WHERE name = 'blah'
 DISTINCT is needed unless a, b, c constitute a key of myThings.

Dataphor:

 select myThings over {a, b, c} WHERE name = 'blah'

Tutorial D:

 (myThings WHERE name = 'blah') {a, b, c}

Datalog:

 query(A,B,C) :- myThings(A,B,C,blah)
 ?- query(A,B,C)

Example 3b: Restriction and projection without required uniqueness

SQL:

 SELECT a, b, c FROM myThings WHERE name = 'blah'

Example 4: Aggregation

SQL:

 SELECT snumber, COUNT(*) AS p_count FROM sp GROUP BY snumber

Dataphor:

 select sp group by {snumber} add { Count() p_count };

Tutorial D:

 SUMMARIZE sp BY {snumber} ADD (COUNT() AS p_count)

Datalog:

support for mutual recursion precludes internal support for aggregation; aggregate would need to be in higher language layer w/ scoped queries

Example 5: Insertion

SQL:

 INSERT INTO myThings(a, b, c, name) VALUES 
   (1, 2, 3, 'glub'),
   (4, 5, 6, 'glob');

Dataphor:

 insert table {
row {a 1, b 2, c 3, name 'glub'},
row {4, 5, 6,'glob'},
 } into myThings

Tutorial D:

 INSERT myThings RELATION {
TUPLE {a 1, b 2, c 3, name 'glub'},
TUPLE {a 4, b 5, c 6, name 'glob'}
 }

Datalog:

 myThings(1,2,3,glub).
 myThings(4,5,6,glob).

Example 6: Table/relvar creation

SQL:

 CREATE TABLE myThings (
name TEXT NOT NULL PRIMARY KEY,
a INTEGER,
b INTEGER,
c INTEGER
 )

Dataphor:

 create table Employee
 {
  name : String,
  a: Integer,
  b: Integer,
  c: Integer,
  key { name}
 };

Tutorial D:

 VAR myThings REAL RELATION {
name CHAR,
a INTEGER,
b INTEGER,
c INTEGER
 } KEY {name}

Datalog:

add first entry to 'myThings' when ready Cannot specify keys.

Example 7: Transitive closure or recursion - "all paths" of a digraph

Datalog:

 path(A,B) :- edge(A,B).
 path(A,B) :- edge(A,C),path(C,B).
 ?- path(A,B).

Tutorial D:

TCLOSE edges // presumes degree 2 and matching types

Example 8: Natural equi-join

SQL:

 SELECT foo, bar
 FROM red NATURAL JOIN blue

Tutorial D:

 (red JOIN blue) {foo, bar}

Datalog:

no 'natural' join

Example 9: Equi-join

SQL:

 SELECT foo, bar
 FROM red, blue
 WHERE x = y
 --
 -- Variation 2 --
 --
 SELECT foo, bar
 FROM red INNER JOIN blue
   ON x = y

Tutorial D:

 JOIN {red RENAME (x AS j), blue RENAME (y AS j)} {foo, bar}

Datalog:

 query(Foo,Bar) :- red(X,Foo),blue(X,Bar)
 ?- query(A,B)

Example 10: Equi-join with same-named key

SQL

 SELECT foo, bar
 FROM red INNER JOIN blue
   ON red.x = blue.x

Tutorial D:

 (red JOIN blue) {foo, bar}

Datalog:

attributes positional in standard Datalog

Example 11: Top 3 largest planets, use name if tie

SQL (Microsoft Dialect)

 SELECT TOP 3 * FROM planets
 ORDER BY diameter DESCENDING, name

Tutorial D:

 WITH (result := planets ORDER(DESC diameter, DESC name)) :
    WRITELN result[0] || result[1] || result[2];

Question: why not show the array manipulation portion for a fuller comparison? It also doesn't appear to address the "tie" requirements. (Dot is a work-around to a wiki formatting bug.)
Answer: The ARRAY manipulation is entirely dependent on what you intend to do with the results. A future update will provide built-in operators for slicing and dicing ARRAYs. Accessing ARRAY elements is via conventional indexing, e.g., p[n] where p is an ARRAY and n is a 0-based index. I have addressed the "tie" requirements.
Thanks for the tie adjustment. As far as the array, some kind of basic display ("print") or console output is probably sufficient for this context in my opinion. I would accept displaying just the planet name even though the other examples display the whole row because the requirements are ambiguous on that.
Done.
Thank You.

Added some DataLog comparisons above. At the moment, Datalog can operate as a query language excepting some functional transforms and a variety of aggregation operations (group by, count, min, max, top N, etc.). Datalog would need some tooling to serve as a data definition or manipulation language (mutations aren't supported, nor is defining tables with keys or enforcing constraints between relations).

In Datalog, aggregations and functions on infinite domains must be lifted into a higher layer - they compete with transitive closures and recursive queries in a manner such that having both features in the same layer leads readily to 'infinite' results sets. I favor lifting them into a 'functional' layer with scoped queries that simply return all the constructed relations. I might eventually explain all this on another page...

These examples would be informative, and more amenable to proof-reading, if they operated on a defined schema.

Good point. As time permits, I'll endeavour to change the above to use either Oracle's familiar DEPT & EMP or ChrisDate's classic SupplierPartsDatabase, etc., and lead off with the table/relvar definitions followed by the 'insert' examples. I'll change the SQL, Dataphor, Tutorial D, and possibly SMEQL (it seems intuitive) examples, but I'll leave the Datalog -- with which I have least familiarity -- examples to their original author.

Chapter 15 (http://web.cecs.pdx.edu/~maier/TheoryBook/MAIER/C15.pdf) of TheTheoryOfRelationalDatabases compares the way to write queries in many (now obsolete?) (pseudo?) relational query languages: ISBL, QUEL, QBE and PIQUE

See: TutorialDee, DataLog, AlphoraDataphor, StructuredQueryLanguage, TqlRoadmap (aka SMEQL), ExperimentalQueryLanguageDiscussion, HowOtherQueryLanguagesAddressSqlFlaws

CategoryQueryLanguage, CategoryProgrammingLanguageComparisons

FebruaryZeroNine