1. Overview

This page has more details on the supported SQL in simple-sql-parser.

See the simple-sql-parser test cases page for examples.

The target dialect of SQL at this time is ISO/ANSI SQL:2011. The parser supports queries, DDL, non-query DML, access control and transaction management syntax. The parser and syntax does not follow the standard grammar closely - they permit a lot of things which the grammar in the standard forbids. The intended usage is that an additional pass over the ast can be made if you want to carefully prohibit everything that the standard doesn’t allow.

Apart from this permissiveness, some work has been put into trying to get good parser error messages.

2. Queries

2.1. Select lists

Supports scalar expressions, aliases with optional 'as'.

Doesn’t support 'select * as (a,b,c) from t' yet.

2.2. Set quantifiers on select

Supports 'select distinct' and explicit 'select all'.

2.3. From clause

  • aliases

  • subqueries

  • functions

  • joins

    • natural

    • inner

    • left/right/full outer

    • cross

    • on expressions

    • using lists

    • lateral

2.4. Group by clause

Supports scalar expressions, group by (), cube, rollup, grouping parentheses and grouping sets with nested grouping expressions.

2.5. Order by clause

Supports scalar expressions, asc/desc and nulls first/last.

2.6. Offset and fetch

Supports 'offset n rows' and 'fetch first n rows only'.

2.7. Set operators

Union, except, intersect + all/distinct and corresponding.

2.8. Table value constructor

For example: values (1,2),(3,4).

2.9. Explicit table

For example: 'table t', which is shorthand for 'select * from t'.

2.10. Scalar expressions

The scalar expressions type and parser is used in many contexts, including:

  • select lists;

  • where clause expressions;

  • group by clause expressions;

  • having clause expressions;

  • order by clause expressions;

  • offset and fetch clause expressions;

  • table value constructors.

This doesn’t exactly follow the ANSI Standards, which have separate grammars for most of these.

The supported scalar expressions include:

  • basic string literals in single quotes

  • number literals: digits.digitse+-exp

  • explicitly typed literal, e.g. int '3'

  • binary operators

    • comparisons: = != <> ⇐ >= < >

    • arithmetic: + - / * % ^

    • logic: and, or

    • bitwise: & | (and ^ as above)

    • string: ||, like, not like

    • other: overlaps, is similar to, is not similar too, is distinct from, is not distinct from

  • prefix unary operators

    • +, -

    • not

    • ~

  • postfix unary

    • is null, is not null

    • is true, is not true, is false, is not false, is unknown, is not unknown

  • other operators

    • extract (extract(day from dt))

    • position (position string1 in string2)

    • substring (substring(x from 2 for 4))

    • convert (convert(string using conversion))

    • translate (translate(string using translation))

    • overlay (overlay (string placing embedded_string from start for length))

    • trim (trim(leading '_' from s))

    • between (a between 1 and 5)

    • in list (a in (1,2,3,4))

    • cast (cast(a as int))

  • subqueries

    • in subquery

    • any/some/all

    • exists

  • case expressions

  • parentheses

  • quoted and unquoted identifiers

  • a.b qualified identifiers

  • *, a.*

  • functions: f(a,b)

  • aggregates: agg(distinct a order by b)

  • window functions: sum(x) over (partition by y order by z) plus some explicit frame support (same as in postgres 9.3)

  • row constructors, e.g. where (a,b) = any (select a,b from t)

  • ? used in parameterized queries

3. DDL

  • schemas

    • create, drop + drop restrict

  • tables

    • create table

      • constraints: named, null, unique, primary key, foreign key (matches, on update/delete)

      • identity (the weird ansi version), defaults

      • defaults

    • alter table

      • defaults, null, set data type, drop column, constraints

    • drop table + restrict

    • create, drop view

    • create, alter, drop domain

      • defaults, constraints

    • create, drop assertion

    • create, alter, drop sequence

4. Non-query DML

  • delete

    • delete from

    • as alias

    • where

  • truncate

    • with identity options

  • insert

    • values, general queries, defaults

  • update

    • including row updates

5. Access Control

  • grant privileges

    • all, grant option, table, domain, type, sequence, role, etc.

  • revoke

  • create role, drop role

6. Transaction management

  • begin, commit, rollback

  • savepoints