Database SQL – Why Do Relational Databases Only Accept SQL Queries?

databasesql

As far as I know, most relational databases do not offer any driver-level API for queries, except a query function which takes an SQL string as an argument.

I'm thinking how easier it would be if one could do:

var result = mysql.select('article', {id: 3})

For joined tables, it would be slightly more complex, but still possible. For example:

var tables = mysql.join({tables: ['article', 'category'], on: 'categoryID'});
mysql.select(tables, {'article.id': 3}, ['article.title', 'article.body', 'category.categoryID'])

Cleaner code, no string parsing overhead, no injection problems, easier reuse of query elements… I can see a lot of advantages.

Is there a specific reason why it was chosen to only provide access to queries through SQL?

Best Answer

Databases are out of process - they run on a different server usually. So even if you had an API, it would need to send something across the wire that represents your query and all of its projections, filters, groups, subqueries, expressions, joins, aggregate functions etc. That something could be XML or JSON or some proprietary format, but it may as well be SQL because that is tried, tested and supported.

It is less common these days to build up SQL commands yourself - many people use some sort of ORM. Even though these ultimately translate into SQL statements, they may provide the API you are after.

Related Solutions

How to Format SQL Queries – Best Practices

For source control reasons, we have linebreaks after every where clause, or comma. So your above turns into

SELECT bla 
     , bla2 
     , bla 
FROM   bla 
WHERE  bla=333 
  AND  bla=2
ORDER  BY nfdfsd
        , asdlfk;

(tabbing and alignment has no standard here, but commas are usually leading)

Still, makes no performance difference.

Database – Why Relational Databases Don’t Support Nested Format Queries

C. J. Date goes into detail about this in Chapter 7 and Appendix B of SQL and Relational Theory. You're right, there's nothing in relational theory that prohibits an attribute's data type from being a relation itself, as long as it's the same relation type on every row. Your example would qualify.

But Date says structures like this are "usually--but not invariably--contraindicated" (i.e. a Bad Idea) because hierarchies of relations are asymmetric. For example, a transformation from nested structure to a familiar "flat" structure cannot always be reversed to recreate the nesting.

Queries, constraints, and updates are more complex, harder to write, and harder for the RDBMS to support if you allow relation-valued attributes (RVA's).

It also muddies database design principles, because the best hierarchy of relations isn't so clear. Should we design a relation of Suppliers with a nested RVA for parts supplied by a given Supplier? Or a relation of Parts with a nested RVA for suppliers who supply a given Part? Or store both, to make it easy to run different types of queries?

This is the same dilemma that results from the hierarchical database and the document-oriented database models. Eventually, the complexity and cost of accessing nested data structures drives designers to store data redundantly for easier lookup by different queries. The relational model discourages redundancy, so RVA's can work against the goals of relational modeling.

From what I understand (I have not used them), Rel and Dataphor are RDBMS projects that support relation-valued attributes.

Re comment from @dportas:

Structured types are part of SQL-99, and Oracle supports these. But they don't store multiple tuples in the nested table per row of the base table. The common example is an "address" attribute which appears to be a single column of the base table, but has further sub-columns for street, city, postal code, etc.

Nested tables are also supported by Oracle, and these do allow multiple tuples per row of the base table. But I am not aware that this is part of standard SQL. And keep in mind the conclusion of one blog: "I'll never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!"

Best Answer

Related Solutions

How to Format SQL Queries – Best Practices

Database – Why Relational Databases Don’t Support Nested Format Queries

Related Topic