# Streaming SQL Language Elements
<a name="sql-reference-basic-building-blocks"></a>

The following topics discuss the language elements in Kinesis Data Analytics that underlie its syntax and operations:

**Topics**
+ [Identifiers](sql-reference-identifiers.md)
+ [Data Types](sql-reference-data-types.md)
+ [Streaming SQL Operators](sql-reference-streaming-operators.md)
+ [Expressions and Literals](sql-reference-expressions.md)
+ [Monotonic Expressions and Operators](sql-reference-monotonic-expressions-operators.md)
+ [Condition Clause](sql-reference-conditions.md)
+ [Temporal Predicates](sql-reference-temporal-predicate.md)
+ [Reserved Words and Keywords](sql-reference-reserved-words-keywords.md)

# Identifiers
<a name="sql-reference-identifiers"></a>

All identifiers may be up to 128 characters. Identifiers may be quoted (with case-sensitivity) by enclosing them in double-quote marks ("), or unquoted (with implicit uppercasing before both storage and lookup).

Unquoted identifiers must start with a letter or underscore, and be followed by letters, digits or underscores; letters are all converted to upper case.

Quoted identifiers can contain other punctuation too (in fact, any Unicode character except control characters: codes 0x0000 through 0x001F). You can include a double-quote in an identifier by escaping it with another double-quote.

In the following example, a stream is created with an unquoted identifier, which is converted to upper case before the stream definition is stored in the catalog. It can be referenced using its upper-case name, or by an unquoted identifier which is implicitly converted to upper case.

```
–- Create a stream. Stream name specified without quotes, 
–- which defaults to uppercase.
CREATE OR REPLACE STREAM ExampleStream (col1 VARCHAR(4));

– example 1: OK, stream name interpreted as uppercase.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO ExampleStream 
    SELECT * FROM SOURCE_SQL_STREAM_001;

– example 2: OK, stream name interpreted as uppercase.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO examplestream 
    SELECT * FROM   customerdata;

– example 3: Ok. 
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO EXAMPLESTREAM 
    SELECT * FROM   customerdata;

– example 2: Not found. Quoted names are case-sensitive.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "examplestream"
    SELECT * FROM   customerdata;
```

When objects are created in Amazon Kinesis Data Analytics, their names are implicitly quoted, so it is easy to create identifiers that contain lowercase characters, spaces, dashes, or other punctuation. If you reference those objects in SQL statements, you will need to quote their names.
<a name="TOC3"></a>
**Reserved Words and Keywords**  
Certain identifiers, called keywords, have special meaning if they occur in a particular place in a streaming SQL statement. A subset of these key words are called reserved words and may not be used as the name of an object, unless they are quoted. For more information, see [Reserved Words and Keywords](sql-reference-reserved-words-keywords.md).

# Data Types
<a name="sql-reference-data-types"></a>

The following table summarizes the data types supported by Amazon Kinesis Data Analytics. 


| SQL Data Type | JSON Data Type | Description | Notes | 
| --- | --- | --- | --- | 
|  BIGINT  | number |  64-bit signed integer  | 
|  BINARY  | BASE64-encoded string |  Binary (non character) data  |  Substring works on BINARY. Concatenation does not work on BINARY.  | 
|  BOOLEAN  | boolean |  TRUE, FALSE, or NULL  |  Evaluates to TRUE, FALSE, and UNKNOWN.  | 
|  CHAR (n)  | string |  A character string of fixed length n. Also specifiable as CHARACTER  |  n must be greater than 0 and less than 65535.   | 
|  DATE  | string |  A date is a calendar day (year/month/day).  |  Precision is day. Range runs from the largest value, approximately \$1229 (in years) to the smallest value, -229.  | 
|  DECIMAL DEC NUMERIC  | number |  A fixed point, with up to 19 significant digits.  |  Can be specified with DECIMAL, DEC, or NUMERIC.  | 
|  DOUBLE DOUBLE PRECISION  | number |  A 64-bit floating point number  |  64-bit approx value; -1.79E\$1308 to 1.79E\$1308. Follows the ISO DOUBLE PRECISION data type, 53 bits are used for the number's mantissa in scientific notation, representing 15 digits of precision and 8 bytes of storage.  | 
|  INTEGER INT  | number |   |  32-bit signed integer. Range is -2147483648 to 2147483647 [ 2\$1\$1(31) to 2\$1\$1(31)- 1]  | 
|  INTERVAL <timeunit> [TO <timeunit>]  | string |  Day-time intervals supported, year-month intervals not supported  |  Allowed in an expression in date arithmetic, but cannot be used as a datatype for a column in a table or stream.   | 
|  <timeUnit>  | string |  The units of a INTERVAL value  |  Supported units are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND  | 
|  SMALLINT  | number |  16-bit signed integer  |  Range is -32768 to 32767  [2\$1\$1(15) to 2\$1\$1(15)-1]  | 
|  REAL  | number |  A 32-bit floating point number  |  Following the ISO REAL data type, 24 bits are used for the number's mantissa in scientific notation, representing 7 digits of precision and 4 bytes of storage. The minimum value is -3.40E\$138; the maximum value is 3.40E\$138.  | 
|  TIME  | string |  A TIME is a time in a day (hour:minute:second).  |  Its precision is milliseconds; its range is 00:00:00.000 to 23:59:59.999. Since the system clock runs in UTC, the timezone used for values stored in a TIME or TIMESTAMP column is not considered. for values stored in a TIME or TIMESTAMP column.  | 
|  TIMESTAMP  | string |  A TIMESTAMP is a combined DATE and TIME.  |  A TIMESTAMP value always has a precision of 1 millisecond. It has no particular timezone. Since the system clock runs in UTC, the timezone used for values stored in a TIME or TIMESTAMP column is not considered. Its range runs from the largest value, approximately \$1229 (in years) to the smallest value, -229. Each timestamp is stored as a signed 64-bit integer, with 0 representing the Unix epoch (Jan 1, 1970 00:00am). This means that the largest TIMESTAMP value represents approximately 300 million years after 1970, and the smallest value represents approximately 300 million years before 1970. Following the SQL standard, a TIMESTAMP value has an undefined timezone.  | 
|  TINYINT  | number |  8-bit signed integer  |  Range is -128 to 127,  | 
|  VARBINARY (n)  | BASE64-encoded string |  Also specifiable as BINARY VARYING  |  n must be greater than 0 and less than 65535.   | 
|  VARCHAR (n)  | string |  Also specifiable as CHARACTER VARYING  |  n must be greater than 0 and less than 65535.   | 
<a name="NOTE1"></a>
**Notes**  
Regarding characters:
+ Amazon Kinesis Data Analytics supports only Java single-byte CHARACTER SETs.
+ Implicit type conversion is not supported. That is, characters are mutually assignable if and only if they are taken from the same character repertoire and are values of the data types CHARACTER or CHARACTER VARYING.

Regarding numbers:
+ Numbers are mutually comparable and mutually assignable if they are values of the data types NUMERIC, DECIMAL, INTEGER, BIGINT, SMALLINT, TINYINT, REAL, and DOUBLE PRECISION.

The following sets of data types are synonyms:
+ DEC and DECIMAL
+ DOUBLE PRECISION and DOUBLE
+ CHARACTER and CHAR
+ CHAR VARYING or CHARACTER VARYING and VARCHAR
+ BINARY VARYING and VARBINARY
+ INT and INTEGER
+ Binary values (data types BINARY and BINARY VARYING) are always mutually comparable and are mutually assignable.

Regarding dates, times, and timestamps:
+ Implicit type conversion is not supported (that is, datetime values are mutually assignable only if the source and target of the assignment are both of type DATE, or both of type TIME, or both of type TIMESTAMP).
+ The Amazon Kinesis Data Analytics timezone is always UTC. The time functions, including the Amazon Kinesis Data Analytics extension CURRENT\$1ROW\$1TIMESTAMP, return time in UTC.

# Numeric Types and Precision
<a name="sql-reference-numeric-types-precision"></a>

For DECIMAL we support a maximum of 18 digits for precision and scale.

Precision specifies the maximum number of decimal digits that can be stored in the column, both to the right and to the left of the decimal point. You can specify precisions ranging from 1 digit to 18 digits or use the default precision of 18 digits.

Scale specifies the maximum number of digits that can be stored to the right of the decimal point. Scale must be less than or equal to the precision. You can specify a scale ranging from 0 digits to 18 digits, or use the default scale of 0 digits.

**Rule for Divide**  
Let p1, s1 be the precision and scale of the first operand, such as DECIMAL (10,1). 

Let p2, s2 be the precision and scale of the second operand, such as DECIMAL (10,3).

Let p, s be the precision and scale of the result. 

Let d be the number of whole digits in the result. Then, the result type is a decimal as shown following:


|  |  | 
| --- |--- |
|  d = p1 - s1 \$1 s2  |  D = 10 - 1 \$1 3 Number of whole digits in result = 6  | 
|  s <= MAX (6, s1 \$1 p2 \$11)  |  S <= MAX (6, 1 \$1 10 \$1 1) Scale of result = 14  | 
|  p = d \$1 s  |  Precision of result = 18  | 

Precision and scale are capped at their maximum values (18, where scale cannot be larger than precision).

Precedence is first giving at least the scale of the first argument (s >= s1) followed by enough whole digits to represent the result without overflow

**Rule for Multiply**  
Let p1, s1 be the precision and scale of the first operand DECIMAL (10,1).

Let p2, s2 be the precision and scale of the second operand DECIMAL (10,3).

Let p, s be the precision and scale of the result. 

Then, the result type is a decimal as shown following:


|  |  | 
| --- |--- |
|  p = p1 \$1 p2  |  p = 10 \$1 10 Precision of result = 18  | 
|  s = s1 \$1 s2  |  s = 1 \$1 3 Scale of result = 4  | 

**Rule for Sum or Subtraction**  
Type-inference strategy whereby the result type of a call is the decimal sum of two exact numeric operands where at least one of the operands is a decimal.

Let p1, s1 be the precision and scale of the first operand DECIMAL (10,1).

Let p2, s2 be the precision and scale of the second operand DECIMAL (10,3).

Let p, s be the precision and scale of the result, as shown following:


|  |  | 
| --- |--- |
|  s = max(s1, s2)  |  s = max (1,3) Scale of result = 3  | 
|  p = max(p1 - s1, p2 - s2) \$1 s \$1 1  |  p = max(10-1,10-3) \$1 3 \$1 1 Precision of result = 11  | 

s and p are capped at their maximum values

# Streaming SQL Operators
<a name="sql-reference-streaming-operators"></a>
<a name="TOC1"></a>
**Subquery Operators**  
Operators are used in queries and subqueries to combine or test data for various properties, attributes, or relationships.

The available operators are described in the topics that follow, grouped into the following categories:
+ [Scalar Operators](#scalaroprs)
  + [Operator Types](#OPRTYPES)
  + [Precedence](#PRECEDENCE)
+ [Arithmetic Operators](#ARITHMOPRS)
+ [String Operators](sql-reference-string-operators.md)
  +  (Concatenation)
  + LIKE patterns
  + SIMILAR TO patterns
+ [Date, Timestamp, and Interval Operators](sql-reference-date-timestamp-interval.md)
+ [Logical Operators](sql-reference-logical-operators.md)
  + 3-state boolean logic
  + Examples

## IN Operator
<a name="INOPERATOR"></a>

As an operator in a condition test, IN tests a scalar or row value for membership in a list of values, a relational expression, or a subquery.

```
Examples:
1. --- IF column IN ('A','B','C')
2. --- IF (col1, col2) IN (
    select a, b from my_table
    )
```

Returns TRUE if the value being tested is found in the list, in the result of evaluating the relational expression, or in the rows returned by the subquery; returns FALSE otherwise.

**Note**  
IN has a different meaning and use in [CREATE FUNCTION](sql-reference-create-function.md). 

## EXISTS Operator
<a name="EXISTSOPERATOR"></a>

Tests whether a relational expression returns any rows; returns TRUE if any row is returned, FALSE otherwise.

## Scalar Operators
<a name="scalaroprs"></a>
<a name="OPRTYPES"></a>
**Operator Types**  
The two general classes of scalar operators are:
+ unary: A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

  ```
  operator operand
  ```
+ binary: A binary operator operates on two operands. A binary operator appears with its operands in this format:

  ```
  operand1 operator operand2
  ```

A few operators that use a different format are noted specifically in the operand descriptions below.

If an operator is given a null operand, the result is almost always null (see the topic on logical operators for exceptions).
<a name="PRECEDENCE"></a>
**Precedence**  
Streaming SQL follows the usual precedence of operators:

1. Evaluate bracketed sub-expressions.

1. Evaluate unary operators (e.g., \$1 or -, logical NOT).

1. Evaluate multiplication and divide (\$1 and /).

1. Evaluate addition and subtraction (\$1 and -) and logical combination (AND and OR).

If one of the operands is NULL, the result is also NULL If the operands are of different but comparable types, the result will be of the type with the greatest precision. If the operands are of the same type, the result will be of the same type as the operands. For instance 5/2 = 2, not 2.5, as 5 and 2 are both integers. 

## Arithmetic Operators
<a name="ARITHMOPRS"></a>


| Operator | Unary/Binary | Description | 
| --- | --- | --- | 
|  \$1  |  U  |  Identity  | 
|  -  |  U  |  Negation  | 
|  \$1  |  B  |  Addition  | 
|  -  |  B  |  Subtraction  | 
|  \$1  |  B  |  Multiplication  | 
|  /  |  B  |  Division  | 



Each of these operators works according to normal arithmetic behavior, with the following caveats:

1. If one of the operands is NULL, the result is also NULL

1. If the operands are of different but comparable types, the result will be of the type with the greatest precision.

1. If the operands are of the same type, the result will be of the same type as the operands. For instance 5/2 = 2, not 2.5, as 5 and 2 are both integers.

### Examples
<a name="operators_examples"></a>


| Operation | Result | 
| --- | --- | 
|  1 \$1 1  |  2  | 
|  2.0 \$1 2.0  |  4.0  | 
|  3.0 \$1 2  |  5.0  | 
|  5 / 2  |  2  | 
|  5.0 / 2  |  2.500000000000  | 
|  5\$12\$12  |  12  | 

# String Operators
<a name="sql-reference-string-operators"></a>

You can use string operators for streaming SQL, including concatenation and string pattern comparison, to combine and compare strings.


| Operator | Unary/Binary | Description | Notes | 
| --- | --- | --- | --- | 
|  \$1\$1  |  B  |  Concatenation  |  Also applies to binary types  | 
|  LIKE  |  B  |  String pattern comparison  |  <string> LIKE <like pattern> [ESCAPE <escape character>]  | 
|  SIMILAR TO  |  B  |  String pattern comparison  |  <string> SIMILAR TO <similar to pattern> [ESCAPE <escape character>]  | 

## Concatenation
<a name="CONCAT"></a>

This operator is used to concatenate one or more strings as shown in the following table.


| Operation | Result | 
| --- | --- | 
|  'SQL'\$1\$1'stream'  |  SQLstream  | 
|  'SQL'\$1\$1''\$1\$1'stream'  |  SQLstream  | 
|  'SQL'\$1\$1'stream'\$1\$1' Incorporated'  |  SQLstream Incorporated  | 
|  <col1>\$1\$1<col2>\$1\$1<col3>\$1\$1<col4>  |  <col1><col2><col3><col4>  | 

## LIKE patterns
<a name="w2aac10c19c21b9"></a>

LIKE compares a string to a string pattern. In the pattern, the characters \$1 (underscore) and % (percent) have special meaning.


| Character in pattern | Effect | 
| --- | --- | 
|  \$1  |  Matches any single character  | 
|  %  |  Matches any substring, including the empty string  | 
|  <any other character>  |  Matches only the exact same character  | 

If either operand is NULL, the result of the LIKE operation is UNKNOWN.

To explicitly match a special character in the character string, you must specify an escape character using the ESCAPE clause. The escape character must then precede the special character in the pattern. The following table lists examples.


| Operation | Result | 
| --- | --- | 
|  'a' LIKE 'a'  |  TRUE  | 
|  'a' LIKE 'A'  |  FALSE  | 
|  'a' LIKE 'b'  |  FALSE  | 
|  'ab' LIKE 'a\$1'  |  TRUE  | 
|  'ab' LIKE 'a%'  |  TRUE  | 
|  'ab' LIKE 'a\$1\$1' ESCAPE '\$1'  |  FALSE  | 
|  'ab' LIKE 'a\$1%' ESCAPE '\$1'  |  FALSE  | 
|  'a\$1' LIKE 'a\$1\$1' ESCAPE '\$1'  |  TRUE  | 
|  'a%' LIKE 'a\$1%' ESCAPE '\$1'  |  TRUE  | 
|  'a' LIKE 'a\$1'  |  FALSE  | 
|  'a' LIKE 'a%'  |  TRUE  | 
|  'abcd' LIKE 'a\$1'  |  FALSE  | 
|  'abcd' LIKE 'a%'  |  TRUE  | 
|  '' LIKE ''  |  TRUE  | 
|  '1a' LIKE '\$1a'  |  TRUE  | 
|  '123aXYZ' LIKE '%a%'  |  TRUE  | 
|  '123aXYZ' LIKE '\$1%\$1a%\$1'  |  TRUE  | 

## SIMILAR TO patterns
<a name="SIMILARPATTERNS"></a>

SIMILAR TO compares a string to a pattern. It is much like the LIKE operator, but more powerful, as the patterns are regular expressions.

In the following SIMILAR TO table, *seq* means any sequence of characters  explicitly specified, such as '13aq'. Non-alphanumeric characters intended for matching must be preceded by an escape character explicitly declared in the SIMILAR TO statement, such as '13aq\$1\$1' SIMILAR TO '13aq\$1\$124br\$1\$1% ESCAPE '\$1'  (This statement is TRUE).

When a range is indicated, as when a dash is used in a pattern, the current collating sequence is used. Typical ranges are 0-9 and a-z. [PostgreSQL](https://www.postgresql.org/docs/7.3/static/functions-matching.html) provides a typical discussion of pattern-matching, including ranges.

When a line requires multiple comparisons, the innermost pattern that can be matched will be matched first, then the "next-innermost," etc.

Expressions and matching operations that are enclosed within parentheses are evaluated before surrounding operations are applied, again by innermost-first precedence.


| Delimiter | Character in pattern | Effect | Rule ID | 
| --- | --- | --- | --- | 
|  parentheses (  )  |    (  seq  )  |   Groups the *seq* (used for defining precedence of pattern expressions)  |  1  | 
|   brackets [  ]  |   [  seq  ]  |  Matches any single character in the seq  |  2  | 
|   caret or circumflex  |   [^seq]  |   Matches any single character not in the seq  |  3  | 
|   |    [ seq ^ seq]  |  Matches any single character in seq and not in seq  |  4  | 
|  dash  |   <character1>-<character2>  |  Specifies a range of characters between character1 and character2 (using some known sequence like 1-9 or a-z)  |  5  | 
|   bar  |    [ seq  seq]  |  Matches either seq or seq  |  6  | 
|   asterisk  |    seq\$1  |  Matches zero or more repetitions of seq  |  7  | 
|   plus  |   seq\$1  |  Matches one or more repetitions of seq  |  8  | 
|   braces  |    seq\$1<number>\$1  |  Matches exactly number repetitions of seq   |  9  | 
|    |    seq\$1<low number>,<high number>\$1  |  Matches low number or more repetitions of seq, to a maximum of high number  |  10  | 
|   question-mark  |    seq?  |  Matches zero or one instances of seq  |  11  | 
|   underscore  |   \$1  |   Matches any single character  |  12  | 
|   percent  |   %  |   Matches any substring, including the empty string  |  13  | 
|   character  |   <any other character>  |   Matches only the exact same character  |  14  | 
|    NULL  |    NULL  |    If either operand is NULL, the result of the SIMILAR TO operation is UNKNOWN.  |  15  | 
|    Non-alphanumeric  |   Special characters  |   To explicitly match a special character in the character string, that special character must be preceded by an escape character defined using an ESCAPE clause specified at the end of the pattern.  |  16  | 

The following table lists examples.


| Operation | Result | Rule | 
| --- | --- | --- | 
|  'a' SIMILAR TO 'a'  |  TRUE  |  14  | 
|  'a' SIMILAR TO 'A'  |  FALSE  |  14  | 
|  'a' SIMILAR TO 'b'  |  FALSE  |  14  | 
|  'ab' SIMILAR TO 'a\$1'  |  TRUE  |  12  | 
|  'ab' SIMILAR TO 'a%'  |  TRUE  |  13  | 
|  'a' SIMILAR TO 'a\$1'  |  FALSE  |  12 & 14  | 
|  'a' SIMILAR TO 'a%'  |  TRUE  |  13  | 
|  'abcd' SIMILAR TO 'a\$1'  |  FALSE  |  12  | 
|  'abcd' SIMILAR TO 'a%'  |  TRUE  |  13  | 
|  '' SIMILAR TO ''  |  TRUE  |  14  | 
|  '1a' SIMILAR TO '\$1a'  |  TRUE  |  12  | 
|  '123aXYZ' SIMILAR TO ''  |  TRUE  |  14  | 
|  '123aXYZ' SIMILAR TO '\$1%\$1a%\$1'  |  TRUE  |  13 & 12  | 
|  'xy' SIMILAR TO '(xy)'  |  TRUE  |  1  | 
|  'abd' SIMILAR TO '[ab][bcde]d'  |  TRUE  |  2  | 
|  'bdd' SIMILAR TO '[ab][bcde]d'  |  TRUE  |  2  | 
|  'abd' SIMILAR TO '[ab]d'  |  FALSE  |  2  | 
|  'cd' SIMILAR TO '[a-e]d'  |  TRUE  |  2  | 
|  'cd' SIMILAR TO '[a-e^c]d'  |  FALSE  |  4  | 
|  'cd' SIMILAR TO '[^(a-e)]d'  |  INVALID  | 
|  'yd' SIMILAR TO '[^(a-e)]d'  |  INVALID  | 
|  'amy' SIMILAR TO 'amyfred'  |  TRUE  |  6  | 
|  'fred' SIMILAR TO 'amyfred'  |  TRUE  |  6  | 
|  'mike' SIMILAR TO 'amyfred'  |  FALSE  |  6  | 
|  'acd' SIMILAR TO 'ab\$1c\$1d'  |  TRUE  |  7 & 8  | 
|  'accccd' SIMILAR TO 'ab\$1c\$1d'  |  TRUE  |  7 & 8  | 
|  'abd' SIMILAR TO 'ab\$1c\$1d'  |  FALSE  |  7 & 8  | 
|  'aabc' SIMILAR TO 'ab\$1c\$1d'  |  FALSE  |   | 
|  'abb' SIMILAR TO 'a(b\$13\$1)'  |  FALSE  |  9  | 
|  'abbb' SIMILAR TO 'a(b\$13\$1)'  |  TRUE  |  9  | 
|  'abbbbb' SIMILAR TO 'a(b\$13\$1)'  |  FALSE  |  9  | 
|  'abbbbb' SIMILAR TO 'ab\$13,6\$1'  |  TRUE  |  10  | 
|  'abbbbbbbb' SIMILAR TO 'ab\$13,6\$1'  |  FALSE  |  10  | 
|  '' SIMILAR TO 'ab?'  |  FALSE  |  11  | 
|  '' SIMILAR TO '(ab)?'  |  TRUE  |  11  | 
|  'a' SIMILAR TO 'ab?'  |  TRUE  |  11  | 
|  'a' SIMILAR TO '(ab)?'  |  FALSE  |  11  | 
|  'a' SIMILAR TO 'a(b?)'  |  TRUE  |  11  | 
|  'ab' SIMILAR TO 'ab?'  |  TRUE  |  11  | 
|  'ab' SIMILAR TO 'a(b?)'  |  TRUE  |  11  | 
|  'abb' SIMILAR TO 'ab?'  |  FALSE  |  11  | 
|  'ab' SIMILAR TO 'a\$1\$1' ESCAPE '\$1'  |  FALSE  |  16  | 
|  'ab' SIMILAR TO 'a\$1%' ESCAPE '\$1'  |  FALSE  |  16  | 
|  'a\$1' SIMILAR TO 'a\$1\$1' ESCAPE '\$1'  |  TRUE  |  16  | 
|  'a%' SIMILAR TO 'a\$1%' ESCAPE '\$1'  |  TRUE  |  16  | 
|  'a(b\$13\$1)' SIMILAR TO 'a(b\$13\$1)'  |  FALSE  |  16  | 
|  'a(b\$13\$1)' SIMILAR TO 'a\$1(b\$1\$13\$1\$1\$1)' ESCAPE '\$1'  |  TRUE  |  16  | 

# Logical Operators
<a name="sql-reference-logical-operators"></a>

Logical operators let you establish conditions and test their results.


| Operator | Unary/Binary | Description | Operands | 
| --- | --- | --- | --- | 
|  NOT  |  U  |  Logical negation  |  Boolean  | 
|  AND  |  B  |  Conjunction  |  Boolean  | 
|  OR  |  B  |  Disjunction  |  Boolean  | 
|  IS  |  B  |  Logical assertion  |  Boolean  | 
|  IS NOT UNKNOWN  |  U  |  Negated unknown comparison: <expr> IS NOT UNKNOWN  |  Boolean  | 
|  IS NULL  |  U  |  Null comparison: <expr> IS NULL  |  Any  | 
|  IS NOT NULL  |  U  |  Negated null comparison: <expr> IS NOT NULL  |  Any  | 
|  =  |  B  |  Equality  |  Any  | 
|  \$1=  |  B  |  Inequality  |  Any  | 
|  <>  |  B  |  Inequality  |  Any  | 
|  >  |  B  |  Greater than  |  Ordered types (Numeric, String, Date, Time)  | 
|  >=  |  B  |  Greater than or equal to (not less than)  |  Ordered types  | 
|  <  |  B  |  Less than  |  Ordered types  | 
|  <=  |  B  |  Less than or equal to (not more than)  |  Ordered types  | 
|  BETWEEN  |  Ternary  |  Range comparison: col1 BETWEEN expr1 AND expr2  |  Ordered types  | 
|  IS DISTINCT FROM  |  B  |  Distinction  |  Any  | 
|  IS NOT DISTINCT FROM  |  B  |  Negated distinction  |  Any  | 

## Three State Boolean Logic
<a name="three_state_boolean_logic"></a>

SQL boolean values have three possible states rather than the usual two: TRUE, FALSE, and UNKNOWN, the last of which is equivalent to a boolean NULL. TRUE and FALSE operands generally function according to normal two-state boolean logic, but additional rules apply when pairing them with UNKNOWN operands, as the tables that follow will show.

**Note**  
UNKOWN represents "maybe TRUE, maybe FALSE" or, to put it another way, "not definitely TRUE and not definitely FALSE." This understanding may help you clarify why some of the expressions in the tables evaluate as they do.


**Negation (NOT)**  

| Operation | Result | 
| --- | --- | 
|  NOT TRUE  |  FALSE  | 
|  NOT FALSE  |  TRUE  | 
|  NOT UNKNOWN  |  UNKNOWN  | 


**Conjunction (AND)**  

| Operation | Result | 
| --- | --- | 
|  TRUE AND TRUE  |  TRUE  | 
|  TRUE AND FALSE  |  FALSE  | 
|  TRUE AND UNKNOWN  |  UNKNOWN  | 
|  FALSE AND TRUE  |  FALSE  | 
|  FALSE AND FALSE  |  FALSE  | 
|  FALSE AND UNKNOWN  |  FALSE  | 
|  UNKNOWN AND TRUE  |  UNKNOWN  | 
|  UNKNOWN AND FALSE  |  FALSE  | 
|  UNKNOWN AND UNKNOWN  |  UNKNOWN  | 


**Disjunction (OR)**  

| Operation | Result | 
| --- | --- | 
|  TRUE OR TRUE  |  TRUE  | 
|  TRUE OR FALSE  |  TRUE  | 
|  TRUE OR UNKNOWN  |  TRUE  | 
|  FALSE OR TRUE  |  TRUE  | 
|  FALSE OR FALSE  |  FALSE  | 
|  FALSE OR UNKNOWN  |  UNKNOWN  | 
|  UNKNOWN OR TRUE  |  TRUE  | 
|  UNKNOWN OR FALSE  |  UNKNOWN  | 
|  UNKNOWN OR UNKNOWN  |  UNKNOWN  | 


**Assertion (IS)**  

| Operation | Result | 
| --- | --- | 
|  TRUE IS TRUE  |  TRUE  | 
|  TRUE IS FALSE  |  FALSE  | 
|  TRUE IS UNKNOWN  |  FALSE  | 
|  FALSE IS TRUE  |  FALSE  | 
|  FALSE IS FALSE  |  TRUE  | 
|  FALSE IS UNKNOWN  |  FALSE  | 
|  UNKNOWN IS TRUE  |  FALSE  | 
|  UNKNOWN IS FALSE  |  FALSE  | 
|  UNKNOWN IS UNKNOWN  |  TRUE  | 


**IS NOT UNKNOWN**  

| Operation | Result | 
| --- | --- | 
|  TRUE IS NOT UNKNOWN  |  TRUE  | 
|  FALSE IS NOT UNKNOWN  |  TRUE  | 
|  UNKNOWN IS NOT UNKNOWN  |  FALSE  | 

IS NOT UNKNOWN is a special operator in and of itself. The expression "x IS NOT UNKNOWN" is equivalent to "(x IS TRUE) OR (x IS FALSE)", not "x IS (NOT UNKNOWN)". Thus, substituting in the table above:


| x | Operation | Result |   | Result of substituting for x in "(x IS TRUE) OR (x IS FALSE)" | 
| --- | --- | --- | --- | --- | 
|  TRUE  |  TRUE IS NOT UNKNOWN  |  TRUE  |  becomes  |  "(TRUE IS TRUE) OR (TRUE IS FALSE)" -- hence TRUE  | 
|  FALSE  |  FALSE IS NOT UNKNOWN  |  TRUE  |  becomes  |  "(FALSE IS TRUE) OR (FALSE IS FALSE)" -- hence TRUE  | 
|  UNKNOWN  |  UNKNOWN IS NOT UNKNOWN  |  FALSE  |  becomes  |  "(UNKNOWN IS TRUE) OR (UNKNOWN IS FALSE)" -- hence FALSE, since UNKNOWN is neither TRUE not FALSE  | 

Since IS NOT UNKNOWN is a special operator, the operations above are not transitive around the word IS:


| Operation | Result | 
| --- | --- | 
|  NOT UNKNOWN IS TRUE  |  FALSE  | 
|  NOT UNKNOWN IS FALSE  |  FALSE  | 
|  NOT UNKNOWN IS UNKNOWN  |  TRUE  | 


**IS NULL and IS NOT NULL**  

| Operation | Result | 
| --- | --- | 
|  UNKNOWN IS NULL  |  TRUE  | 
|  UNKNOWN IS NOT NULL  |  FALSE  | 
|  NULL IS NULL  |  TRUE  | 
|  NULL IS NOT NULL  |  FALSE  | 


**IS DISTINCT FROM and IS NOT DISTINCT FROM**  

| Operation | Result | 
| --- | --- | 
|  UNKNOWN IS DISTINCT FROM TRUE  |  TRUE  | 
|  UNKNOWN IS DISTINCT FROM FALSE  |  TRUE  | 
|  UNKNOWN IS DISTINCT FROM UNKNOWN  |  FALSE  | 
|  UNKNOWN IS NOT DISTINCT FROM TRUE  |  FALSE  | 
|  UNKNOWN IS NOT DISTINCT FROM FALSE  |  FALSE  | 
|  UNKNOWN IS NOT DISTINCT FROM UNKNOWN  |  TRUE  | 



Informally, "x IS DISTINCT FROM y" is similar to "x <> y", except that it is true even when either x or y (but not both) is NULL. DISTINCT FROM is the opposite of identical, whose usual meaning is that a value (true, false, or unknown) is identical to itself, and distinct from every other value. The IS and IS NOT operators treat UNKOWN in a special way, because it represents "maybe TRUE, maybe FALSE".

## Other Logical Operators
<a name="w2aac10c19c25b9"></a>

For all other operators, passing a NULL or UNKNOWN operand will cause the result to be UNKNOWN (which is the same as NULL).


**Examples**  

| Operation | Result | 
| --- | --- | 
|  TRUE AND CAST( NULL AS BOOLEAN)  |  UNKNOWN  | 
|  FALSE AND CAST( NULL AS BOOLEAN)  |  FALSE  | 
|  1 > 2  |  FALSE  | 
|  1 < 2  |  TRUE  | 
|  'foo' = 'bar'  |  FALSE  | 
|  'foo' <> 'bar'  |  TRUE  | 
|  'foo' <= 'bar'  |  FALSE  | 
|  'foo' <= 'bar'  |  TRUE  | 
|  3 BETWEEN 1 AND 5  |  TRUE  | 
|  1 BETWEEN 3 AND 5  |  FALSE  | 
|  3 BETWEEN 3 AND 5  |  TRUE  | 
|  5 BETWEEN 3 AND 5  |  TRUE  | 
|  1 IS DISTINCT FROM 1.0  |  FALSE  | 
|  CAST( NULL AS INTEGER ) IS NOT DISTINCT FROM CAST (NULL AS INTEGER)  |  TRUE  | 

# Expressions and Literals
<a name="sql-reference-expressions"></a>
<a name="value-expression"></a>
**Value expressions**  
Value expressions are defined by the following syntax:

```
value-expression := <character-expression > | <number-expression> | <datetime-expression> | <interval-expression> | <boolean-expression>
```
<a name="character-expression"></a>
**Character (string) expressions**  
Character expressions are defined by the following syntax:

```
character-expression := <character-literal>
                  | <character-expression> || <character-expression>
                  | <character-function> ( <parameters> )

 character-literal  := <quote> { <character> }* <quote>
 string-literal     := <quote> { <character> }* <quote>
 character-function   :=   CAST | COALESCE | CURRENT_PATH
                  | FIRST_VALUE  | INITCAP | LAST_VALUE
                  | LOWER | MAX | MIN | NULLIF
                  | OVERLAY | SUBSTRING| SYSTEM_USER
                  | TRIM | UPPER 
                  | <user-defined-function>
```

Note that Amazon Kinesis Data Analytics streaming SQL supports unicode character literals, such as u&'foo'. As in the use of regular literals, you can escape single quotes in these, such as u&'can''t'. Unlike regular literals, you can have unicode escapes: e.g., u&'\$10009' is a string consisting only of a tab character. You can escape a \$1 with another \$1, such as u&'back\$1\$1slash'. Amazon Kinesis Data Analytics also supports alternate escape characters, such as u&'\$10009\$1\$1' uescape '\$1' is a tab character.
<a name="numeric-expression"></a>
**Numeric expressions**  
Numeric expressions are defined by the following syntax:

```
number-expression := <number-literal>
                  | <number-unary-oper> <number-expression>
                  | <number-expression> <number-operator> <number-expression>
                  | <number-function> [ ( <parameters> ) ]
 number-literal :=   <UNSIGNED_INTEGER_LITERAL> | <DECIMAL_NUMERIC_LITERAL>
                           | <APPROX_NUMERIC_LITERAL>
```



```
--Note: An <APPROX_NUMERIC_LITERAL> is a number in scientific notation, such as with an 
--exponent, such as 1e2 or -1.5E-6.
number-unary-oper := + | -
 number-operator      :=   + | - | / | *

 number-function      :=   ABS | AVG | CAST | CEIL
                          | CEILING | CHAR_LENGTH
                          | CHARACTER_LENGTH | COALESCE
                          | COUNT | EXP | EXTRACT
                          | FIRST_VALUE
                          | FLOOR | LAST_VALUE
                          | LN | LOG10
                          | MAX | MIN  | MOD
                          | NULLIF
                          | POSITION | POWER
                          | SUM| <user-defined-function>
```
<a name="datetime-expression"></a>
**Date / Time expressions**  
Date / Time expressions are defined by the following syntax:

```
datetime-expression := <datetime-literal>
                          | <datetime-expression> [ + | - ] <number-expression>
                          | <datetime-function> [ ( <parameters> ) ]
 datetime-literal    :=  <left_brace> { <character-literal> } *  <right_brace>
                          |  <DATE> { <character-literal> } *
                          |  <TIME> { <character-literal> } *
                          |  <TIMESTAMP> { <character-literal> } *
 datetime-function   :=    CAST | CEIL | CEILING
                          | CURRENT_DATE | CURRENT_ROW_TIMESTAMP
                          | CURRENT_ROW_TIMESTAMP
                          | FIRST_VALUE| FLOOR
                          | LAST_VALUE | LOCALTIME
                          | LOCALTIMESTAMP | MAX | MIN
                          | NULLIF | ROWTIME
                          | <user-defined-function>
 <time unit>         :=  YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
```
<a name="INTERVAL-EXPRESSION"></a>
**Interval Expression**  
Interval expressions are defined by the following syntax:

```
interval-expression := <interval-literal>
                          | <interval-function>
 interval-literal    :=    <INTERVAL> ( <MINUS> | <PLUS> ) <QUOTED_STRING> <IntervalQualifier>
 IntervalQualifier  :=  <YEAR> ( <UNSIGNED_INTEGER_LITERAL> )
                          | <YEAR> ( <UNSIGNED_INTEGER_LITERAL> ) <TO> <MONTH>
                          |  <MONTH> [ ( <UNSIGNED_INTEGER_LITERAL> ) ]
                          |  <DAY> [ (  <UNSIGNED_INTEGER_LITERAL> )  ]
                          |  <DAY> [ (  <UNSIGNED_INTEGER_LITERAL> )  ] <TO>
                                    { <HOUR> | <MINUTE> | <SECOND> [ ( <UNSIGNED_INTEGER_LITERAL> ) ] }
                          |  <HOUR> [ (  <UNSIGNED_INTEGER_LITERAL> ) ]
                          |  <HOUR> [ (  <UNSIGNED_INTEGER_LITERAL> ) ] <TO>
                                    { <MINUTE> | <SECOND> [ <UNSIGNED_INTEGER_LITERAL> ] }
                          |  <MINUTE> [ ( <UNSIGNED_INTEGER_LITERAL> )  ]
                          |  <MINUTE> [ ( <UNSIGNED_INTEGER_LITERAL> )  ] <TO>
                                     <SECOND> [ ( <UNSIGNED_INTEGER_LITERAL> ) ]
                          |  <SECOND> [ ( <UNSIGNED_INTEGER_LITERAL> )  ]
 interval-function   :=    ABS  | CAST | FIRST_VALUE
                          | LAST_VALUE | MAX | MIN
                          | NULLIF| <user-defined-function>
```
<a name="BOOLEAN_EXPRESSION"></a>
**Boolean expression**  
Boolean expressions are defined by the following syntax:

```
boolean-expression := <boolean-literal>
                  | <boolean-expression> <boolean-operator> <boolean-expression>
                  | <boolean-unary-oper> <boolean-expression>
                         | <boolean-function> ( <parameters> )
                         | ( <boolean-expression> )
 boolean-literal    :=  TRUE | FALSE
 boolean-operator   :=  AND | OR
 boolean-unary-oper :=  NOT
 boolean-function   :=    CAST | FIRST_VALUE | LAST_VALUE
                         | NULLIF | <user-defined-function>
```

# Monotonic Expressions and Operators
<a name="sql-reference-monotonic-expressions-operators"></a>

Since Amazon Kinesis Data Analytics queries operate on infinite streams of rows, some operations are only possible if something is known about those streams.

For example, given a stream of orders, it makes sense to ask for a stream summarizing orders by day and product (because day is increasing) but not to ask for a stream summarizing orders by product and shipping state. We can never complete the summary of, say Widget X to Oregon, because we never see the 'last' order of a Widget to Oregon.

This property, of a stream being sorted by a particular column or expression, is called monotonicity.

Some time-related definitions:
+ **Monotonic**. An expression is monotonic if it is ascending or descending. An equivalent phrasing is "non-decreasing or non-increasing."
+ **Ascending**. An expression e is ascending within a stream if the value of e for a given row is always greater than or equal to the value in the previous row.
+ **Descending**. An expression e is descending within a stream if the value of e for a given row is always less than or equal to the value in the previous row.
+ **Strictly Ascending**. An expression e is strictly ascending within a stream if for the value of e for a given row is always greater than the value in the previous row.
+ **Strictly Descending**. An expression e is strictly descending within a stream if the value of e for a given row is always less than the value in the previous row.
+ **Constant**. An expression e is constant within a stream if the value of e for a given row is always equal to the value in the previous row.

Note that by this definition, a constant expression is considered monotonic.

## Monotonic columns
<a name="sql-reference-monotonic-expressions-columns"></a>

The ROWTIME system column is ascending. The ROWTIME column is not strictly ascending: it is acceptable for consecutive rows to have the same timestamp.

Amazon Kinesis Data Analytics prevents a client from inserting a row into a stream whose timestamp is less than the previous row it wrote into the stream. Amazon Kinesis Data Analytics also ensures that if multiple clients are inserting rows into the same stream, the rows are merged so that the ROWTIME column is ascending.

Clearly it would be useful to assert, for instance, that the orderId column is ascending; or that no orderId is ever more than 100 rows from sorted order. However, declared sort keys are not supported in the current release.

## Monotonic expressions
<a name="sql-reference-monotonic-expressions-expressions"></a>

Amazon Kinesis Data Analytics can deduce that an expression is monotonic if it knows that its arguments are monotonic. (See also the [Monotonic Function](sql-reference-monotonic.md).)

Another definition:

Functions or operators that are monotonic

A function or operator is monotonic if, when applied to a strictly increasing sequence of values, it yields a monotonic sequence of results.

For example, the FLOOR function, when applied to the ascending inputs \$11.5, 3, 5, 5.8, 6.3\$1, yields \$11, 3, 5, 5, 6\$1. Note that the input is strictly ascending, but the output is merely ascending (includes duplicate values).

## Rules for deducing monotonicity
<a name="sql-reference-monotonic-rules"></a>

Amazon Kinesis Data Analytics requires that one or more grouping expressions are valid in order for a streaming GROUP BY statement to be valid. In other cases, Amazon Kinesis Data Analytics may be able to operate more efficiently if it knows about monotonicity; for example it may be able to remove entries from a table of windowed aggregate totals if it knows that a particular key will never be seen on the stream again.

In order to exploit monotonicity in this way, Amazon Kinesis Data Analytics uses a set of rules for deducing the monotonicity of an expression. Here are the rules for deducing monotonicity: 


| Expression | Monotonicity | 
| --- | --- | 
|  c  |  Constant  | 
|  [FLOOR](sql-reference-floor.md)(m)  |  Same as m, but not strict  | 
|  [CEIL / CEILING](sql-reference-ceil.md)(m)  |  Same as m, but not strict  | 
|  [CEIL / CEILING](sql-reference-ceil.md)(m TO timeUnit)  |  Same as m, but not strict  | 
|  [FLOOR](sql-reference-floor.md)(m TO timeUnit)  |  Same as m, but not strict  | 
|  [SUBSTRING](sql-reference-substring.md)(m FROM 0 FOR c)  |  Same as m, but not strict  | 
|  \$1 m  |  Same as m  | 
|  - m  |  Reverse of m  | 
|  m \$1 c c \$1 m  |  Same as m  | 
|  m1 \$1 m2  |  Same as m1, if m1 and m2 have same direction; otherwise not monotonic  | 
|  c - m  |  Reverse of m  | 
|  m \$1 c c \$1 m  |  Same as m if c is positive; reverse of m is c is negative; constant (0) c is 0  | 
|  c / m  |  Same as m if m is always positive or always negative, and c and m have same sign; reverse of m if m is always positive or always negative, and c and m have different sign; otherwise not monotonic  | 
|   |  Constant  | 
|  [LOCALTIME](sql-reference-localtime.md) [LOCALTIMESTAMP](sql-reference-local-timestamp.md) [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md) [CURRENT\$1DATE](sql-reference-current-date.md)  |  Ascending  | 

Throughout the table, c is a constant, and m (also m1 and m2) is a monotonic expression.

# Condition Clause
<a name="sql-reference-conditions"></a>

Referenced by:
+ SELECT clauses: [HAVING clause](sql-reference-having-clause.md), [WHERE clause](sql-reference-where-clause.md), and [JOIN clause](sql-reference-join-clause.md). (See also the SELECT chart and its [SELECT clause](sql-reference-select-clause.md).)
+ DELETE

A condition is any value expression of type BOOLEAN, such as the following examples:
+ 2<4
+ TRUE
+ FALSE
+ expr\$117 IS NULL
+ NOT expr\$119 IS NULL AND expr\$123 < expr>29
+ expr\$117 IS NULL OR ( NOT expr\$119 IS NULL AND expr\$123 < expr>29 )

# Temporal Predicates
<a name="sql-reference-temporal-predicate"></a>

The following table shows a graphic representation of temporal predicates supported by standard SQL and extensions to the SQL standard supported by Amazon Kinesis Data Analytics. It shows the relationships that each predicate covers. Each relationship is represented as an upper interval and a lower interval with the combined meaning *upperInterval predicate lowerInterval evaluates to TRUE*. The first 7 predicates are standard SQL. The last 10 predicates, shown in bold text, are Amazon Kinesis Data Analytics extensions to the SQL standard.


| Predicate | Covered Relationships | 
| --- | --- | 
|  CONTAINS  |  ![\[Blue rectangular shapes arranged in horizontal rows, resembling a simplified layout or structure.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_contains.png)  | 
|  OVERLAPS  |  ![\[Blue rectangular boxes arranged in rows, representing a structured layout or diagram.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_overlaps.png)  | 
|  EQUALS  |  ![\[Two horizontal blue rectangles with orange borders, stacked vertically.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_equals.png)  | 
|  PRECEDES  |  ![\[Four blue rectangular shapes representing placeholder text or content blocks.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_precedes.png)  | 
|  SUCCEEDS  |  ![\[Four blue rectangular shapes arranged horizontally with gaps between them.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_succeeds.png)  | 
|  IMMEDIATELY PRECEDES  |  ![\[Blue rectangular shapes representing text or content placeholders.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_precedes.png)  | 
|  IMMEDIATELY SUCCEEDS  |  ![\[Two horizontal blue rectangular shapes against a white background.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_succeeds.png)  | 
|  **LEADS**  |  ![\[Four blue rectangular buttons with orange outlines, arranged horizontally.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_leads.png)  | 
|  **LAGS**  |  ![\[Four blue rectangular bars of varying lengths arranged horizontally.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_lags.png)  | 
|  **STRICTLY CONTAINS**  |  ![\[Two blue rectangular shapes with orange outlines, one larger above a smaller one.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_contains.png)  | 
|  **STRICTLY OVERLAPS**  |  ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_overlaps.png)  | 
|  **STRICTLY PRECEDES**  |  ![\[Two horizontal blue bars representing placeholder elements in a user interface.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_precedes.png)  | 
|  **STRICTLY SUCCEEDS**  |  ![\[Two blue rectangular shapes representing UI elements or buttons.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_succeeds.png)  | 
|  **STRICTLY LEADS**  |  ![\[Two blue rectangular shapes representing text or content blocks.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_leads.png)  | 
|  **STRICTLY LAGS**  |  ![\[Two horizontal blue rectangular shapes, one above the other, against a white background.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_lags.png)  | 
|  **IMMEDIATELY LEADS**  |  ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_leads.png)  | 
|  **IMMEDIATELY LAGS**  |  ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_lags.png)  | 

To enable concise expressions, Amazon Kinesis Data Analytics also supports the following extensions: 
+ Optional PERIOD keyword – The PERIOD keyword can be omitted.
+ Compact chaining – If two of these predicates occur back to back, separated by an AND, the AND can be omitted provided that the right interval of the first predicate is identical to the left interval of the second predicate.
+ TSDIFF – This function takes two TIMESTAMP arguments and returns their difference in milliseconds.

For example, you can write the following expression:

```
  PERIOD (s1,e1) PRECEDES PERIOD(s2,e2)
  AND PERIOD(s2, e2) PRECEDES PERIOD(s3,e3)
```

More concisely as follows:

```
(s1,e1) PRECEDES (s2,e2) PRECEDES PERIOD(s3,e3)
```

The following concise expression:

```
TSDIFF(s,e) 
```

Means the following:

 

```
CAST((e - s) SECOND(10, 3) * 1000 AS BIGINT)
```

Finally, standard SQL allows the CONTAINS predicate to take a single TIMESTAMP as its right-hand argument. For example, the following expression:

```
PERIOD(s, e) CONTAINS t
```

Is equivalent to the following:

```
s <= t AND t < e
```

## Syntax
<a name="sql-reference-temporal-predicate-syntax"></a>

Temporal predicates are integrated into a new BOOLEAN valued expression:

```
<period-expression> :=
  <left-period> <half-period-predicate> <right-period>

<half-period-predicate> := 
  <period-predicate> [ <left-period> <half-period-predicate> ]

<period-predicate> :=
   EQUALS
 | [ STRICTLY ] CONTAINS
 | [ STRICTLY ] OVERLAPS
 | [ STRICTLY | IMMEDIATELY ] PRECEDES
 | [ STRICTLY | IMMEDIATELY ] SUCCEEDS
 | [ STRICTLY | IMMEDIATELY ] LEADS
 | [ STRICTLY | IMMEDIATELY ] LAGS

<left-period> := <bounded-period>

<right-period> := <bounded-period> | <timestamp-expression>

<bounded-period> := [ PERIOD ] ( <start-time>, <end-time> )

<start-time> := <timestamp-expression>

<end-time> := <timestamp-expression>

<timestamp-expression> :=
  an expression which evaluates to a TIMESTAMP value

where <right-period> may evaluate to a <timestamp-expression> only if
the immediately preceding <period-predicate> is [ STRICTLY ] CONTAINS
```

This Boolean expression is supported by the following builtin function:

```
BIGINT tsdiff( startTime TIMESTAMP, endTime TIMESTAMP )
```

Returns the value of (endTime - startTime) in milliseconds.

```
```

## Example
<a name="sql-reference-temporal-predicate-example"></a>

The following example code records an alarm if a window is open while the air conditioning is on:

```
create or replace pump alarmPump stopped as
  insert into alarmStream( houseID, roomID, alarmTime, alarmMessage )
    select stream w.houseID, w.roomID, current_timestamp,
                   'Window open while air conditioner is on.'
    from
        windowIsOpenEvents over (range interval '1' minute preceding) w
    join
        acIsOnEvents over (range interval '1' minute preceding) h
    on w.houseID = h.houseID
    where (h.startTime, h.endTime) overlaps (w.startTime, w.endTime);
```

## Sample Use Case
<a name="sql-reference-temporal-predicate-sample-use-case"></a>

The following query uses a temporal predicate to raise a fraud alarm when two people try to use the same credit card simultaneously at two different locations:

```
create pump creditCardFraudPump stopped as
 insert into alarmStream
  select stream
    current_timestamp, creditCardNumber, registerID1, registerID2
  from transactionsPerCreditCard
  where registerID1 <> registerID2
  and (startTime1, endTime1) overlaps (startTime2, endTime2)
;
```

The preceding code example uses an input stream with the following dataset:

```
(current_timestamp  TIMESTAMP,
  creditCardNumber  VARCHAR(16),
  registerID1       VARCHAR(16),
  registerID2       VARCHAR(16),
  startTime1        TIMESTAMP,
  endTime1          TIMESTAMP,
  startTime2        TIMESTAMP,
  endTime2          TIMESTAMP)
```

# Reserved Words and Keywords
<a name="sql-reference-reserved-words-keywords"></a>

**Reserved Words**  
The following is a list of reserved words in Amazon Kinesis Data Analytics applications as of version 5.0.1.


|  | 
| --- |
| A | 
| ABS | ALL | ALLOCATE | 
| ALLOW | ALTER | ANALYZE | 
| AND | ANY | APPROXIMATE\$1ARRIVAL\$1TIME | 
| ARE | ARRAY | AS | 
| ASENSITIVE | ASYMMETRIC | AT | 
| ATOMIC | AUTHORIZATION | AVG | 
| B | 
| BEGIN | BETWEEN | BIGINT | 
| BINARY | BIT | BLOB | 
| BOOLEAN | BOTH | BY | 
| C | 
| CALL | CALLED | CARDINALITY | 
| CASCADED | CASE | CAST | 
| CEIL | CEILING | CHAR | 
| CHARACTER | CHARACTER\$1LENGTH | CHAR\$1LENGTH | 
| CHECK | CHECKPOINT | CLOB | 
| CLOSE | CLUSTERED | COALESCE | 
| COLLATE | COLLECT | COLUMN | 
| COMMIT | CONDITION | CONNECT | 
| CONSTRAINT | CONVERT | CORR | 
| CORRESPONDING | COUNT | COVAR\$1POP | 
| COVAR\$1SAMP | CREATE | CROSS | 
| CUBE | CUME\$1DIST | CURRENT | 
| CURRENT\$1CATALOG | CURRENT\$1DATE | CURRENT\$1DEFAULT\$1TRANSFORM\$1GROUP | 
| CURRENT\$1PATH | CURRENT\$1ROLE | CURRENT\$1SCHEMA | 
| CURRENT\$1TIME | CURRENT\$1TIMESTAMP | CURRENT\$1TRANSFORM\$1GROUP\$1FOR\$1TYPE | 
| CURRENT\$1USER | CURSOR | CYCLE | 
| D | 
| DATE | DAY | DEALLOCATE | 
| DEC | DECIMAL | DECLARE | 
| DEFAULT | DELETE | DENSE\$1RANK | 
| DEREF | DESCRIBE | DETERMINISTIC | 
| DISALLOW | DISCONNECT | DISTINCT | 
| DOUBLE | DROP | DYNAMIC | 
| E | 
| EACH | ELEMENT | ELSE | 
| END | END-EXEC | ESCAPE | 
| EVERY | EXCEPT | EXEC | 
| EXECUTE | EXISTS | EXP | 
| EXPLAIN | EXP\$1AVG | EXTERNAL | 
| EXTRACT | 
| F | 
| FALSE | FETCH | FILTER | 
| FIRST\$1VALUE | FLOAT | FLOOR | 
| FOR | FOREIGN | FREE | 
| FROM | FULL | FUNCTION | 
| FUSION | 
| G | 
| GET | GLOBAL | GRANT | 
| GROUP | GROUPING | 
| H | 
| HAVING | HOLD | HOUR | 
| I | 
| IDENTITY | IGNORE | IMPORT | 
| IN | INDICATOR | INITCAP | 
| INNER | INOUT | INSENSITIVE | 
| INSERT | INT | INTEGER | 
| INTERSECT | INTERSECTION | INTERVAL | 
| INTO | IS | 
| J | 
| JOIN | 
| L | 
| LANGUAGE | LARGE | LAST\$1VALUE | 
| LATERAL | LEADING | LEFT | 
| LIKE | LIMIT | LN | 
| LOCAL | LOCALTIME | LOCALTIMESTAMP | 
| LOWER | 
| M | 
| MATCH | MAX | MEMBER | 
| MERGE | METHOD | MIN | 
| MINUTE | MOD | MODIFIES | 
| MODULE | MONTH | MULTISET | 
| N | 
| NATIONAL | NATURAL | NCHAR | 
| NCLOB | NEW | NO | 
| NODE | NONE | NORMALIZE | 
| NOT | NTH\$1VALUE | NULL | 
| NULLIF | NUMERIC | 
| O | 
| OCTET\$1LENGTH | OF | OLD | 
| ON | ONLY | OPEN | 
| OR | ORDER | OUT | 
| OUTER | OVER | OVERLAPS | 
| OVERLAY | 
| P | 
| PARAMETER | PARTITION | PARTITION\$1ID | 
| PARTITION\$1KEY | PERCENTILE\$1CONT | PERCENTILE\$1DISC | 
| PERCENT\$1RANK | POSITION | POWER | 
| PRECISION | PREPARE | PRIMARY | 
| PROCEDURE | 
| R | 
| RANGE | RANK | READS | 
| REAL | RECURSIVE | REF | 
| REFERENCES | REFERENCING | REGR\$1AVGX | 
| REGR\$1AVGY | REGR\$1COUNT | REGR\$1INTERCEPT | 
| REGR\$1R2 | REGR\$1SLOPE | REGR\$1SXX | 
| REGR\$1SXY | RELEASE | RESPECT | 
| RESULT | RETURN | RETURNS | 
| REVOKE | RIGHT | ROLLBACK | 
| ROLLUP | ROW | ROWS | 
| ROWTIME | ROW\$1NUMBER | 
| S | 
| SAVEPOINT | SCOPE | SCROLL | 
| SEARCH | SECOND | SELECT | 
| SENSITIVE | SEQUENCE\$1NUMBER | SESSION\$1USER | 
| SET | SHARD\$1ID | SIMILAR | 
| SMALLINT | SOME | SORT | 
| SPECIFIC | SQL | SQLEXCEPTION | 
| SQLSTATE | SQLWARNING | SQRT | 
| START | STATIC | STDDEV | 
| STDDEV\$1POP | STDDEV\$1SAMP | STOP | 
| STREAM | SUBMULTISET | SUBSTRING | 
| SUM | SYMMETRIC | SYSTEM | 
| SYSTEM\$1USER | 
| T | 
| TABLE | TABLESAMPLE | THEN | 
| TIME | TIMESTAMP | TIMEZONE\$1HOUR | 
| TIMEZONE\$1MINUTE | TINYINT | TO | 
| TRAILING | TRANSLATE | TRANSLATION | 
| TREAT | TRIGGER | TRIM | 
| TRUE | TRUNCATE | 
| U | 
| UESCAPE | UNION | UNIQUE | 
| UNKNOWN | UNNEST | UPDATE | 
| UPPER | USER | USING | 
| V | 
| VALUE | VALUES | VARBINARY | 
| VARCHAR | VARYING | VAR\$1POP | 
| VAR\$1SAMP | 
| W | 
| WHEN | WHENEVER | WHERE | 
| WIDTH\$1BUCKET | WINDOW | WITH | 
| WITHIN | WITHOUT |  | 
| Y | 
| YEAR |