Supported SQL statements

Supported SQL statements

• SELECT TOP number columns INTO table FROM tableid1 JOIN tableid2 ON oncond WHERE cond GROUP BY v1,v2 HAVING cond ORDER BY a,b, LIMIT number OFFSET number
• INSERT INTO table [ (field1, field2) ] VALUES (value1, value2), (value3, value4), ...
• INSERT INTO table SELECT subquery
• UPDATE table SET field = value1, field = value2 WHERE condition
• DELETE FROM table WHERE condition
• CREATE TABLE [IF NOT EXISTS] table (columns type PRIMARY KEY, constraints)
• ALTER TABLE ADD COLUMN / DROP COLUMN
• DROP TABLE [IF EXISTS] table
• CREATE DATABASE, USE DATABASE, DROP DATABASE
• SHOW DATABASES / SHOW TABLES / SHOW COLUMNS / SHOW CREATE TABLE
• SOURCE 'url-file.sql'
• ASSERT json-object
• Expression (like SELECT expression)

Try all these statements in AlaSQL console

SELECT statement

AlaSQL.js supports following subset of SELECT syntax:

• SELECT column1, column2 AS alias3, FUNCTION(field4+field5) AS alias6, SUM(expression7) AS alias8, , table2.
• TOP number
• FROM table1, table2, (SELECT * FROM table3) alias
• LEFT / RIGHT / INNER / OUTER / ANTI / SEMI / CROSS / NATURAL JOIN table2 ON condition / USING columns
• WHERE condition
• GROUP BY column1, column2, ROLLUP(a,b), CUBE(c,d,e), GROUPING SETS(g,h)
• HAVING condition
• ORDER BY column1, column2 DESC,
• LIMIT number [OFFSET number]
• UNION / UNION ALL select / INTERSECT / EXCEPT

Operators:

• +, -, *, /, %, AND, OR, NOT, BETWEEN, NOT BETWEEN, EXISTS (Subquery), > ALL (subquery/array), > ANY/SOME (subquery / array), [NOT] IN (subquery / array), LIKE
• CAST (expression AS type)

Aggregators:

• SUM(), COUNT(), MIN(), MAX(), FIRST(), LAST(), AVG(), AGGR(), ARRAY(), REDUCE()

GROUP BY Grouping functions:

• ROLLUP(), CUBE(), GROUPING SETS()

Functions:

• ABS(), IIF(), IFNULL(), INSTR(), LOWER(), UPPER(), LCASE(), UCASE(), LEN(), LENGTH()
• GREATEST(), LEAST()

SELECT modifiers (non-standard SQL):

• SELECT VALUE - get single value
• SELECT ROW - get first row as an array
• SELECT COLUMN - get first column as an array
• SELECT MATRIX - get all results as an array of arrays

See a complete list of SQL-99 statements and if they are supported or not

User-defined JavaScript functions

You can use all benefits of SQL and JavaScript togeather by defining user functions. Just add new functions to alasql.fn object:

        alasql.fn.double = function(x){return x*2};        
        alasql.fn.sum10 = function(x,y) { return x+y*10; }
        db.exec('SELECT a, double(a) AS b, sum10(a,b) FROM test1');

User-defined functions are related to current database. You can define different functions in different databases.