You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by mi...@apache.org on 2018/05/09 21:10:27 UTC
[18/51] [partial] impala git commit: [DOCS] Impala doc site update
for 3.0
http://git-wip-us.apache.org/repos/asf/impala/blob/fae51ec2/docs/build3x/html/topics/impala_operators.html
----------------------------------------------------------------------
diff --git a/docs/build3x/html/topics/impala_operators.html b/docs/build3x/html/topics/impala_operators.html
new file mode 100644
index 0000000..e03240b
--- /dev/null
+++ b/docs/build3x/html/topics/impala_operators.html
@@ -0,0 +1,2042 @@
+<!DOCTYPE html
+ SYSTEM "about:legacy-compat">
+<html lang="en"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><meta charset="UTF-8"><meta name="copyright" content="(C) Copyright 2018"><meta name="DC.rights.owner" content="(C) Copyright 2018"><meta name="DC.Type" content="concept"><meta name="DC.Relation" scheme="URI" content="../topics/impala_langref.html"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="version" content="Impala 3.0.x"><meta name="version" content="
Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="version" content="Impala 3.0.x"><meta name="DC.Format" content="XHTML"><meta name="DC.Identifier" content="operators"><link rel="stylesheet" type="text/css" href="../commonltr.css"><title>SQL Operators</title></head><body id="operators"><main role="main"><article role="article" aria-labelledby="ariaid-title1">
+
+ <h1 class="title topictitle1" id="ariaid-title1">SQL Operators</h1>
+
+
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ SQL operators are a class of comparison functions that are widely used within the <code class="ph codeph">WHERE</code> clauses of
+ <code class="ph codeph">SELECT</code> statements.
+ </p>
+
+ <p class="p toc inpage"></p>
+
+ </div>
+
+ <nav role="navigation" class="related-links"><div class="familylinks"><div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_langref.html">Impala SQL Language Reference</a></div></div></nav><article class="topic concept nested1" aria-labelledby="ariaid-title2" id="operators__arithmetic_operators">
+
+ <h2 class="title topictitle2" id="ariaid-title2">Arithmetic Operators</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ The arithmetic operators use expressions with a left-hand argument, the operator, and then (in most cases) a right-hand argument.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">left_hand_arg</var> <var class="keyword varname">binary_operator</var> <var class="keyword varname">right_hand_arg</var>
+<var class="keyword varname">unary_operator</var> <var class="keyword varname">single_arg</var>
+</code></pre>
+
+ <ul class="ul">
+ <li class="li">
+ <code class="ph codeph">+</code> and <code class="ph codeph">-</code>: Can be used either as unary or binary operators.
+ <ul class="ul">
+ <li class="li">
+ <p class="p">
+ With unary notation, such as <code class="ph codeph">+5</code>, <code class="ph codeph">-2.5</code>, or <code class="ph codeph">-<var class="keyword varname">col_name</var></code>,
+ they multiply their single numeric argument by <code class="ph codeph">+1</code> or <code class="ph codeph">-1</code>. Therefore, unary
+ <code class="ph codeph">+</code> returns its argument unchanged, while unary <code class="ph codeph">-</code> flips the sign of its argument. Although
+ you can double up these operators in expressions such as <code class="ph codeph">++5</code> (always positive) or <code class="ph codeph">-+2</code> or
+ <code class="ph codeph">+-2</code> (both always negative), you cannot double the unary minus operator because <code class="ph codeph">--</code> is
+ interpreted as the start of a comment. (You can use a double unary minus operator if you separate the <code class="ph codeph">-</code>
+ characters, for example with a space or parentheses.)
+ </p>
+ </li>
+
+ <li class="li">
+ <p class="p">
+ With binary notation, such as <code class="ph codeph">2+2</code>, <code class="ph codeph">5-2.5</code>, or <code class="ph codeph"><var class="keyword varname">col1</var> +
+ <var class="keyword varname">col2</var></code>, they add or subtract respectively the right-hand argument to (or from) the left-hand
+ argument. Both arguments must be of numeric types.
+ </p>
+ </li>
+ </ul>
+ </li>
+
+ <li class="li">
+ <p class="p">
+ <code class="ph codeph">*</code> and <code class="ph codeph">/</code>: Multiplication and division respectively. Both arguments must be of numeric types.
+ </p>
+
+ <p class="p">
+ When multiplying, the shorter argument is promoted if necessary (such as <code class="ph codeph">SMALLINT</code> to <code class="ph codeph">INT</code> or
+ <code class="ph codeph">BIGINT</code>, or <code class="ph codeph">FLOAT</code> to <code class="ph codeph">DOUBLE</code>), and then the result is promoted again to the
+ next larger type. Thus, multiplying a <code class="ph codeph">TINYINT</code> and an <code class="ph codeph">INT</code> produces a <code class="ph codeph">BIGINT</code>
+ result. Multiplying a <code class="ph codeph">FLOAT</code> and a <code class="ph codeph">FLOAT</code> produces a <code class="ph codeph">DOUBLE</code> result. Multiplying
+ a <code class="ph codeph">FLOAT</code> and a <code class="ph codeph">DOUBLE</code> or a <code class="ph codeph">DOUBLE</code> and a <code class="ph codeph">DOUBLE</code> produces a
+ <code class="ph codeph">DECIMAL(38,17)</code>, because <code class="ph codeph">DECIMAL</code> values can represent much larger and more precise values than
+ <code class="ph codeph">DOUBLE</code>.
+ </p>
+
+ <p class="p">
+ When dividing, Impala always treats the arguments and result as <code class="ph codeph">DOUBLE</code> values to avoid losing precision. If you
+ need to insert the results of a division operation into a <code class="ph codeph">FLOAT</code> column, use the <code class="ph codeph">CAST()</code>
+ function to convert the result to the correct type.
+ </p>
+ </li>
+
+ <li class="li" id="arithmetic_operators__div">
+ <p class="p">
+ <code class="ph codeph">DIV</code>: Integer division. Arguments are not promoted to a floating-point type, and any fractional result
+ is discarded. For example, <code class="ph codeph">13 DIV 7</code> returns 1, <code class="ph codeph">14 DIV 7</code> returns 2, and
+ <code class="ph codeph">15 DIV 7</code> returns 2. This operator is the same as the <code class="ph codeph">QUOTIENT()</code> function.
+ </p>
+ </li>
+
+ <li class="li">
+ <p class="p">
+ <code class="ph codeph">%</code>: Modulo operator. Returns the remainder of the left-hand argument divided by the right-hand argument. Both
+ arguments must be of one of the integer types.
+ </p>
+ </li>
+
+ <li class="li">
+ <p class="p">
+ <code class="ph codeph">&</code>, <code class="ph codeph">|</code>, <code class="ph codeph">~</code>, and <code class="ph codeph">^</code>: Bitwise operators that return the
+ logical AND, logical OR, <code class="ph codeph">NOT</code>, or logical XOR (exclusive OR) of their argument values. Both arguments must be of
+ one of the integer types. If the arguments are of different type, the argument with the smaller type is implicitly extended to
+ match the argument with the longer type.
+ </p>
+ </li>
+ </ul>
+
+ <p class="p">
+ You can chain a sequence of arithmetic expressions, optionally grouping them with parentheses.
+ </p>
+
+ <p class="p">
+ The arithmetic operators generally do not have equivalent calling conventions using functional notation. For example, prior to
+ <span class="keyword">Impala 2.2</span>, there is no <code class="ph codeph">MOD()</code> function equivalent to the <code class="ph codeph">%</code> modulo operator.
+ Conversely, there are some arithmetic functions that do not have a corresponding operator. For example, for exponentiation you use
+ the <code class="ph codeph">POW()</code> function, but there is no <code class="ph codeph">**</code> exponentiation operator. See
+ <a class="xref" href="impala_math_functions.html#math_functions">Impala Mathematical Functions</a> for the arithmetic functions you can use.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ To access a column with a complex type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>)
+ in an aggregation function, you unpack the individual elements using join notation in the query,
+ and then apply the function to the final scalar item, field, key, or value at the bottom of any nested type hierarchy in the column.
+ See <a class="xref" href="../shared/../topics/impala_complex_types.html#complex_types">Complex Types (Impala 2.3 or higher only)</a> for details about using complex types in Impala.
+ </p>
+
+ <div class="p">
+The following example demonstrates calls to several aggregation functions
+using values from a column containing nested complex types
+(an <code class="ph codeph">ARRAY</code> of <code class="ph codeph">STRUCT</code> items).
+The array is unpacked inside the query using join notation.
+The array elements are referenced using the <code class="ph codeph">ITEM</code>
+pseudocolumn, and the structure fields inside the array elements
+are referenced using dot notation.
+Numeric values such as <code class="ph codeph">SUM()</code> and <code class="ph codeph">AVG()</code>
+are computed using the numeric <code class="ph codeph">R_NATIONKEY</code> field, and
+the general-purpose <code class="ph codeph">MAX()</code> and <code class="ph codeph">MIN()</code>
+values are computed from the string <code class="ph codeph">N_NAME</code> field.
+<pre class="pre codeblock"><code>describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+select r_name, r_nations.item.n_nationkey
+ from region, region.r_nations as r_nations
+order by r_name, r_nations.item.n_nationkey;
++-------------+------------------+
+| r_name | item.n_nationkey |
++-------------+------------------+
+| AFRICA | 0 |
+| AFRICA | 5 |
+| AFRICA | 14 |
+| AFRICA | 15 |
+| AFRICA | 16 |
+| AMERICA | 1 |
+| AMERICA | 2 |
+| AMERICA | 3 |
+| AMERICA | 17 |
+| AMERICA | 24 |
+| ASIA | 8 |
+| ASIA | 9 |
+| ASIA | 12 |
+| ASIA | 18 |
+| ASIA | 21 |
+| EUROPE | 6 |
+| EUROPE | 7 |
+| EUROPE | 19 |
+| EUROPE | 22 |
+| EUROPE | 23 |
+| MIDDLE EAST | 4 |
+| MIDDLE EAST | 10 |
+| MIDDLE EAST | 11 |
+| MIDDLE EAST | 13 |
+| MIDDLE EAST | 20 |
++-------------+------------------+
+
+select
+ r_name,
+ count(r_nations.item.n_nationkey) as count,
+ sum(r_nations.item.n_nationkey) as sum,
+ avg(r_nations.item.n_nationkey) as avg,
+ min(r_nations.item.n_name) as minimum,
+ max(r_nations.item.n_name) as maximum,
+ ndv(r_nations.item.n_nationkey) as distinct_vals
+from
+ region, region.r_nations as r_nations
+group by r_name
+order by r_name;
++-------------+-------+-----+------+-----------+----------------+---------------+
+| r_name | count | sum | avg | minimum | maximum | distinct_vals |
++-------------+-------+-----+------+-----------+----------------+---------------+
+| AFRICA | 5 | 50 | 10 | ALGERIA | MOZAMBIQUE | 5 |
+| AMERICA | 5 | 47 | 9.4 | ARGENTINA | UNITED STATES | 5 |
+| ASIA | 5 | 68 | 13.6 | CHINA | VIETNAM | 5 |
+| EUROPE | 5 | 77 | 15.4 | FRANCE | UNITED KINGDOM | 5 |
+| MIDDLE EAST | 5 | 58 | 11.6 | EGYPT | SAUDI ARABIA | 5 |
++-------------+-------+-----+------+-----------+----------------+---------------+
+</code></pre>
+</div>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+ <p class="p">
+ The following example shows how to do an arithmetic operation using a numeric field of a <code class="ph codeph">STRUCT</code> type that is an
+ item within an <code class="ph codeph">ARRAY</code> column. Once the scalar numeric value <code class="ph codeph">R_NATIONKEY</code> is extracted, it can be
+ used in an arithmetic expression, such as multiplying by 10:
+ </p>
+
+<pre class="pre codeblock"><code>
+-- The SMALLINT is a field within an array of structs.
+describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+-- When we refer to the scalar value using dot notation,
+-- we can use arithmetic and comparison operators on it
+-- like any other number.
+select r_name, nation.item.n_name, nation.item.n_nationkey * 10
+ from region, region.r_nations as nation
+where nation.item.n_nationkey < 5;
++-------------+-------------+------------------------------+
+| r_name | item.n_name | nation.item.n_nationkey * 10 |
++-------------+-------------+------------------------------+
+| AMERICA | CANADA | 30 |
+| AMERICA | BRAZIL | 20 |
+| AMERICA | ARGENTINA | 10 |
+| MIDDLE EAST | EGYPT | 40 |
+| AFRICA | ALGERIA | 0 |
++-------------+-------------+------------------------------+
+</code></pre>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title3" id="operators__between">
+
+ <h2 class="title topictitle2" id="ariaid-title3">BETWEEN Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ In a <code class="ph codeph">WHERE</code> clause, compares an expression to both a lower and upper bound. The comparison is successful is the
+ expression is greater than or equal to the lower bound, and less than or equal to the upper bound. If the bound values are switched,
+ so the lower bound is greater than the upper bound, does not match any values.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">expression</var> BETWEEN <var class="keyword varname">lower_bound</var> AND <var class="keyword varname">upper_bound</var></code></pre>
+
+ <p class="p">
+ <strong class="ph b">Data types:</strong> Typically used with numeric data types. Works with any data type, although not very practical for
+ <code class="ph codeph">BOOLEAN</code> values. (<code class="ph codeph">BETWEEN false AND true</code> will match all <code class="ph codeph">BOOLEAN</code> values.) Use
+ <code class="ph codeph">CAST()</code> if necessary to ensure the lower and upper bound values are compatible types. Call string or date/time
+ functions if necessary to extract or transform the relevant portion to compare, especially if the value can be transformed into a
+ number.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ Be careful when using short string operands. A longer string that starts with the upper bound value will not be included, because it
+ is considered greater than the upper bound. For example, <code class="ph codeph">BETWEEN 'A' and 'M'</code> would not match the string value
+ <code class="ph codeph">'Midway'</code>. Use functions such as <code class="ph codeph">upper()</code>, <code class="ph codeph">lower()</code>, <code class="ph codeph">substr()</code>,
+ <code class="ph codeph">trim()</code>, and so on if necessary to ensure the comparison works as expected.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>-- Retrieve data for January through June, inclusive.
+select c1 from t1 where month <strong class="ph b">between 1 and 6</strong>;
+
+-- Retrieve data for names beginning with 'A' through 'M' inclusive.
+-- Only test the first letter to ensure all the values starting with 'M' are matched.
+-- Do a case-insensitive comparison to match names with various capitalization conventions.
+select last_name from customers where upper(substr(last_name,1,1)) <strong class="ph b">between 'A' and 'M'</strong>;
+
+-- Retrieve data for only the first week of each month.
+select count(distinct visitor_id)) from web_traffic where dayofmonth(when_viewed) <strong class="ph b">between 1 and 7</strong>;</code></pre>
+
+ <p class="p">
+ The following example shows how to do a <code class="ph codeph">BETWEEN</code> comparison using a numeric field of a <code class="ph codeph">STRUCT</code> type
+ that is an item within an <code class="ph codeph">ARRAY</code> column. Once the scalar numeric value <code class="ph codeph">R_NATIONKEY</code> is extracted, it
+ can be used in a comparison operator:
+ </p>
+
+<pre class="pre codeblock"><code>
+-- The SMALLINT is a field within an array of structs.
+describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+-- When we refer to the scalar value using dot notation,
+-- we can use arithmetic and comparison operators on it
+-- like any other number.
+select r_name, nation.item.n_name, nation.item.n_nationkey
+from region, region.r_nations as nation
+where nation.item.n_nationkey between 3 and 5
++-------------+-------------+------------------+
+| r_name | item.n_name | item.n_nationkey |
++-------------+-------------+------------------+
+| AMERICA | CANADA | 3 |
+| MIDDLE EAST | EGYPT | 4 |
+| AFRICA | ETHIOPIA | 5 |
++-------------+-------------+------------------+
+</code></pre>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title4" id="operators__comparison_operators">
+
+ <h2 class="title topictitle2" id="ariaid-title4">Comparison Operators</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ Impala supports the familiar comparison operators for checking equality and sort order for the column data types:
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">left_hand_expression</var> <var class="keyword varname">comparison_operator</var> <var class="keyword varname">right_hand_expression</var></code></pre>
+
+ <ul class="ul">
+ <li class="li">
+ <code class="ph codeph">=</code>, <code class="ph codeph">!=</code>, <code class="ph codeph"><></code>: apply to all types.
+ </li>
+
+ <li class="li">
+ <code class="ph codeph"><</code>, <code class="ph codeph"><=</code>, <code class="ph codeph">></code>, <code class="ph codeph">>=</code>: apply to all types; for
+ <code class="ph codeph">BOOLEAN</code>, <code class="ph codeph">TRUE</code> is considered greater than <code class="ph codeph">FALSE</code>.
+ </li>
+ </ul>
+
+ <p class="p">
+ <strong class="ph b">Alternatives:</strong>
+ </p>
+
+ <p class="p">
+ The <code class="ph codeph">IN</code> and <code class="ph codeph">BETWEEN</code> operators provide shorthand notation for expressing combinations of equality,
+ less than, and greater than comparisons with a single operator.
+ </p>
+
+ <p class="p">
+ Because comparing any value to <code class="ph codeph">NULL</code> produces <code class="ph codeph">NULL</code> rather than <code class="ph codeph">TRUE</code> or
+ <code class="ph codeph">FALSE</code>, use the <code class="ph codeph">IS NULL</code> and <code class="ph codeph">IS NOT NULL</code> operators to check if a value is
+ <code class="ph codeph">NULL</code> or not.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+ <p class="p">
+ The following example shows how to do an arithmetic operation using a numeric field of a <code class="ph codeph">STRUCT</code> type that is an
+ item within an <code class="ph codeph">ARRAY</code> column. Once the scalar numeric value <code class="ph codeph">R_NATIONKEY</code> is extracted, it can be
+ used with a comparison operator such as <code class="ph codeph"><</code>:
+ </p>
+
+<pre class="pre codeblock"><code>
+-- The SMALLINT is a field within an array of structs.
+describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+-- When we refer to the scalar value using dot notation,
+-- we can use arithmetic and comparison operators on it
+-- like any other number.
+select r_name, nation.item.n_name, nation.item.n_nationkey
+from region, region.r_nations as nation
+where nation.item.n_nationkey < 5
++-------------+-------------+------------------+
+| r_name | item.n_name | item.n_nationkey |
++-------------+-------------+------------------+
+| AMERICA | CANADA | 3 |
+| AMERICA | BRAZIL | 2 |
+| AMERICA | ARGENTINA | 1 |
+| MIDDLE EAST | EGYPT | 4 |
+| AFRICA | ALGERIA | 0 |
++-------------+-------------+------------------+
+</code></pre>
+
+ </div>
+
+ </article>
+
+
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title5" id="operators__exists">
+
+ <h2 class="title topictitle2" id="ariaid-title5">EXISTS Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+
+
+ The <code class="ph codeph">EXISTS</code> operator tests whether a subquery returns any results. You typically use it to find values from one
+ table that have corresponding values in another table.
+ </p>
+
+ <p class="p">
+ The converse, <code class="ph codeph">NOT EXISTS</code>, helps to find all the values from one table that do not have any corresponding values in
+ another table.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>EXISTS (<var class="keyword varname">subquery</var>)
+NOT EXISTS (<var class="keyword varname">subquery</var>)
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ The subquery can refer to a different table than the outer query block, or the same table. For example, you might use
+ <code class="ph codeph">EXISTS</code> or <code class="ph codeph">NOT EXISTS</code> to check the existence of parent/child relationships between two columns of
+ the same table.
+ </p>
+
+ <p class="p">
+ You can also use operators and function calls within the subquery to test for other kinds of relationships other than strict
+ equality. For example, you might use a call to <code class="ph codeph">COUNT()</code> in the subquery to check whether the number of matching
+ values is higher or lower than some limit. You might call a UDF in the subquery to check whether values in one table matches a
+ hashed representation of those same values in a different table.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">NULL considerations:</strong>
+ </p>
+
+ <p class="p">
+ If the subquery returns any value at all (even <code class="ph codeph">NULL</code>), <code class="ph codeph">EXISTS</code> returns <code class="ph codeph">TRUE</code> and
+ <code class="ph codeph">NOT EXISTS</code> returns false.
+ </p>
+
+ <p class="p">
+ The following example shows how even when the subquery returns only <code class="ph codeph">NULL</code> values, <code class="ph codeph">EXISTS</code> still
+ returns <code class="ph codeph">TRUE</code> and thus matches all the rows from the table in the outer query block.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > create table all_nulls (x int);
+[localhost:21000] > insert into all_nulls values (null), (null), (null);
+[localhost:21000] > select y from t2 where exists (select x from all_nulls);
++---+
+| y |
++---+
+| 2 |
+| 4 |
+| 6 |
++---+
+</code></pre>
+
+ <p class="p">
+ However, if the table in the subquery is empty and so the subquery returns an empty result set, <code class="ph codeph">EXISTS</code> returns
+ <code class="ph codeph">FALSE</code>:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > create table empty (x int);
+[localhost:21000] > select y from t2 where exists (select x from empty);
+[localhost:21000] >
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.0.0</span>
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Restrictions:</strong>
+ </p>
+
+ <p class="p">
+ Correlated subqueries used in <code class="ph codeph">EXISTS</code> and <code class="ph codeph">IN</code> operators cannot include a
+ <code class="ph codeph">LIMIT</code> clause.
+ </p>
+
+ <p class="p">
+ Prior to <span class="keyword">Impala 2.6</span>,
+ the <code class="ph codeph">NOT EXISTS</code> operator required a correlated subquery.
+ In <span class="keyword">Impala 2.6</span> and higher, <code class="ph codeph">NOT EXISTS</code> works with
+ uncorrelated queries also.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+ <div class="p">
+
+
+ The following examples refer to these simple tables containing small sets of integers or strings:
+<pre class="pre codeblock"><code>[localhost:21000] > create table t1 (x int);
+[localhost:21000] > insert into t1 values (1), (2), (3), (4), (5), (6);
+
+[localhost:21000] > create table t2 (y int);
+[localhost:21000] > insert into t2 values (2), (4), (6);
+
+[localhost:21000] > create table t3 (z int);
+[localhost:21000] > insert into t3 values (1), (3), (5);
+
+[localhost:21000] > create table month_names (m string);
+[localhost:21000] > insert into month_names values
+ > ('January'), ('February'), ('March'),
+ > ('April'), ('May'), ('June'), ('July'),
+ > ('August'), ('September'), ('October'),
+ > ('November'), ('December');
+</code></pre>
+ </div>
+
+ <p class="p">
+ The following example shows a correlated subquery that finds all the values in one table that exist in another table. For each value
+ <code class="ph codeph">X</code> from <code class="ph codeph">T1</code>, the query checks if the <code class="ph codeph">Y</code> column of <code class="ph codeph">T2</code> contains an
+ identical value, and the <code class="ph codeph">EXISTS</code> operator returns <code class="ph codeph">TRUE</code> or <code class="ph codeph">FALSE</code> as appropriate in
+ each case.
+ </p>
+
+<pre class="pre codeblock"><code>localhost:21000] > select x from t1 where exists (select y from t2 where t1.x = y);
++---+
+| x |
++---+
+| 2 |
+| 4 |
+| 6 |
++---+
+</code></pre>
+
+ <p class="p">
+ An uncorrelated query is less interesting in this case. Because the subquery always returns <code class="ph codeph">TRUE</code>, all rows from
+ <code class="ph codeph">T1</code> are returned. If the table contents where changed so that the subquery did not match any rows, none of the rows
+ from <code class="ph codeph">T1</code> would be returned.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select x from t1 where exists (select y from t2 where y > 5);
++---+
+| x |
++---+
+| 1 |
+| 2 |
+| 3 |
+| 4 |
+| 5 |
+| 6 |
++---+
+</code></pre>
+
+ <p class="p">
+ The following example shows how an uncorrelated subquery can test for the existence of some condition within a table. By using
+ <code class="ph codeph">LIMIT 1</code> or an aggregate function, the query returns a single result or no result based on whether the subquery
+ matches any rows. Here, we know that <code class="ph codeph">T1</code> and <code class="ph codeph">T2</code> contain some even numbers, but <code class="ph codeph">T3</code>
+ does not.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select "contains an even number" from t1 where exists (select x from t1 where x % 2 = 0) limit 1;
++---------------------------+
+| 'contains an even number' |
++---------------------------+
+| contains an even number |
++---------------------------+
+[localhost:21000] > select "contains an even number" as assertion from t1 where exists (select x from t1 where x % 2 = 0) limit 1;
++-------------------------+
+| assertion |
++-------------------------+
+| contains an even number |
++-------------------------+
+[localhost:21000] > select "contains an even number" as assertion from t2 where exists (select x from t2 where y % 2 = 0) limit 1;
+ERROR: AnalysisException: couldn't resolve column reference: 'x'
+[localhost:21000] > select "contains an even number" as assertion from t2 where exists (select y from t2 where y % 2 = 0) limit 1;
++-------------------------+
+| assertion |
++-------------------------+
+| contains an even number |
++-------------------------+
+[localhost:21000] > select "contains an even number" as assertion from t3 where exists (select z from t3 where z % 2 = 0) limit 1;
+[localhost:21000] >
+</code></pre>
+
+ <p class="p">
+ The following example finds numbers in one table that are 1 greater than numbers from another table. The <code class="ph codeph">EXISTS</code>
+ notation is simpler than an equivalent <code class="ph codeph">CROSS JOIN</code> between the tables. (The example then also illustrates how the
+ same test could be performed using an <code class="ph codeph">IN</code> operator.)
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select x from t1 where exists (select y from t2 where x = y + 1);
++---+
+| x |
++---+
+| 3 |
+| 5 |
++---+
+[localhost:21000] > select x from t1 where x in (select y + 1 from t2);
++---+
+| x |
++---+
+| 3 |
+| 5 |
++---+
+</code></pre>
+
+ <p class="p">
+ The following example finds values from one table that do not exist in another table.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select x from t1 where not exists (select y from t2 where x = y);
++---+
+| x |
++---+
+| 1 |
+| 3 |
+| 5 |
++---+
+</code></pre>
+
+ <p class="p">
+ The following example uses the <code class="ph codeph">NOT EXISTS</code> operator to find all the leaf nodes in tree-structured data. This
+ simplified <span class="q">"tree of life"</span> has multiple levels (class, order, family, and so on), with each item pointing upward through a
+ <code class="ph codeph">PARENT</code> pointer. The example runs an outer query and a subquery on the same table, returning only those items whose
+ <code class="ph codeph">ID</code> value is <em class="ph i">not</em> referenced by the <code class="ph codeph">PARENT</code> of any other item.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > create table tree (id int, parent int, name string);
+[localhost:21000] > insert overwrite tree values
+ > (0, null, "animals"),
+ > (1, 0, "placentals"),
+ > (2, 0, "marsupials"),
+ > (3, 1, "bats"),
+ > (4, 1, "cats"),
+ > (5, 2, "kangaroos"),
+ > (6, 4, "lions"),
+ > (7, 4, "tigers"),
+ > (8, 5, "red kangaroo"),
+ > (9, 2, "wallabies");
+[localhost:21000] > select name as "leaf node" from tree one
+ > where not exists (select parent from tree two where one.id = two.parent);
++--------------+
+| leaf node |
++--------------+
+| bats |
+| lions |
+| tigers |
+| red kangaroo |
+| wallabies |
++--------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Related information:</strong>
+ </p>
+
+ <p class="p">
+ <a class="xref" href="impala_subqueries.html#subqueries">Subqueries in Impala SELECT Statements</a>
+ </p>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title6" id="operators__ilike">
+
+ <h2 class="title topictitle2" id="ariaid-title6">ILIKE Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ A case-insensitive comparison operator for <code class="ph codeph">STRING</code> data, with basic wildcard capability using <code class="ph codeph">_</code> to match a single
+ character and <code class="ph codeph">%</code> to match multiple characters. The argument expression must match the entire string value.
+ Typically, it is more efficient to put any <code class="ph codeph">%</code> wildcard match at the end of the string.
+ </p>
+
+ <p class="p">
+ This operator, available in <span class="keyword">Impala 2.5</span> and higher, is the equivalent of the <code class="ph codeph">LIKE</code> operator,
+ but with case-insensitive comparisons.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">string_expression</var> ILIKE <var class="keyword varname">wildcard_expression</var>
+<var class="keyword varname">string_expression</var> NOT ILIKE <var class="keyword varname">wildcard_expression</var>
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+ <p class="p">
+ In the following examples, strings that are the same except for differences in uppercase
+ and lowercase match successfully with <code class="ph codeph">ILIKE</code>, but do not match
+ with <code class="ph codeph">LIKE</code>:
+ </p>
+
+<pre class="pre codeblock"><code>select 'fooBar' ilike 'FOOBAR';
++-------------------------+
+| 'foobar' ilike 'foobar' |
++-------------------------+
+| true |
++-------------------------+
+
+select 'fooBar' like 'FOOBAR';
++------------------------+
+| 'foobar' like 'foobar' |
++------------------------+
+| false |
++------------------------+
+
+select 'FOOBAR' ilike 'f%';
++---------------------+
+| 'foobar' ilike 'f%' |
++---------------------+
+| true |
++---------------------+
+
+select 'FOOBAR' like 'f%';
++--------------------+
+| 'foobar' like 'f%' |
++--------------------+
+| false |
++--------------------+
+
+select 'ABCXYZ' not ilike 'ab_xyz';
++-----------------------------+
+| not 'abcxyz' ilike 'ab_xyz' |
++-----------------------------+
+| false |
++-----------------------------+
+
+select 'ABCXYZ' not like 'ab_xyz';
++----------------------------+
+| not 'abcxyz' like 'ab_xyz' |
++----------------------------+
+| true |
++----------------------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Related information:</strong>
+ </p>
+
+ <p class="p">
+ For case-sensitive comparisons, see <a class="xref" href="impala_operators.html#like">LIKE Operator</a>.
+ For a more general kind of search operator using regular expressions, see <a class="xref" href="impala_operators.html#regexp">REGEXP Operator</a>
+ or its case-insensitive counterpart <a class="xref" href="impala_operators.html#iregexp">IREGEXP Operator</a>.
+ </p>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title7" id="operators__in">
+
+ <h2 class="title topictitle2" id="ariaid-title7">IN Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+
+
+ The <code class="ph codeph">IN</code> operator compares an argument value to a set of values, and returns <code class="ph codeph">TRUE</code> if the argument
+ matches any value in the set. The <code class="ph codeph">NOT IN</code> operator reverses the comparison, and checks if the argument value is not
+ part of a set of values.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">expression</var> IN (<var class="keyword varname">expression</var> [, <var class="keyword varname">expression</var>])
+<var class="keyword varname">expression</var> IN (<var class="keyword varname">subquery</var>)
+
+<var class="keyword varname">expression</var> NOT IN (<var class="keyword varname">expression</var> [, <var class="keyword varname">expression</var>])
+<var class="keyword varname">expression</var> NOT IN (<var class="keyword varname">subquery</var>)
+</code></pre>
+
+ <p class="p">
+ The left-hand expression and the set of comparison values must be of compatible types.
+ </p>
+
+ <p class="p">
+ The left-hand expression must consist only of a single value, not a tuple. Although the left-hand expression is typically a column
+ name, it could also be some other value. For example, the <code class="ph codeph">WHERE</code> clauses <code class="ph codeph">WHERE id IN (5)</code> and
+ <code class="ph codeph">WHERE 5 IN (id)</code> produce the same results.
+ </p>
+
+ <p class="p">
+ The set of values to check against can be specified as constants, function calls, column names, or other expressions in the query
+ text. The maximum number of expressions in the <code class="ph codeph">IN</code> list is 9999. (The maximum number of elements of
+ a single expression is 10,000 items, and the <code class="ph codeph">IN</code> operator itself counts as one.)
+ </p>
+
+ <p class="p">
+ In Impala 2.0 and higher, the set of values can also be generated by a subquery. <code class="ph codeph">IN</code> can evaluate an unlimited
+ number of results using a subquery.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ Any expression using the <code class="ph codeph">IN</code> operator could be rewritten as a series of equality tests connected with
+ <code class="ph codeph">OR</code>, but the <code class="ph codeph">IN</code> syntax is often clearer, more concise, and easier for Impala to optimize. For
+ example, with partitioned tables, queries frequently use <code class="ph codeph">IN</code> clauses to filter data by comparing the partition key
+ columns to specific values.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">NULL considerations:</strong>
+ </p>
+
+ <p class="p">
+ If there really is a matching non-null value, <code class="ph codeph">IN</code> returns <code class="ph codeph">TRUE</code>:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select 1 in (1,null,2,3);
++----------------------+
+| 1 in (1, null, 2, 3) |
++----------------------+
+| true |
++----------------------+
+[localhost:21000] > select 1 not in (1,null,2,3);
++--------------------------+
+| 1 not in (1, null, 2, 3) |
++--------------------------+
+| false |
++--------------------------+
+</code></pre>
+
+ <p class="p">
+ If the searched value is not found in the comparison values, and the comparison values include <code class="ph codeph">NULL</code>, the result is
+ <code class="ph codeph">NULL</code>:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select 5 in (1,null,2,3);
++----------------------+
+| 5 in (1, null, 2, 3) |
++----------------------+
+| NULL |
++----------------------+
+[localhost:21000] > select 5 not in (1,null,2,3);
++--------------------------+
+| 5 not in (1, null, 2, 3) |
++--------------------------+
+| NULL |
++--------------------------+
+[localhost:21000] > select 1 in (null);
++-------------+
+| 1 in (null) |
++-------------+
+| NULL |
++-------------+
+[localhost:21000] > select 1 not in (null);
++-----------------+
+| 1 not in (null) |
++-----------------+
+| NULL |
++-----------------+
+</code></pre>
+
+ <p class="p">
+ If the left-hand argument is <code class="ph codeph">NULL</code>, <code class="ph codeph">IN</code> always returns <code class="ph codeph">NULL</code>. This rule applies even
+ if the comparison values include <code class="ph codeph">NULL</code>.
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select null in (1,2,3);
++-------------------+
+| null in (1, 2, 3) |
++-------------------+
+| NULL |
++-------------------+
+[localhost:21000] > select null not in (1,2,3);
++-----------------------+
+| null not in (1, 2, 3) |
++-----------------------+
+| NULL |
++-----------------------+
+[localhost:21000] > select null in (null);
++----------------+
+| null in (null) |
++----------------+
+| NULL |
++----------------+
+[localhost:21000] > select null not in (null);
++--------------------+
+| null not in (null) |
++--------------------+
+| NULL |
++--------------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Added in:</strong> Available in earlier Impala releases, but new capabilities were added in
+ <span class="keyword">Impala 2.0.0</span>
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+ <p class="p">
+ The following example shows how to do an arithmetic operation using a numeric field of a <code class="ph codeph">STRUCT</code> type that is an
+ item within an <code class="ph codeph">ARRAY</code> column. Once the scalar numeric value <code class="ph codeph">R_NATIONKEY</code> is extracted, it can be
+ used in an arithmetic expression, such as multiplying by 10:
+ </p>
+
+<pre class="pre codeblock"><code>
+-- The SMALLINT is a field within an array of structs.
+describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+-- When we refer to the scalar value using dot notation,
+-- we can use arithmetic and comparison operators on it
+-- like any other number.
+select r_name, nation.item.n_name, nation.item.n_nationkey
+from region, region.r_nations as nation
+where nation.item.n_nationkey in (1,3,5)
++---------+-------------+------------------+
+| r_name | item.n_name | item.n_nationkey |
++---------+-------------+------------------+
+| AMERICA | CANADA | 3 |
+| AMERICA | ARGENTINA | 1 |
+| AFRICA | ETHIOPIA | 5 |
++---------+-------------+------------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Restrictions:</strong>
+ </p>
+
+ <p class="p">
+ Correlated subqueries used in <code class="ph codeph">EXISTS</code> and <code class="ph codeph">IN</code> operators cannot include a
+ <code class="ph codeph">LIMIT</code> clause.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>-- Using IN is concise and self-documenting.
+SELECT * FROM t1 WHERE c1 IN (1,2,10);
+-- Equivalent to series of = comparisons ORed together.
+SELECT * FROM t1 WHERE c1 = 1 OR c1 = 2 OR c1 = 10;
+
+SELECT c1 AS "starts with vowel" FROM t2 WHERE upper(substr(c1,1,1)) IN ('A','E','I','O','U');
+
+SELECT COUNT(DISTINCT(visitor_id)) FROM web_traffic WHERE month IN ('January','June','July');</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Related information:</strong>
+ </p>
+
+ <p class="p">
+ <a class="xref" href="impala_subqueries.html#subqueries">Subqueries in Impala SELECT Statements</a>
+ </p>
+
+ </div>
+
+ </article>
+
+
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title8" id="operators__iregexp">
+
+ <h2 class="title topictitle2" id="ariaid-title8">IREGEXP Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ Tests whether a value matches a regular expression, using case-insensitive string comparisons.
+ Uses the POSIX regular expression syntax where <code class="ph codeph">^</code> and
+ <code class="ph codeph">$</code> match the beginning and end of the string, <code class="ph codeph">.</code> represents any single character, <code class="ph codeph">*</code>
+ represents a sequence of zero or more items, <code class="ph codeph">+</code> represents a sequence of one or more items, <code class="ph codeph">?</code>
+ produces a non-greedy match, and so on.
+ </p>
+
+ <p class="p">
+ This operator, available in <span class="keyword">Impala 2.5</span> and higher, is the equivalent of the <code class="ph codeph">REGEXP</code> operator,
+ but with case-insensitive comparisons.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">string_expression</var> IREGEXP <var class="keyword varname">regular_expression</var>
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ The regular expression must match the entire value, not just occur somewhere inside it. Use <code class="ph codeph">.*</code> at the beginning,
+ the end, or both if you only need to match characters anywhere in the middle. Thus, the <code class="ph codeph">^</code> and <code class="ph codeph">$</code>
+ atoms are often redundant, although you might already have them in your expression strings that you reuse from elsewhere.
+ </p>
+
+
+
+ <p class="p">
+ The <code class="ph codeph">|</code> symbol is the alternation operator, typically used within <code class="ph codeph">()</code> to match different sequences.
+ The <code class="ph codeph">()</code> groups do not allow backreferences. To retrieve the part of a value matched within a <code class="ph codeph">()</code>
+ section, use the <code class="ph codeph"><a class="xref" href="impala_string_functions.html#string_functions__regexp_extract">regexp_extract()</a></code>
+ built-in function. (Currently, there is not any case-insensitive equivalent for the <code class="ph codeph">regexp_extract()</code> function.)
+ </p>
+
+ <div class="note note note_note"><span class="note__title notetitle">Note:</span>
+ <p class="p">
+ In Impala 1.3.1 and higher, the <code class="ph codeph">REGEXP</code> and <code class="ph codeph">RLIKE</code> operators now match a
+ regular expression string that occurs anywhere inside the target string, the same as if the regular
+ expression was enclosed on each side by <code class="ph codeph">.*</code>. See
+ <a class="xref" href="../shared/../topics/impala_operators.html#regexp">REGEXP Operator</a> for examples. Previously, these operators only
+ succeeded when the regular expression matched the entire target string. This change improves compatibility
+ with the regular expression support for popular database systems. There is no change to the behavior of the
+ <code class="ph codeph">regexp_extract()</code> and <code class="ph codeph">regexp_replace()</code> built-in functions.
+ </p>
+ </div>
+
+ <p class="p">
+ In Impala 2.0 and later, the Impala regular expression syntax conforms to the POSIX Extended Regular
+ Expression syntax used by the Google RE2 library. For details, see
+ <a class="xref" href="https://code.google.com/p/re2/" target="_blank">the RE2 documentation</a>. It
+ has most idioms familiar from regular expressions in Perl, Python, and so on, including
+ <code class="ph codeph">.*?</code> for non-greedy matches.
+ </p>
+
+ <p class="p">
+ In Impala 2.0 and later, a change in the underlying regular expression library could cause changes in the
+ way regular expressions are interpreted by this function. Test any queries that use regular expressions and
+ adjust the expression patterns if necessary. See
+ <a class="xref" href="../shared/../topics/impala_incompatible_changes.html#incompatible_changes_200">Incompatible Changes Introduced in Impala 2.0.0</a> for details.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+ <p class="p">
+ The following examples demonstrate the syntax for the <code class="ph codeph">IREGEXP</code> operator.
+ </p>
+
+<pre class="pre codeblock"><code>select 'abcABCaabbcc' iregexp '^[a-c]+$';
++---------------------------------+
+| 'abcabcaabbcc' iregexp '[a-c]+' |
++---------------------------------+
+| true |
++---------------------------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Related information:</strong>
+ </p>
+
+ <p class="p">
+ <a class="xref" href="impala_operators.html#regexp">REGEXP Operator</a>
+ </p>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="is_distinct_from__is_distinct" id="operators__is_distinct_from">
+
+ <h2 class="title topictitle2" id="is_distinct_from__is_distinct">IS DISTINCT FROM Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+
+
+ The <code class="ph codeph">IS DISTINCT FROM</code> operator, and its converse the <code class="ph codeph">IS NOT DISTINCT FROM</code> operator, test whether or
+ not values are identical. <code class="ph codeph">IS NOT DISTINCT FROM</code> is similar to the <code class="ph codeph">=</code> operator, and <code class="ph codeph">IS
+ DISTINCT FROM</code> is similar to the <code class="ph codeph">!=</code> operator, except that <code class="ph codeph">NULL</code> values are treated as
+ identical. Therefore, <code class="ph codeph">IS NOT DISTINCT FROM</code> returns <code class="ph codeph">true</code> rather than <code class="ph codeph">NULL</code>, and
+ <code class="ph codeph">IS DISTINCT FROM</code> returns <code class="ph codeph">false</code> rather than <code class="ph codeph">NULL</code>, when comparing two
+ <code class="ph codeph">NULL</code> values. If one of the values being compared is <code class="ph codeph">NULL</code> and the other is not, <code class="ph codeph">IS DISTINCT
+ FROM</code> returns <code class="ph codeph">true</code> and <code class="ph codeph">IS NOT DISTINCT FROM</code> returns <code class="ph codeph">false</code>, again instead
+ of returning <code class="ph codeph">NULL</code> in both cases.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">expression1</var> IS DISTINCT FROM <var class="keyword varname">expression2</var>
+
+<var class="keyword varname">expression1</var> IS NOT DISTINCT FROM <var class="keyword varname">expression2</var>
+<var class="keyword varname">expression1</var> <=> <var class="keyword varname">expression2</var>
+</code></pre>
+
+ <p class="p">
+ The operator <code class="ph codeph"><=></code> is an alias for <code class="ph codeph">IS NOT DISTINCT FROM</code>.
+ It is typically used as a <code class="ph codeph">NULL</code>-safe equality operator in join queries.
+ That is, <code class="ph codeph">A <=> B</code> is true if <code class="ph codeph">A</code> equals <code class="ph codeph">B</code>
+ or if both <code class="ph codeph">A</code> and <code class="ph codeph">B</code> are <code class="ph codeph">NULL</code>.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ This operator provides concise notation for comparing two values and always producing a <code class="ph codeph">true</code> or
+ <code class="ph codeph">false</code> result, without treating <code class="ph codeph">NULL</code> as a special case. Otherwise, to unambiguously distinguish
+ between two values requires a compound expression involving <code class="ph codeph">IS [NOT] NULL</code> tests of both operands in addition to the
+ <code class="ph codeph">=</code> or <code class="ph codeph">!=</code> operator.
+ </p>
+
+ <p class="p">
+ The <code class="ph codeph"><=></code> operator, used like an equality operator in a join query,
+ is more efficient than the equivalent clause: <code class="ph codeph">A = B OR (A IS NULL AND B IS NULL)</code>.
+ The <code class="ph codeph"><=></code> operator can use a hash join, while the <code class="ph codeph">OR</code> expression
+ cannot.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+ <p class="p">
+ The following examples show how <code class="ph codeph">IS DISTINCT FROM</code> gives output similar to
+ the <code class="ph codeph">!=</code> operator, and <code class="ph codeph">IS NOT DISTINCT FROM</code> gives output
+ similar to the <code class="ph codeph">=</code> operator. The exception is when the expression involves
+ a <code class="ph codeph">NULL</code> value on one side or both sides, where <code class="ph codeph">!=</code> and
+ <code class="ph codeph">=</code> return <code class="ph codeph">NULL</code> but the <code class="ph codeph">IS [NOT] DISTINCT FROM</code>
+ operators still return <code class="ph codeph">true</code> or <code class="ph codeph">false</code>.
+ </p>
+
+<pre class="pre codeblock"><code>
+select 1 is distinct from 0, 1 != 0;
++----------------------+--------+
+| 1 is distinct from 0 | 1 != 0 |
++----------------------+--------+
+| true | true |
++----------------------+--------+
+
+select 1 is distinct from 1, 1 != 1;
++----------------------+--------+
+| 1 is distinct from 1 | 1 != 1 |
++----------------------+--------+
+| false | false |
++----------------------+--------+
+
+select 1 is distinct from null, 1 != null;
++-------------------------+-----------+
+| 1 is distinct from null | 1 != null |
++-------------------------+-----------+
+| true | NULL |
++-------------------------+-----------+
+
+select null is distinct from null, null != null;
++----------------------------+--------------+
+| null is distinct from null | null != null |
++----------------------------+--------------+
+| false | NULL |
++----------------------------+--------------+
+
+select 1 is not distinct from 0, 1 = 0;
++--------------------------+-------+
+| 1 is not distinct from 0 | 1 = 0 |
++--------------------------+-------+
+| false | false |
++--------------------------+-------+
+
+select 1 is not distinct from 1, 1 = 1;
++--------------------------+-------+
+| 1 is not distinct from 1 | 1 = 1 |
++--------------------------+-------+
+| true | true |
++--------------------------+-------+
+
+select 1 is not distinct from null, 1 = null;
++-----------------------------+----------+
+| 1 is not distinct from null | 1 = null |
++-----------------------------+----------+
+| false | NULL |
++-----------------------------+----------+
+
+select null is not distinct from null, null = null;
++--------------------------------+-------------+
+| null is not distinct from null | null = null |
++--------------------------------+-------------+
+| true | NULL |
++--------------------------------+-------------+
+</code></pre>
+
+ <p class="p">
+ The following example shows how <code class="ph codeph">IS DISTINCT FROM</code> considers
+ <code class="ph codeph">CHAR</code> values to be the same (not distinct from each other)
+ if they only differ in the number of trailing spaces. Therefore, sometimes
+ the result of an <code class="ph codeph">IS [NOT] DISTINCT FROM</code> operator differs
+ depending on whether the values are <code class="ph codeph">STRING</code>/<code class="ph codeph">VARCHAR</code>
+ or <code class="ph codeph">CHAR</code>.
+ </p>
+
+<pre class="pre codeblock"><code>
+select
+ 'x' is distinct from 'x ' as string_with_trailing_spaces,
+ cast('x' as char(5)) is distinct from cast('x ' as char(5)) as char_with_trailing_spaces;
++-----------------------------+---------------------------+
+| string_with_trailing_spaces | char_with_trailing_spaces |
++-----------------------------+---------------------------+
+| true | false |
++-----------------------------+---------------------------+
+</code></pre>
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title10" id="operators__is_null">
+
+ <h2 class="title topictitle2" id="ariaid-title10">IS NULL Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+
+
+
+
+ The <code class="ph codeph">IS NULL</code> operator, and its converse the <code class="ph codeph">IS NOT NULL</code> operator, test whether a specified value is
+ <code class="ph codeph"><a class="xref" href="impala_literals.html#null">NULL</a></code>. Because using <code class="ph codeph">NULL</code> with any of the other
+ comparison operators such as <code class="ph codeph">=</code> or <code class="ph codeph">!=</code> also returns <code class="ph codeph">NULL</code> rather than
+ <code class="ph codeph">TRUE</code> or <code class="ph codeph">FALSE</code>, you use a special-purpose comparison operator to check for this special condition.
+ </p>
+
+ <p class="p">
+ In <span class="keyword">Impala 2.11</span> and higher, you can use
+ the operators <code class="ph codeph">IS UNKNOWN</code> and
+ <code class="ph codeph">IS NOT UNKNOWN</code> as synonyms for
+ <code class="ph codeph">IS NULL</code> and <code class="ph codeph">IS NOT NULL</code>,
+ respectively.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">expression</var> IS NULL
+<var class="keyword varname">expression</var> IS NOT NULL
+
+<span class="ph"><var class="keyword varname">expression</var> IS UNKNOWN</span>
+<span class="ph"><var class="keyword varname">expression</var> IS NOT UNKNOWN</span>
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ In many cases, <code class="ph codeph">NULL</code> values indicate some incorrect or incomplete processing during data ingestion or conversion.
+ You might check whether any values in a column are <code class="ph codeph">NULL</code>, and if so take some followup action to fill them in.
+ </p>
+
+ <p class="p">
+ With sparse data, often represented in <span class="q">"wide"</span> tables, it is common for most values to be <code class="ph codeph">NULL</code> with only an
+ occasional non-<code class="ph codeph">NULL</code> value. In those cases, you can use the <code class="ph codeph">IS NOT NULL</code> operator to identify the
+ rows containing any data at all for a particular column, regardless of the actual value.
+ </p>
+
+ <p class="p">
+ With a well-designed database schema, effective use of <code class="ph codeph">NULL</code> values and <code class="ph codeph">IS NULL</code> and <code class="ph codeph">IS NOT
+ NULL</code> operators can save having to design custom logic around special values such as 0, -1, <code class="ph codeph">'N/A'</code>, empty
+ string, and so on. <code class="ph codeph">NULL</code> lets you distinguish between a value that is known to be 0, false, or empty, and a truly
+ unknown value.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ The <code class="ph codeph">IS [NOT] UNKNOWN</code> operator, as with the <code class="ph codeph">IS [NOT] NULL</code>
+ operator, is not applicable to complex type columns (<code class="ph codeph">STRUCT</code>,
+ <code class="ph codeph">ARRAY</code>, or <code class="ph codeph">MAP</code>). Using a complex type column with this
+ operator causes a query error.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>-- If this value is non-zero, something is wrong.
+select count(*) from employees where employee_id is null;
+
+-- With data from disparate sources, some fields might be blank.
+-- Not necessarily an error condition.
+select count(*) from census where household_income is null;
+
+-- Sometimes we expect fields to be null, and followup action
+-- is needed when they are not.
+select count(*) from web_traffic where weird_http_code is not null;</code></pre>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title11" id="operators__is_true">
+
+ <h2 class="title topictitle2" id="ariaid-title11">IS TRUE Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+
+
+
+ This variation of the <code class="ph codeph">IS</code> operator tests for truth
+ or falsity, with right-hand arguments <code class="ph codeph">[NOT] TRUE</code>,
+ <code class="ph codeph">[NOT] FALSE</code>, and <code class="ph codeph">[NOT] UNKNOWN</code>.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">expression</var> IS TRUE
+<var class="keyword varname">expression</var> IS NOT TRUE
+
+<var class="keyword varname">expression</var> IS FALSE
+<var class="keyword varname">expression</var> IS NOT FALSE
+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Usage notes:</strong>
+ </p>
+
+ <p class="p">
+ This <code class="ph codeph">IS TRUE</code> and <code class="ph codeph">IS FALSE</code> forms are
+ similar to doing equality comparisons with the Boolean values
+ <code class="ph codeph">TRUE</code> and <code class="ph codeph">FALSE</code>, except that
+ <code class="ph codeph">IS TRUE</code> and <code class="ph codeph">IS FALSE</code>
+ always return either <code class="ph codeph">TRUE</code> or <code class="ph codeph">FALSE</code>,
+ even if the left-hand side expression returns <code class="ph codeph">NULL</code>
+ </p>
+
+ <p class="p">
+ These operators let you simplify Boolean comparisons that must also
+ check for <code class="ph codeph">NULL</code>, for example
+ <code class="ph codeph">X != 10 AND X IS NOT NULL</code> is equivalent to
+ <code class="ph codeph">(X != 10) IS TRUE</code>.
+ </p>
+
+ <p class="p">
+ In <span class="keyword">Impala 2.11</span> and higher, you can use
+ the operators <code class="ph codeph">IS [NOT] TRUE</code> and
+ <code class="ph codeph">IS [NOT] FALSE</code> as equivalents for the built-in
+ functions <code class="ph codeph">istrue()</code>, <code class="ph codeph">isnottrue()</code>,
+ <code class="ph codeph">isfalse()</code>, and <code class="ph codeph">isnotfalse()</code>.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ The <code class="ph codeph">IS [NOT] TRUE</code> and <code class="ph codeph">IS [NOT] FALSE</code> operators are not
+ applicable to complex type columns (<code class="ph codeph">STRUCT</code>, <code class="ph codeph">ARRAY</code>, or
+ <code class="ph codeph">MAP</code>). Using a complex type column with these operators causes a query error.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.11.0</span>
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>
+select assertion, b, b is true, b is false, b is unknown
+ from boolean_test;
++-------------+-------+-----------+------------+-----------+
+| assertion | b | istrue(b) | isfalse(b) | b is null |
++-------------+-------+-----------+------------+-----------+
+| 2 + 2 = 4 | true | true | false | false |
+| 2 + 2 = 5 | false | false | true | false |
+| 1 = null | NULL | false | false | true |
+| null = null | NULL | false | false | true |
++-------------+-------+-----------+------------+-----------+
+</code></pre>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title12" id="operators__like">
+
+ <h2 class="title topictitle2" id="ariaid-title12">LIKE Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ A comparison operator for <code class="ph codeph">STRING</code> data, with basic wildcard capability using the underscore
+ (<code class="ph codeph">_</code>) to match a single character and the percent sign (<code class="ph codeph">%</code>) to match multiple
+ characters. The argument expression must match the entire string value.
+ Typically, it is more efficient to put any <code class="ph codeph">%</code> wildcard match at the end of the string.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">string_expression</var> LIKE <var class="keyword varname">wildcard_expression</var>
+<var class="keyword varname">string_expression</var> NOT LIKE <var class="keyword varname">wildcard_expression</var>
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+<pre class="pre codeblock"><code>select distinct c_last_name from customer where c_last_name like 'Mc%' or c_last_name like 'Mac%';
+select count(c_last_name) from customer where c_last_name like 'M%';
+select c_email_address from customer where c_email_address like '%.edu';
+
+-- We can find 4-letter names beginning with 'M' by calling functions...
+select distinct c_last_name from customer where length(c_last_name) = 4 and substr(c_last_name,1,1) = 'M';
+-- ...or in a more readable way by matching M followed by exactly 3 characters.
+select distinct c_last_name from customer where c_last_name like 'M___';</code></pre>
+
+ <p class="p">
+ For case-insensitive comparisons, see <a class="xref" href="impala_operators.html#ilike">ILIKE Operator</a>.
+ For a more general kind of search operator using regular expressions, see <a class="xref" href="impala_operators.html#regexp">REGEXP Operator</a>
+ or its case-insensitive counterpart <a class="xref" href="impala_operators.html#iregexp">IREGEXP Operator</a>.
+ </p>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title13" id="operators__logical_operators">
+
+ <h2 class="title topictitle2" id="ariaid-title13">Logical Operators</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ Logical operators return a <code class="ph codeph">BOOLEAN</code> value, based on a binary or unary logical operation between arguments that are
+ also Booleans. Typically, the argument expressions use <a class="xref" href="impala_operators.html#comparison_operators">comparison
+ operators</a>.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">boolean_expression</var> <var class="keyword varname">binary_logical_operator</var> <var class="keyword varname">boolean_expression</var>
+<var class="keyword varname">unary_logical_operator</var> <var class="keyword varname">boolean_expression</var>
+</code></pre>
+
+ <p class="p">
+ The Impala logical operators are:
+ </p>
+
+ <ul class="ul">
+ <li class="li">
+ <code class="ph codeph">AND</code>: A binary operator that returns <code class="ph codeph">true</code> if its left-hand and right-hand arguments both evaluate
+ to <code class="ph codeph">true</code>, <code class="ph codeph">NULL</code> if either argument is <code class="ph codeph">NULL</code>, and <code class="ph codeph">false</code> otherwise.
+ </li>
+
+ <li class="li">
+ <code class="ph codeph">OR</code>: A binary operator that returns <code class="ph codeph">true</code> if either of its left-hand and right-hand arguments
+ evaluate to <code class="ph codeph">true</code>, <code class="ph codeph">NULL</code> if one argument is <code class="ph codeph">NULL</code> and the other is either
+ <code class="ph codeph">NULL</code> or <code class="ph codeph">false</code>, and <code class="ph codeph">false</code> otherwise.
+ </li>
+
+ <li class="li">
+ <code class="ph codeph">NOT</code>: A unary operator that flips the state of a Boolean expression from <code class="ph codeph">true</code> to
+ <code class="ph codeph">false</code>, or <code class="ph codeph">false</code> to <code class="ph codeph">true</code>. If the argument expression is <code class="ph codeph">NULL</code>,
+ the result remains <code class="ph codeph">NULL</code>. (When <code class="ph codeph">NOT</code> is used this way as a unary logical operator, it works
+ differently than the <code class="ph codeph">IS NOT NULL</code> comparison operator, which returns <code class="ph codeph">true</code> when applied to a
+ <code class="ph codeph">NULL</code>.)
+ </li>
+ </ul>
+
+ <p class="p">
+ <strong class="ph b">Complex type considerations:</strong>
+ </p>
+
+ <p class="p">
+ You cannot refer to a column with a complex data type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, or <code class="ph codeph">MAP</code>
+ directly in an operator. You can apply operators only to scalar values that make up a complex type
+ (the fields of a <code class="ph codeph">STRUCT</code>, the items of an <code class="ph codeph">ARRAY</code>,
+ or the key or value portion of a <code class="ph codeph">MAP</code>) as part of a join query that refers to
+ the scalar value using the appropriate dot notation or <code class="ph codeph">ITEM</code>, <code class="ph codeph">KEY</code>, or <code class="ph codeph">VALUE</code>
+ pseudocolumn names.
+ </p>
+
+ <p class="p">
+ The following example shows how to do an arithmetic operation using a numeric field of a <code class="ph codeph">STRUCT</code> type that is an
+ item within an <code class="ph codeph">ARRAY</code> column. Once the scalar numeric value <code class="ph codeph">R_NATIONKEY</code> is extracted, it can be
+ used in an arithmetic expression, such as multiplying by 10:
+ </p>
+
+<pre class="pre codeblock"><code>
+-- The SMALLINT is a field within an array of structs.
+describe region;
++-------------+-------------------------+---------+
+| name | type | comment |
++-------------+-------------------------+---------+
+| r_regionkey | smallint | |
+| r_name | string | |
+| r_comment | string | |
+| r_nations | array<struct< | |
+| | n_nationkey:smallint, | |
+| | n_name:string, | |
+| | n_comment:string | |
+| | >> | |
++-------------+-------------------------+---------+
+
+-- When we refer to the scalar value using dot notation,
+-- we can use arithmetic and comparison operators on it
+-- like any other number.
+select r_name, nation.item.n_name, nation.item.n_nationkey
+ from region, region.r_nations as nation
+where
+ nation.item.n_nationkey between 3 and 5
+ or nation.item.n_nationkey < 15;
++-------------+----------------+------------------+
+| r_name | item.n_name | item.n_nationkey |
++-------------+----------------+------------------+
+| EUROPE | UNITED KINGDOM | 23 |
+| EUROPE | RUSSIA | 22 |
+| EUROPE | ROMANIA | 19 |
+| ASIA | VIETNAM | 21 |
+| ASIA | CHINA | 18 |
+| AMERICA | UNITED STATES | 24 |
+| AMERICA | PERU | 17 |
+| AMERICA | CANADA | 3 |
+| MIDDLE EAST | SAUDI ARABIA | 20 |
+| MIDDLE EAST | EGYPT | 4 |
+| AFRICA | MOZAMBIQUE | 16 |
+| AFRICA | ETHIOPIA | 5 |
++-------------+----------------+------------------+
+</code></pre>
+
+ <p class="p">
+ <strong class="ph b">Examples:</strong>
+ </p>
+
+ <p class="p">
+ These examples demonstrate the <code class="ph codeph">AND</code> operator:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select true and true;
++---------------+
+| true and true |
++---------------+
+| true |
++---------------+
+[localhost:21000] > select true and false;
++----------------+
+| true and false |
++----------------+
+| false |
++----------------+
+[localhost:21000] > select false and false;
++-----------------+
+| false and false |
++-----------------+
+| false |
++-----------------+
+[localhost:21000] > select true and null;
++---------------+
+| true and null |
++---------------+
+| NULL |
++---------------+
+[localhost:21000] > select (10 > 2) and (6 != 9);
++-----------------------+
+| (10 > 2) and (6 != 9) |
++-----------------------+
+| true |
++-----------------------+
+</code></pre>
+
+ <p class="p">
+ These examples demonstrate the <code class="ph codeph">OR</code> operator:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select true or true;
++--------------+
+| true or true |
++--------------+
+| true |
++--------------+
+[localhost:21000] > select true or false;
++---------------+
+| true or false |
++---------------+
+| true |
++---------------+
+[localhost:21000] > select false or false;
++----------------+
+| false or false |
++----------------+
+| false |
++----------------+
+[localhost:21000] > select true or null;
++--------------+
+| true or null |
++--------------+
+| true |
++--------------+
+[localhost:21000] > select null or true;
++--------------+
+| null or true |
++--------------+
+| true |
++--------------+
+[localhost:21000] > select false or null;
++---------------+
+| false or null |
++---------------+
+| NULL |
++---------------+
+[localhost:21000] > select (1 = 1) or ('hello' = 'world');
++--------------------------------+
+| (1 = 1) or ('hello' = 'world') |
++--------------------------------+
+| true |
++--------------------------------+
+[localhost:21000] > select (2 + 2 != 4) or (-1 > 0);
++--------------------------+
+| (2 + 2 != 4) or (-1 > 0) |
++--------------------------+
+| false |
++--------------------------+
+</code></pre>
+
+ <p class="p">
+ These examples demonstrate the <code class="ph codeph">NOT</code> operator:
+ </p>
+
+<pre class="pre codeblock"><code>[localhost:21000] > select not true;
++----------+
+| not true |
++----------+
+| false |
++----------+
+[localhost:21000] > select not false;
++-----------+
+| not false |
++-----------+
+| true |
++-----------+
+[localhost:21000] > select not null;
++----------+
+| not null |
++----------+
+| NULL |
++----------+
+[localhost:21000] > select not (1=1);
++-------------+
+| not (1 = 1) |
++-------------+
+| false |
++-------------+
+</code></pre>
+
+ </div>
+
+ </article>
+
+ <article class="topic concept nested1" aria-labelledby="ariaid-title14" id="operators__regexp">
+
+ <h2 class="title topictitle2" id="ariaid-title14">REGEXP Operator</h2>
+
+ <div class="body conbody">
+
+ <p class="p">
+
+ Tests whether a value matches a regular expression. Uses the POSIX regular expression syntax where <code class="ph codeph">^</code> and
+ <code class="ph codeph">$</code> match the beginning and end of the string, <code class="ph codeph">.</code> represents any single character, <code class="ph codeph">*</code>
+ represents a sequence of zero or more items, <code class="ph codeph">+</code> represents a sequence of one or more items, <code class="ph codeph">?</code>
+ produces a non-greedy match, and so on.
+ </p>
+
+ <p class="p">
+ <strong class="ph b">Syntax:</strong>
+ </p>
+
+<pre class="pre codeblock"><code><var class="keyword varname">string_ex
<TRUNCATED>