SQL vs. Bandicoot

Published by Julius Chrobak on 2011 11 01.

Every time I write something in Bandicoot I realize how different this is comparing to SQL. I must admit the concepts in Bandicoot are very natural for me and I'm enjoying Bandicoot programming a lot more than writing queries in SQL.

I've decided to put a small page together to show the differences. I'm going to do this informally and skip the definitions of relations, variables, operators, and other constructs from the relational model. All you can find on this page are one-to-one comparisons of the well known SQL expressions: select from, where, join, and group by.

SELECT FROM

This is the most used expression in SQL. It returns all the rows from a table, allows you to define which columns to pick, or define new derived columns:

select * from books

books

select title, author from books

books project(title, author)

select b.*, 2011 - year as age
  from books b

books extend(age = 2011 - year)

or everything together

select title, author, 2011 - year as age
  from books

books project(title, author)
      extend(age = 2011 - year)

WHERE

select *
  from books
 where author = 'John Irving'

books select(author == "John Irving")

JOIN

select *
  from books natural join stores

books * stores

note: there are several different join operators defined in SQL. Bandicoot supports only natural inner join (a full match on equality of all the columns with the same name and type):

GROUP BY

select genre, 
       count(*) as cnt,
       avg(pages) as pages
  from books
 group by genre

(books, books project(genre))
    summary(cnt = cnt(),
            pages = avg(pages, 0.0))

An Example

To get a better picture how this all works in reality I show you an example of a complete Bandicoot function. Let's imagine you have a simple database of books and stores and you want to find out what is the average price of books per author providing the genre you are interested in:

fn prices(g: string):
    rel {author: string, avgPrice: real}
{
    onSell := stores * (books select(genre == g));
    authors := onSell project(author);

    return (onSell, authors) summary(avgPrice = avg(price, 0.0));
}

To compare this with SQL I would need to pick a specific implementation of procedural language (ie Oracle's PL/SQL or Postgres' PL/pgSQL). To leave it simple I only write the select statement which executes the same calculation:

select author, avg(price) as avgPrice
  from books natural join stores
 where genre == g
 group by author