Skip to content

Latest commit

 

History

History
2373 lines (2113 loc) · 70.8 KB

sql.md

File metadata and controls

2373 lines (2113 loc) · 70.8 KB
Raw
title nav-parent_id nav-pos
SQL
tableapi
30

SQL queries are specified with the sql() method of the TableEnvironment. The method returns the result of the SQL query as a Table. A Table can be used in subsequent SQL and Table API queries, be converted into a DataSet or DataStream, or written to a TableSink). SQL and Table API queries can seamlessly mixed and are holistically optimized and translated into a single program.

In order to access a table in a SQL query, it must be registered in the TableEnvironment. A table can be registered from a TableSource, Table, DataStream, or DataSet. Alternatively, users can also register external catalogs in a TableEnvironment to specify the location of the data sources.

For convenience Table.toString() automatically registers the table under a unique name in its TableEnvironment and returns the name. Hence, Table objects can be directly inlined into SQL queries (by string concatenation) as shown in the examples below.

Note: Flink's SQL support is not yet feature complete. Queries that include unsupported SQL features cause a TableException. The supported features of SQL on batch and streaming tables are listed in the following sections.

  • This will be replaced by the TOC {:toc}

Specifying a Query

The following examples show how to specify a SQL queries on registered and inlined tables.

{% highlight java %} StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); StreamTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env);

// ingest a DataStream from an external source DataStream<Tuple3<Long, String, Integer>> ds = env.addSource(...);

// SQL query with an inlined (unregistered) table Table table = tableEnv.toTable(ds, "user, product, amount"); Table result = tableEnv.sql( "SELECT SUM(amount) FROM " + table + " WHERE product LIKE '%Rubber%'");

// SQL query with a registered table // register the DataStream as table "Orders" tableEnv.registerDataStream("Orders", ds, "user, product, amount"); // run a SQL query on the Table and retrieve the result as a new Table Table result2 = tableEnv.sql( "SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'"); {% endhighlight %}

{% highlight scala %} val env = StreamExecutionEnvironment.getExecutionEnvironment val tableEnv = TableEnvironment.getTableEnvironment(env)

// read a DataStream from an external source val ds: DataStream[(Long, String, Integer)] = env.addSource(...)

// SQL query with an inlined (unregistered) table val table = ds.toTable(tableEnv, 'user, 'product, 'amount) val result = tableEnv.sql( s"SELECT SUM(amount) FROM $table WHERE product LIKE '%Rubber%'")

// SQL query with a registered table // register the DataStream under the name "Orders" tableEnv.registerDataStream("Orders", ds, 'user, 'product, 'amount) // run a SQL query on the Table and retrieve the result as a new Table val result2 = tableEnv.sql( "SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'") {% endhighlight %}

{% top %}

Supported Syntax

Flink parses SQL using Apache Calcite, which supports standard ANSI SQL. DML and DDL statements are not supported by Flink.

The following BNF-grammar describes the superset of supported SQL features in batch and streaming queries. The Operations section shows examples for the supported features and indicates which features are only supported for batch or streaming queries.


query:
  values
  | {
      select
      | selectWithoutFrom
      | query UNION [ ALL ] query
      | query EXCEPT query
      | query INTERSECT query
    }
    [ ORDER BY orderItem [, orderItem ]* ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY]

orderItem:
  expression [ ASC | DESC ]

select:
  SELECT [ ALL | DISTINCT ]
  { * | projectItem [, projectItem ]* }
  FROM tableExpression
  [ WHERE booleanExpression ]
  [ GROUP BY { groupItem [, groupItem ]* } ]
  [ HAVING booleanExpression ]

selectWithoutFrom:
  SELECT [ ALL | DISTINCT ]
  { * | projectItem [, projectItem ]* }

projectItem:
  expression [ [ AS ] columnAlias ]
  | tableAlias . *

tableExpression:
  tableReference [, tableReference ]*
  | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ]

joinCondition:
  ON booleanExpression
  | USING '(' column [, column ]* ')'

tableReference:
  tablePrimary
  [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]

