# 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  |