[/Band|c00t] GettingStarted Specification Download Blog About
Fork me on GitHub

SQL vs. Bandicoot v5

Published by Julius Chrobak on 2012 09 21.

This is an update of my previous blog on the SQL vs. Bandicoot comparison. You can find here are one-to-one comparisons of the well known SQL expressions and the Bandicoot v5 language.


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:

SQL Bandicoot
select * from books
select title, author from books
project (title, author) books
select b.*, 2011 - year as age
  from books b
extend (age = 2011 - year) books

or everything together

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


select *
  from books
 where author = 'John Irving'
select (author == "John Irving") books


select *
  from books natural join stores
join 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):


select genre, 
       count(*) as cnt,
       avg(pages) as pages
  from books
 group by genre
summary (cnt = cnt, pages = (avg pages 0.0))
         (project genre books)

All together (i.e. creating a function)

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) {author string, avgPrice real}
    var onSell = join stores (select (genre == g) books);

    return summary (avgPrice = (avg price 0.0))
                     (project author onSell);

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