tablePrimary:
  [ TABLE ] [ [ catalogName . ] schemaName . ] tableName
  | LATERAL TABLE '(' functionName '(' expression [, expression ]* ')' ')'
  | UNNEST '(' expression ')'

values:
  VALUES expression [, expression ]*

groupItem:
  expression
  | '(' ')'
  | '(' expression [, expression ]* ')'
  | CUBE '(' expression [, expression ]* ')'
  | ROLLUP '(' expression [, expression ]* ')'
  | GROUPING SETS '(' groupItem [, groupItem ]* ')'

Flink SQL uses a lexical policy for identifier (table, attribute, function names) similar to Java:

  • The case of identifiers is preserved whether or not they are quoted.
  • After which, identifiers are matched case-sensitively.
  • Unlike Java, back-ticks allow identifiers to contain non-alphanumeric characters (e.g. "SELECT a AS my field FROM t").

{% top %}

Operations

Scan, Projection, and Filter

Operation Description
Scan / Select / As
Batch Streaming 		{% highlight sql %} SELECT * FROM Orders

SELECT a, c AS d FROM Orders {% endhighlight %}

Where / Filter
Batch Streaming 		{% highlight sql %} SELECT * FROM Orders WHERE b = 'red'

SELECT * FROM Orders WHERE a % 2 = 0 {% endhighlight %}

User-defined Scalar Functions (Scalar UDF)
Batch Streaming

UDFs must be registered in the TableEnvironment. See the UDF documentation for details on how to specify and register scalar UDFs.

{% highlight sql %} SELECT PRETTY_PRINT(user) FROM Orders {% endhighlight %}

{% top %}

Aggregations

Operation Description
GroupBy Aggregation
Batch Streaming
Result Updating

Note: GroupBy on a streaming table produces an updating result. See the Streaming Concepts page for details.

{% highlight sql %} SELECT a, SUM(b) as d FROM Orders GROUP BY a {% endhighlight %}
GroupBy Window Aggregation
Batch Streaming

Use a group window to compute a single result row per group. See Group Windows section for more details.

{% highlight sql %} SELECT user, SUM(amount) FROM Orders GROUP BY TUMBLE(rowtime, INTERVAL '1' DAY), user {% endhighlight %}
Over Window aggregation
Streaming

Note: All aggregates must be defined over the same window, i.e., same partitioning, sorting, and range. Currently, only windows with PRECEDING (UNBOUNDED and bounded) to CURRENT ROW range are supported. Ranges with FOLLOWING are not supported yet. ORDER BY must be specified on a single time attribute

