SQL grammar¶
SQL grammar¶
select: SELECT [ STREAM ] [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] [ HAVING booleanExpression ] [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ] selectWithoutFrom: SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } projectItem: expression [ [ AS ] columnAlias ] | tableAlias . * tableExpression: tableReference [, tableReference ]* | tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ] | tableExpression CROSS JOIN tableExpression | tableExpression [ CROSS | OUTER ] APPLY tableExpression joinCondition: ON booleanExpression | USING '(' column [, column ]* ')' tableReference: tablePrimary [ FOR SYSTEM_TIME AS OF expression ] [ matchRecognize ] [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ] tablePrimary: [ [ catalogName . ] schemaName . ] tableName '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')' | tablePrimary [ EXTEND ] '(' columnDecl [, columnDecl ]* ')' | [ LATERAL ] '(' query ')' | UNNEST '(' expression ')' [ WITH ORDINALITY ] | [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')' columnDecl: column type [ NOT NULL ] aggregateCall: agg( [ ALL | DISTINCT ] value [, value ]*) [ WITHIN GROUP (ORDER BY orderItem [, orderItem ]*) ] [ FILTER (WHERE condition) ] | agg(*) [ FILTER (WHERE condition) ] describe: DESCRIBE DATABASE databaseName | DESCRIBE CATALOG [ databaseName . ] catalogName | DESCRIBE SCHEMA [ [ databaseName . ] catalogName ] . schemaName | DESCRIBE [ TABLE ] [ [ [ databaseName . ] catalogName . ] schemaName . ] tableName [ columnName ] | DESCRIBE [ STATEMENT ] ( query | insert | update | merge | delete )
Functions and Operators¶
Conditional Expressions support: case | if (1)case SELECT CASE type WHEN 'a' THEN 'b' ELSE 'c' END FROM db.tablename (2)if SELECT IF(10 > 1, 'hello', 2) FROM db.tablename Comparison operators support: = | <> | != | > | >= | < | <= | is null | is not null | between and | not between and| like | not like | in (sub-query) | not in(sub_query) | exists (sub-query) | not exists(sub-query) (1)= SELECT type FROM db.tablename where type=value (2)<> | != SELECT type FROM db.tablename where [type<> | !=] value (3)> SELECT type FROM db.tablename where type>value (4)>= SELECT type FROM db.tablename where type>=value (5)< SELECT type FROM db.tablename where type<value (6)<= SELECT type FROM db.tablename where type<=value (7)is null | is not null SELECT type FROM db.tablename where type [is null | is not null] (8)between and | not between and SELECT type FROM db.tablename where type [between value1 and value2 | not between value1 and value2] (9)like | not like SELECT type FROM db.tablename where type [like | not like] [value|"%value"|"%value%"|"value%"|...] (10)in | not in SELECT type FROM db.tablename where type [in|not in] (sub-query|value-list) (11)exists | not exists SELECT type FROM db.tablename where type [exists | not exists] (sub-query) Logical operators support: boolean1 or boolean2 | boolean1 and boolean2 | not boolean | boolean is false |boolean is not false | boolean is true | boolean is not true | boolean is unknown |boolean is not unknown (1)boolean1 or boolean2 --> boolean Arithmetic operators and functions support: + | - | * | / | % | POWER(numeric1, numeric2) | ABS(numeric) | MOD(numeric1, numeric2) | SQRT(numeric) | LN(numeric) | LOG10(numeric) | EXP(numeric) | CEIL(numeric) | FLOOR(numeric) | RAND([seed]) | RAND_INTEGER([seed, ] numeric) | ACOS(numeric) | ASIN(numeric) | ATAN(numeric) | ATAN2(numeric, numeric) | COS(numeric) | COT(numeric) | PI() | RADIANS(numeric) | ROUND(numeric1 [, numeric2]) | SIGN(numeric) | SIN(numeric) | TAN(numeric) | TRUNCATE(numeric1 [, numeric2]) (1)+ --> numeric1 + numeric2 = numeric (2)- --> numeric1 - numeric2 = numeric Character string operators and functions support: string || string | concat(string1, ..., stringN) | CHAR_LENGTH(string) | CHARACTER_LENGTH(string) | UPPER(string) | LOWER(string) | POSITION(string1 IN string2) | POSITION(string1 IN string2 FROM integer) | TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2) | OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ]) | SUBSTRING(string FROM integer) | SUBSTRING(string FROM integer FOR integer) | INITCAP(string) (1)[|| | concat(string1, ..., stringN)] --> Returns the concatenation of string1...stringN (2)CHAR_LENGTH(string) --> Returns the number of characters in a character string Date/time functions support: LOCALTIME | LOCALTIME(precision) | LOCALTIMESTAMP | LOCALTIMESTAMP(precision) | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP | EXTRACT(timeUnit FROM datetime) | FLOOR(datetime TO timeUnit) | CEIL(datetime TO timeUnit) | YEAR(date) | QUARTER(date) | MONTH(date) | WEEK(date) | DAYOFYEAR(date) | DAYOFMONTH(date) | DAYOFWEEK(date) | HOUR(date) | MINUTE(date) | SECOND(date) | TIMESTAMPADD(timeUnit, integer, datetime) | TIMESTAMPDIFF(timeUnit, datetime, datetime2) | LAST_DAY(date) Aggregate functions support: COUNT(*|1|...) | SUM([ALL | DISTINCT]numeric) | AVG([ALL | DISTINCT]numeric) | MAX([ALL | DISTINCT]value) | MIN([ALL | DISTINCT]value)
Key words¶
support: A | ABS | ABSOLUTE | ACTION | ADA | AND | AS | ADMIN | AVG | AFTER | ALL | ALLOCATE | ALLOW | ALTER | ALWAYS | ANY | APPLY | ARE | ARRAY | ARRAY_MAX_CARDINALITY | ASC | ASENSITIVE | ASSERTION | ASSIGNMENT | ASYMMETRIC | AT | ATOMIC | ATTRIBUTE | ATTRIBUTES | AUTHORIZATION BETWEEN | BY | BEFORE | BEGIN | BEGIN_FRAME | BEGIN_PARTITION | BERNOULLI | BIGINT | BINARY | BITBLOB | BOOLEAN | BOTH | BREADTH C | CAST | CASE | CALL | CALLED | CARDINALITY | CASCADE | CASCADED | CATALOG | CATALOCEILG_NAME | CEIL | CEILING | CENTURY | CHAIN | CHAR | CHAR_LENGTH | CHARACTER CHARACTER_LENGTH | CHARACTER_SET_CATALOG | CHARACTER_SET_NAME | CHARACTER_SET_SCHEMA CHARACTERISTICS | CHARACTERS | CHECK | CLASSIFIER | CLASS_ORIGIN | CLOB | CLOSE | COALESCE | COBOL | COLLATE | COLLATION_CATALOG | COLLATION | COLLATION_NAME | COLLATION_SCHEMA | COLLECT | COLUMN_NAME | COLUMN | COMMAND_FUNCTION | COMMAND_FUNCTION_CODE | COMMIT | COMMITTED | CONDITION | CONDITION_NUMBER | CONNECT | CONNECTION | CONNECTION_NAME | CONSTRAINT | CONSTRAINT_CATALOG | CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CONSTRAINTS | CONSTRUCTOR | CONTAINS | CONTINUE CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP | COVAR_SAMP | CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_CATALOG | CURRENT_DATE | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_ROW | CURRENT_SCHEMA | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER | CURSOR | CURSOR_NAME | CYCLE DATA | DATABASE | DATE | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DAY | DEALLOCATE | DEC | DECADE | DECIMAL | DECLARE | DEFAULT_ | DEFAULTS | DEFERRABLE | DEFERRED | DEFINE | DEFINED | DEFINER | DEGREE | DELETE | DENSE_RANK | DEPTH | DEREF | DERIVED | DESC | DESCRIBE | DESCRIPTION | DESCRIPTOR | DETERMINISTIC | DIAGNOSTICS | DISALLOW | DISCONNECT | DISPATCH | DISTINCT | DOMAIN | DOUBLE | DOW | DOY | DROP | DYNAMIC | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE EACH | ELEMENT | ELSE | EMPTY | END | END_EXEC | END_FRAME | END_PARTITION | EPOCH | EQUALS | ESCAPE | EVERY | EXCEPT | EXCEPTION | EXCLUDE | EXCLUDING | EXEC | EXECUTE | EXISTS | EXP | EXPLAIN | EXTEND | EXTERNAL | EXTRACT FALSE | FETCH | FILTER | FINAL | FIRST | FIRST_VALUE | FLOAT | FLOOR | FOLLOWING | FOR | FOREIGN | FORTRAN | FOUND | FRAC_SECOND | FRAME_ROW | FREE | FROM | FULL | FUNCTION | FUSION G | GENERAL | GENERATED | GEOMETRY | GET | GLOBAL | GO | GOTO | GRANT | GROUP | GRANTED | GROUPS HAVING | HOLD | HIERARCHY | HOUR IDENTITY | IMMEDIATE | IMMEDIATELY | IMPLEMENTATION | IMPORT | IN | INCLUDING | INCREMENT | INDICATOR | INITIAL | INITIALLY | INNER | INOUT | INPUT | INSENSITIVE | INSERT | INSTANCE | INSTANTIABLE | INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | INVOKER | IS | ISOYEAR | ISODOW | ISOLATION JAVA | JOIN | JSON K | KEY | KEY_MEMBER | KEY_TYPE LABEL | LAG | LANGUAGE | LARGE | LAST | LAST_VALUE | LATERAL | LEAD | LEADING | LEFT | LENGTH | LEVEL | LIBRARY | LIKE | LIKE_REGEX | LIMIT | LN | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOCATOR | LOWER M | MAP | MATCH | MATCHED | MATCHES | MATCH_NUMBER | MATCH_RECOGNIZE | MAX | MAXVALUE | MEASURES | MEMBER | MERGE | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH | MESSAGE_TEXT | METHOD | MICROSECOND | MILLISECOND | MIN | MILLENNIUM | MINUTE | MINVALUE | MOD | MODIFIES | MODULE | MONTH | MORE_ | MULTISET | MUMPS NAME | NAMES | NANOSECOND | NATIONAL | NCHAR | NATURAL | NCLOB | NESTING | NEW | NEXT | NNONEO | NORMALIZE | NORMALIZED | NOT | NTH_VALUE | NTILE | NULL | NULLABLE | NULLIF | NULLS | NUMBER | NUMERIC OBJECT | OCCURRENCES_REGEX | OCTET_LENGTH | OCTETS | OF | OFFSET | OLD | OMIT | ON | ONE | ONLY | OPEN | OPTION | OPTIONS | OR | ORDER | ORDERING | ORDINALITY | OTHERS | OUT | OUTER | OUTPUT | OVER | OVERLAPS | OVERLAY | OVERRIDING PAD | PARAMETER | PARAMETER_MODE | PARAMETER_NAME | PARAMETER_ORDINAL_POSITION | PARAMETER_SPECIFIC_CATALOG | PARAMETER_SPECIFIC_NAME | PARAMETER_SPECIFIC_SCHEMA | PARTITION | PARTIAL | PASCAL | PASSTHROUGH | PAST | PATH | PATTERN | PER | PERCENT | PERCENTILE_CONT | PERCENTILE_DISC | PERCENT_RANK | PERIOD | PERMUTE | PLACING | PLAN | PLIPORTION | POSITION | POSITION_REGEX | POWER | PRECEDES | PRECEDING | PREPARE | PRECISION | PRESERVE | PREV | PRIMARY | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC QUARTER RANGE | RANK | READ | READS | REAL | RECURSIVE | REFERENCES | REF | REFERENCING | REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 | REGR_SLOPE | REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RELATIVE | REPEATABLE | REPLACE | RESET | RESTART | RESTRICT | RESULT | RETURN | RETURNED_CARDINALITY | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE | RETURNS | REVOKE | RIGHT | ROLE | ROLLBACK | ROLLUP | ROUTINE | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_SCHEMA | ROW | ROW_COUNT | ROW_NUMBER | ROWS | RUNNING SAVEPOINT | SCALE | SCHEMA | SCHEMA_NAME | SCOPE | SCOPE_CATALOGS | SCOPE_NAME | SCOPE_SCHEMA | SCROLL | SEARCH | SECOND | SECTION | SECURITY | SEEK | SELECT | SELF | SENSITIVE | SEQUENCE | SERIALIZABLE | SERVER | SERVER_NAME | SESSION | SESSION_USER | SET | SETS | SET_MINUS | SHOW | SIMILAR | SIMPLE | SIZE | SKIP_ | SMALLINT | SOME | SOURCE | SPACE | SPECIFIC | SPECIFIC_NAME | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQL_BIGINT | SQL_BINARY | SQL_BIT | SQL_BLOB | SQL_BOOLEAN | SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL | SQL_DOUBLE | SQL_FLOAT | SQL_INTEGER | SQL_INTERVAL_DAY | SQL_INTERVAL_DAY_TO_HOUR | SQL_INTERVAL_DAY_TO_MINUTE | SQL_INTERVAL_DAY_TO_SECOND | SQL_INTERVAL_HOUR | SQL_INTERVAL_HOUR_TO_MINUTE | SQL_INTERVAL_HOUR_TO_SECOND | SQL_INTERVAL_MINUTE | SQL_INTERVAL_MINUTE_TO_SECOND | SQL_INTERVAL_MONTH | SQL_INTERVAL_SECOND | SQL_INTERVAL_YEAR | SQL_INTERVAL_YEAR_TO_MONTH | SQL_LONGVARBINARY | SQL_LONGVARCHAR | SQL_LONGVARNCHAR | SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC | SQL_NVARCHAR | SQL_REAL | SQL_SMALLINT | SQL_TIME | SQL_TIMESTAMP | SQL_TINYINT | SQL_TSI_DAY | SQL_TSI_FRAC_SECOND | SQL_TSI_HOUR | SQL_TSI_MICROSECOND | SQL_TSI_MINUTE | SQL_TSI_MONTH | SQL_TSI_QUARTER | SQL_TSI_SECOND | SQL_TSI_WEEK | SQL_TSI_YEAR | SQL_VARBINARY | SQL_VARCHAR | SQRT | START | STATE | STATEMENT | STATIC | STDDEV_POP | STDDEV_SAMP | STREAM | STRUCTURE | STYLE | SUBCLASS_ORIGIN | SUBMULTISET | SUBSET | SUBSTITUTE | SUBSTRING | SUBSTRING_REGEX | SUCCEEDS | SUM | SYMMETRIC | SYSTEM | SYSTEM_TIME | SYSTEM_USER TABLE | TABLE_NAME | TABLESAMPLE | TEMPORARY | THEN | TIES | TIME | TIMESTAMP | TIMESTAMPADD | TIMESTAMPDIFF | TIMEZONE_HOUR | TIMEZONE_MINUTE | TINYINT | TO | TOP_LEVEL_COUNT | TRAILING | TRANSACTION | TRANSACTIONS_ACTIVE | TRANSACTIONS_COMMITTED | TRANSACTIONS_ROLLED_BACK | TRANSFORM | TRANSFORMS | TRANSLATE | TRANSLATE_REGEX | TRANSLATION | TREAT | TRIGGER | TRIGGER_CATALOG | TRIGGER_NAME | TRIGGER_SCHEMA | TRIM | TRIM_ARRAY | TRUE | TRUNCATE | TYPE UESCAPE | UNBOUNDED | UNCOMMITTED | UNDER | UNION | UNIQUE | UNKNOWN | UNNAMED | UNNEST | UPDATE | UPPER | UPSERT | USAGE | USER | USER_DEFINED_TYPE_CATALOG | USER_DEFINED_TYPE_CODE | USER_DEFINED_TYPE_NAME | USER_DEFINED_TYPE_SCHEMA | USING VALUE | VALUES | VALUE_OF | VAR_POP | VAR_SAMP | VARBINARY | VARCHAR | VARYING | VERSION | VERSIONING | VIEW WEEK | WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN | WITHOUT | WORK | WRAPPER | WRITE | XML | YEAR | ZONE
Data Types¶
lsupport: ARRAY BIGINT BOOLEAN DOUBLE DECIMAL DATE FLOAT INT INTEGER LONG MAP STRING SMALLINT TINYINT TIMESTAMP VARCHAR
Last update: January 6, 2020