{% highlight sql %} SELECT COUNT(amount) OVER ( PARTITION BY user ORDER BY proctime ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM Orders {% endhighlight %}
Distinct
Batch Streaming
Result Updating 		{% highlight sql %} SELECT DISTINCT users FROM Orders {% endhighlight %}

Note: For streaming queries the required state to compute the query result might grow infinitely depending on the number of distinct fields. Please provide a query configuration with valid retention interval to prevent excessive state size. See Streaming Concepts for details.
Grouping sets, Rollup, Cube
Batch 		{% highlight sql %} SELECT SUM(amount) FROM Orders GROUP BY GROUPING SETS ((user), (product)) {% endhighlight %}
Having
Batch Streaming 		{% highlight sql %} SELECT SUM(amount) FROM Orders GROUP BY users HAVING SUM(amount) > 50 {% endhighlight %}
User-defined Aggregate Functions (UDAGG)
Batch Streaming

UDAGGs must be registered in the TableEnvironment. See the UDF documentation for details on how to specify and register UDAGGs.

{% highlight sql %} SELECT MyAggregate(amount) FROM Orders GROUP BY users {% endhighlight %}

{% top %}

Joins

Operation Description
Inner Equi-join / Outer Equi-join
Batch

Currently, only equi-joins are supported, i.e., joins that have at least one conjunctive condition with an equality predicate. Arbitrary cross or theta joins are not supported.

Note: The order of joins is not optimized. Tables are joined in the order in which they are specified in the FROM clause. Make sure to specify tables in an order that does not yield a cross join (Cartesian product) which are not supported and would cause a query to fail.

{% highlight sql %} SELECT * FROM Orders INNER JOIN Product ON Orders.productId = Product.id

SELECT * FROM Orders LEFT JOIN Product ON Orders.productId = Product.id {% endhighlight %}

Time-windowed Join
Batch Streaming

Note: Time-windowed joins are a subset of regular joins that can be processed in a streaming fashion.

    <p>A time-windowed join requires a special join condition that bounds the time on both sides. This can be done by either two appropriate range predicates (<code> &lt;, &lt;=, &gt;=, &gt;</code>) or a <code>BETWEEN</code> predicate that compares the <a href="streaming.html#time-attributes">time attributes</a> of both input tables. The following rules apply for time predicates:
      <ul>
        <li>Time predicates must compare time attributes of both input tables.</li>
        <li>Time predicates must compare only time attributes of the same type, i.e., processing time with processing time or event time with event time.</li>
        <li>Only range predicates are valid time predicates.</li>
        <li>Non-time predicates must not access a time attribute.</li>
      </ul>
    </p>

    <p><b>Note:</b> Currently, only processing time window joins and <code>INNER</code> joins are supported.</p>

{% highlight sql %} SELECT * FROM Orders o, Shipments s WHERE o.id = s.orderId AND o.ordertime BETWEEN s.shiptime - INTERVAL '4' HOUR AND s.shiptime {% endhighlight %}

The example above will join all orders with their corresponding shipments if the order was shipped four hours after the order was received.

Expanding arrays into a relation
Batch Streaming

Unnesting WITH ORDINALITY is not supported yet.

{% highlight sql %} SELECT users, tag FROM Orders CROSS JOIN UNNEST(tags) AS t (tag) {% endhighlight %}
User Defined Table Functions (UDTF)
Batch Streaming

UDTFs must be registered in the TableEnvironment. See the UDF documentation for details on how to specify and register UDTFs.

{% highlight sql %} SELECT users, tag FROM Orders LATERAL VIEW UNNEST_UDTF(tags) t AS tag {% endhighlight %}

{% top %}

Set Operations

<tr>
  <td>
    <strong>Intersect / Except</strong><br>
    <span class="label label-primary">Batch</span>
  </td>
  <td>

{% highlight sql %} SELECT * FROM ( (SELECT user FROM Orders WHERE a % 2 = 0) INTERSECT (SELECT user FROM Orders WHERE b = 0) ) {% endhighlight %} {% highlight sql %} SELECT * FROM ( (SELECT user FROM Orders WHERE a % 2 = 0) EXCEPT (SELECT user FROM Orders WHERE b = 0) ) {% endhighlight %} 

<tr>
  <td>
    <strong>In</strong><br>
    <span class="label label-primary">Batch</span>
  </td>
  <td>
  Returns true if an expression exists in a given table sub-query. The sub-query table must consist of one column. This column must have the same data type as the expression.

{% highlight sql %} SELECT user, amount FROM Orders WHERE product IN ( SELECT product FROM NewProducts ) {% endhighlight %}

Operation Description
Union
Batch 		{% highlight sql %} SELECT * FROM ( (SELECT user FROM Orders WHERE a % 2 = 0) UNION (SELECT user FROM Orders WHERE b = 0) ) {% endhighlight %}
UnionAll
Batch Streaming 		{% highlight sql %} SELECT * FROM ( (SELECT user FROM Orders WHERE a % 2 = 0) UNION ALL (SELECT user FROM Orders WHERE b = 0) ) {% endhighlight %}

{% top %}

OrderBy & Limit

<tr>
  <td><strong>Limit</strong><br>
    <span class="label label-primary">Batch</span>
  </td>
  <td>

{% highlight sql %} SELECT * FROM Orders LIMIT 3 {% endhighlight %}

Operation Description
Order By
Batch Streaming 		Note: The result of streaming queries must be primarily sorted on an ascending time attribute. Additional sorting attributes are supported.

{% highlight sql %} SELECT * FROM Orders ORDER BY orderTime {% endhighlight %}

{% top %}

Group Windows

Group windows are defined in the GROUP BY clause of a SQL query. Just like queries with regular GROUP BY clauses, queries with a GROUP BY clause that includes a group window function compute a single result row per group. The following group windows functions are supported for SQL on batch and streaming tables.

Group Window Function Description
TUMBLE(time_attr, interval) Defines a tumbling time window. A tumbling time window assigns rows to non-overlapping, continuous windows with a fixed duration (interval). For example, a tumbling window of 5 minutes groups rows in 5 minutes intervals. Tumbling windows can be defined on event-time (stream + batch) or processing-time (stream).
HOP(time_attr, interval, interval) Defines a hopping time window (called sliding window in the Table API). A hopping time window has a fixed duration (second interval parameter) and hops by a specified hop interval (first interval parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. Hopping windows can be defined on event-time (stream + batch) or processing-time (stream).
SESSION(time_attr, interval) Defines a session time window. Session time windows do not have a fixed duration but their bounds are defined by a time interval of inactivity, i.e., a session window is closed if no event appears for a defined gap period. For example a session window with a 30 minute gap starts when a row is observed after 30 minutes inactivity (otherwise the row would be added to an existing window) and is closed if no row is added within 30 minutes. Session windows can work on event-time (stream + batch) or processing-time (stream).

Time Attributes

For SQL queries on streaming tables, the time_attr argument of the group window function must refer to a valid time attribute that specifies the processing time or event time of rows. See the documentation of time attributes to learn how to define time attributes.

For SQL on batch tables, the time_attr argument of the group window function must be an attribute of type TIMESTAMP.

Selecting Group Window Start and End Timestamps

The start and end timestamps of group windows can be selected with the following auxiliary functions:

Auxiliary Function Description
TUMBLE_START(time_attr, interval)
HOP_START(time_attr, interval, interval)
SESSION_START(time_attr, interval)
 Returns the start timestamp of the corresponding tumbling, hopping, and session window.
TUMBLE_END(time_attr, interval)
HOP_END(time_attr, interval, interval)
SESSION_END(time_attr, interval)
 Returns the end timestamp of the corresponding tumbling, hopping, and session window.

Note: Auxiliary functions must be called with exactly same arguments as the group window function in the GROUP BY clause.

The following examples show how to specify SQL queries with group windows on streaming tables.

{% highlight java %} StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); StreamTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env);

// ingest a DataStream from an external source DataStream<Tuple3<Long, String, Integer>> ds = env.addSource(...); // register the DataStream as table "Orders" tableEnv.registerDataStream("Orders", ds, "user, product, amount, proctime.proctime, rowtime.rowtime");

// compute SUM(amount) per day (in event-time) Table result1 = tableEnv.sql( "SELECT user, " + " TUMBLE_START(rowtime, INTERVAL '1' DAY) as wStart, " + " SUM(amount) FROM Orders " + "GROUP BY TUMBLE(rowtime, INTERVAL '1' DAY), user");

// compute SUM(amount) per day (in processing-time) Table result2 = tableEnv.sql( "SELECT user, SUM(amount) FROM Orders GROUP BY TUMBLE(proctime, INTERVAL '1' DAY), user");

// compute every hour the SUM(amount) of the last 24 hours in event-time Table result3 = tableEnv.sql( "SELECT product, SUM(amount) FROM Orders GROUP BY HOP(rowtime, INTERVAL '1' HOUR, INTERVAL '1' DAY), product");

// compute SUM(amount) per session with 12 hour inactivity gap (in event-time) Table result4 = tableEnv.sql( "SELECT user, " + " SESSION_START(rowtime, INTERVAL '12' HOUR) AS sStart, " + " SESSION_END(rowtime, INTERVAL '12' HOUR) AS snd, " + " SUM(amount) " + "FROM Orders " + "GROUP BY SESSION(rowtime, INTERVAL '12' HOUR), user");

{% endhighlight %}

{% highlight scala %} val env = StreamExecutionEnvironment.getExecutionEnvironment val tableEnv = TableEnvironment.getTableEnvironment(env)

// read a DataStream from an external source val ds: DataStream[(Long, String, Int)] = env.addSource(...) // register the DataStream under the name "Orders" tableEnv.registerDataStream("Orders", ds, 'user, 'product, 'amount, 'proctime.proctime, 'rowtime.rowtime)

// compute SUM(amount) per day (in event-time) val result1 = tableEnv.sql( """ |SELECT | user, | TUMBLE_START(rowtime, INTERVAL '1' DAY) as wStart, | SUM(amount) | FROM Orders | GROUP BY TUMBLE(rowtime, INTERVAL '1' DAY), user """.stripMargin)

// compute SUM(amount) per day (in processing-time) val result2 = tableEnv.sql( "SELECT user, SUM(amount) FROM Orders GROUP BY TUMBLE(proctime, INTERVAL '1' DAY), user")

// compute every hour the SUM(amount) of the last 24 hours in event-time val result3 = tableEnv.sql( "SELECT product, SUM(amount) FROM Orders GROUP BY HOP(rowtime, INTERVAL '1' HOUR, INTERVAL '1' DAY), product")

// compute SUM(amount) per session with 12 hour inactivity gap (in event-time) val result4 = tableEnv.sql( """ |SELECT | user, | SESSION_START(rowtime, INTERVAL '12' HOUR) AS sStart, | SESSION_END(rowtime, INTERVAL '12' HOUR) AS sEnd, | SUM(amount) | FROM Orders | GROUP BY SESSION(rowtime(), INTERVAL '12' HOUR), user """.stripMargin)

{% endhighlight %}

{% top %}

Data Types

The SQL runtime is built on top of Flink's DataSet and DataStream APIs. Internally, it also uses Flink's TypeInformation to distinguish between types. The SQL support does not include all Flink types so far. All supported simple types are listed in org.apache.flink.table.api.Types. The following table summarizes the relation between SQL Types, Table API types, and the resulting Java class.

Table API SQL Java type
Types.STRING VARCHAR java.lang.String
Types.BOOLEAN BOOLEAN java.lang.Boolean
Types.BYTE TINYINT java.lang.Byte
Types.SHORT SMALLINT java.lang.Short
Types.INT INTEGER, INT java.lang.Integer
Types.LONG BIGINT java.lang.Long
Types.FLOAT REAL, FLOAT java.lang.Float
Types.DOUBLE DOUBLE java.lang.Double
Types.DECIMAL DECIMAL java.math.BigDecimal
Types.DATE DATE java.sql.Date
Types.TIME TIME java.sql.Time
Types.TIMESTAMP TIMESTAMP(3) java.sql.Timestamp
Types.INTERVAL_MONTHS INTERVAL YEAR TO MONTH java.lang.Integer
Types.INTERVAL_MILLIS INTERVAL DAY TO SECOND(3) java.lang.Long
Types.PRIMITIVE_ARRAY ARRAY e.g. int[]
Types.OBJECT_ARRAY ARRAY e.g. java.lang.Byte[]
Types.MAP MAP java.util.HashMap
Types.MULTISET MULTISET e.g. java.util.HashMap<String, Integer> for a multiset of String

Advanced types such as generic types, composite types (e.g. POJOs or Tuples), and array types (object or primitive arrays) can be fields of a row.

Generic types are treated as a black box within Table API and SQL yet.

Composite types, however, are fully supported types where fields of a composite type can be accessed using the .get() operator in Table API and dot operator (e.g. MyTable.pojoColumn.myField) in SQL. Composite types can also be flattened using .flatten() in Table API or MyTable.pojoColumn.* in SQL.

Array types can be accessed using the myArray.at(1) operator in Table API and myArray[1] operator in SQL. Array literals can be created using array(1, 2, 3) in Table API and ARRAY[1, 2, 3] in SQL.

{% top %}

Built-In Functions

Both the Table API and SQL come with a set of built-in functions for data transformations. This section gives a brief overview of the available functions so far.

The Flink SQL functions (including their syntax) are a subset of Apache Calcite's built-in functions. Most of the documentation has been adopted from the Calcite SQL reference.

<tr>
  <td>
    {% highlight text %}

value1 <> value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 > value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 >= value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 < value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 <= value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value IS NULL {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value IS NOT NULL {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 IS DISTINCT FROM value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 IS NOT DISTINCT FROM value2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 BETWEEN [ASYMMETRIC | SYMMETRIC] value2 AND value3 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value1 NOT BETWEEN value2 AND value3 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

string1 LIKE string2 [ ESCAPE string3 ] {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

string1 NOT LIKE string2 [ ESCAPE string3 ] {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

string1 SIMILAR TO string2 [ ESCAPE string3 ] {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

string1 NOT SIMILAR TO string2 [ ESCAPE string3 ] {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value IN (value [, value]* ) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value NOT IN (value [, value]* ) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

EXISTS (sub-query) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value IN (sub-query) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

value NOT IN (sub-query) {% endhighlight %}

Comparison functions Description
{% highlight text %} value1 = value2 {% endhighlight %}

Equals.

Not equal.

Greater than.

Greater than or equal.

Less than.

Less than or equal.

Returns TRUE if value is null.

Returns TRUE if value is not null.

Returns TRUE if two values are not equal, treating null values as the same.

Returns TRUE if two values are equal, treating null values as the same.

Returns TRUE if value1 is greater than or equal to value2 and less than or equal to value3.

Returns TRUE if value1 is less than value2 or greater than value3.

Returns TRUE if string1 matches pattern string2. An escape character can be defined if necessary.

Returns TRUE if string1 does not match pattern string2. An escape character can be defined if necessary.

Returns TRUE if string1 matches regular expression string2. An escape character can be defined if necessary.

Returns TRUE if string1 does not match regular expression string2. An escape character can be defined if necessary.

Returns TRUE if an expression exists in a given list of expressions. This is a shorthand for multiple OR conditions. If the testing set contains NULL, the result will be NULL if the element can not be found and TRUE if it can be found. If the element is NULL, the result is always NULL. E.g. "42 IN (1, 2, 3)" leads to FALSE.

Returns TRUE if value is not equal to every value in a list.

Returns TRUE if sub-query returns at least one row. Only supported if the operation can be rewritten in a join and group operation.

Returns TRUE if value is equal to a row returned by sub-query. This operation is not supported in a streaming environment yet.

Returns TRUE if value is not equal to every row returned by sub-query. This operation is not supported in a streaming environment yet.

 
<tr>
  <td>
    {% highlight text %}

boolean1 AND boolean2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

NOT boolean {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS FALSE {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS NOT FALSE {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS TRUE {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS NOT TRUE {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS UNKNOWN {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

boolean IS NOT UNKNOWN {% endhighlight %}

Logical functions Description
{% highlight text %} boolean1 OR boolean2 {% endhighlight %}

Returns TRUE if boolean1 is TRUE or boolean2 is TRUE. Supports three-valued logic.

Returns TRUE if boolean1 and boolean2 are both TRUE. Supports three-valued logic.

Returns TRUE if boolean is not TRUE; returns UNKNOWN if boolean is UNKNOWN.

Returns TRUE if boolean is FALSE; returns FALSE if boolean is UNKNOWN.

Returns TRUE if boolean is not FALSE; returns TRUE if boolean is UNKNOWN.

Returns TRUE if boolean is TRUE; returns FALSE if boolean is UNKNOWN.

Returns TRUE if boolean is not TRUE; returns TRUE if boolean is UNKNOWN.

Returns TRUE if boolean is UNKNOWN.

Returns TRUE if boolean is not UNKNOWN.

 
<tr>
  <td>
    {% highlight text %}

  • numeric {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

numeric1 - numeric2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

numeric1 * numeric2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

numeric1 / numeric2 {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

POWER(numeric1, numeric2) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

ABS(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

MOD(numeric1, numeric2) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

SQRT(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

LN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

LOG10(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

EXP(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

CEIL(numeric) CEILING(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

FLOOR(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

SIN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

COS(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

TAN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

COT(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

ASIN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

ACOS(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

ATAN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

DEGREES(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

RADIANS(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

SIGN(numeric) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

ROUND(numeric, int) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

PI() {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

RAND() {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

RAND(seed integer) {% endhighlight %} 

<tr>
 <td>
   {% highlight text %}

RAND_INTEGER(bound integer) {% endhighlight %} 

<tr>
 <td>
   {% highlight text %}

RAND_INTEGER(seed integer, bound integer) {% endhighlight %} 

<tr>
 <td>
   {% highlight text %}

LOG(x numeric), LOG(base numeric, x numeric) {% endhighlight %}

Arithmetic functions Description
{% highlight text %} + numeric {% endhighlight %}

Returns numeric.

Returns negative numeric.
{% highlight text %}

numeric1 + numeric2 {% endhighlight %}

Returns numeric1 plus numeric2.

Returns numeric1 minus numeric2.

Returns numeric1 multiplied by numeric2.

Returns numeric1 divided by numeric2.

Returns numeric1 raised to the power of numeric2.

Returns the absolute value of numeric.

Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.

Returns the square root of numeric.

Returns the natural logarithm (base e) of numeric.

Returns the base 10 logarithm of numeric.

Returns e raised to the power of numeric.

Rounds numeric up, and returns the smallest number that is greater than or equal to numeric.

Rounds numeric down, and returns the largest number that is less than or equal to numeric.

Calculates the sine of a given number.

Calculates the cosine of a given number.

Calculates the tangent of a given number.

Calculates the cotangent of a given number.

Calculates the arc sine of a given number.

Calculates the arc cosine of a given number.

Calculates the arc tangent of a given number.

Converts numeric from radians to degrees.

Converts numeric from degrees to radians.

Calculates the signum of a given number.

Rounds the given number to integer places right to the decimal point.

Returns a value that is closer than any other value to pi.
{% highlight text %} E() {% endhighlight %}

Returns a value that is closer than any other value to e.

Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).

Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with a initial seed. Two RAND functions will return identical sequences of numbers if they have same initial seed.

Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive).

Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive) with a initial seed. Two RAND_INTEGER functions will return identical sequences of numbers if they have same initial seed and same bound.

Returns the natural logarithm of a specified number of a specified base. If called with one parameter, this function returns the natural logarithm of x. If called with two parameters, this function returns the logarithm of x to the base b. x must be greater than 0. b must be greater than 1.

 
<tr>
  <td>
    {% highlight text %}

CHAR_LENGTH(string) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

CHARACTER_LENGTH(string) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

UPPER(string) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

LOWER(string) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

POSITION(string1 IN string2) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ]) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

SUBSTRING(string FROM integer) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

SUBSTRING(string FROM integer FOR integer) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

INITCAP(string) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

CONCAT(string1, string2,...) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

CONCAT_WS(separator, string1, string2,...) {% endhighlight %}

String functions Description
{% highlight text %} string || string {% endhighlight %}

Concatenates two character strings.

Returns the number of characters in a character string.

As CHAR_LENGTH(string).

Returns a character string converted to upper case.

Returns a character string converted to lower case.

Returns the position of the first occurrence of string1 in string2.

Removes leading and/or trailing characters from string2. By default, whitespaces at both sides are removed.

Replaces a substring of string1 with string2.

Returns a substring of a character string starting at a given point.

Returns a substring of a character string starting at a given point with a given length.

Returns string with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. E.g. CONCAT("AA", "BB", "CC") returns AABBCC.

Returns the string that results from concatenating the arguments using a separator. The separator is added between the strings to be concatenated. Returns NULL If the separator is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL argument. E.g. CONCAT_WS("", "AA", "BB", "", "CC") returns AABB~~CC

 
<tr>
  <td>
    {% highlight text %}

CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

NULLIF(value, value) {% endhighlight %} 

<tr>
  <td>
    {% highlight text %}

COALESCE(value, value [, value ]* ) {% endhighlight %}

Conditional functions Description
{% highlight text %} CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] END {% endhighlight %}

Simple case.

Searched case.

Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.

Provides a value if the first value is null. For example, COALESCE(NULL, 5) returns 5.
Type conversion functions Description
{% highlight text %} CAST(value AS type) {% endhighlight %}

Converts a value to a given type.
Value constructor functions Description