| <?xml version="1.0" encoding="UTF-8"?> |
| <!DOCTYPE html |
| PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> |
| <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> |
| <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> |
| |
| <meta name="copyright" content="(C) Copyright 2025" /> |
| <meta name="DC.rights.owner" content="(C) Copyright 2025" /> |
| <meta name="DC.Type" content="concept" /> |
| <meta name="DC.Title" content="Subqueries in Impala SELECT Statements" /> |
| <meta name="DC.Relation" scheme="URI" content="../topics/impala_select.html" /> |
| <meta name="prodname" content="Impala" /> |
| <meta name="prodname" content="Impala" /> |
| <meta name="version" content="Impala 3.4.x" /> |
| <meta name="version" content="Impala 3.4.x" /> |
| <meta name="DC.Format" content="XHTML" /> |
| <meta name="DC.Identifier" content="subqueries" /> |
| <link rel="stylesheet" type="text/css" href="../commonltr.css" /> |
| <title>Subqueries in Impala SELECT Statements</title> |
| </head> |
| <body id="subqueries"> |
| |
| |
| <h1 class="title topictitle1" id="ariaid-title1">Subqueries in Impala SELECT Statements</h1> |
| |
| |
| |
| |
| <div class="body conbody"> |
| |
| <p class="p"> |
| |
| A <dfn class="term">subquery</dfn> is a query that is nested within another query. Subqueries let queries on one table |
| dynamically adapt based on the contents of another table. This technique provides great flexibility and |
| expressive power for SQL queries. |
| </p> |
| |
| |
| <p class="p"> |
| A subquery can return a result set for use in the <code class="ph codeph">FROM</code> or <code class="ph codeph">WITH</code> clauses, or |
| with operators such as <code class="ph codeph">IN</code> or <code class="ph codeph">EXISTS</code>. |
| </p> |
| |
| |
| <p class="p"> |
| A <dfn class="term">scalar subquery</dfn> produces a result set with a single row containing a single column, typically |
| produced by an aggregation function such as <code class="ph codeph">MAX()</code> or <code class="ph codeph">SUM()</code>. This single |
| result value can be substituted in scalar contexts such as arguments to comparison operators. If the result |
| set is empty, the value of the scalar subquery is <code class="ph codeph">NULL</code>. For example, the following query |
| finds the maximum value of <code class="ph codeph">T2.Y</code> and then substitutes that value into the |
| <code class="ph codeph">WHERE</code> clause of the outer block that queries <code class="ph codeph">T1</code>: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT x FROM t1 WHERE x > (SELECT MAX(y) FROM t2); |
| </code></pre> |
| |
| <p class="p"> |
| <dfn class="term">Uncorrelated subqueries</dfn> do not refer to any tables from the outer block of the query. The same |
| value or set of values produced by the subquery is used when evaluating each row from the outer query block. |
| In this example, the subquery returns an arbitrary number of values from <code class="ph codeph">T2.Y</code>, and each |
| value of <code class="ph codeph">T1.X</code> is tested for membership in that same set of values: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT x FROM t1 WHERE x IN (SELECT y FROM t2); |
| </code></pre> |
| |
| <p class="p"> |
| <dfn class="term">Correlated subqueries</dfn> compare one or more values from the outer query block to values referenced |
| in the <code class="ph codeph">WHERE</code> clause of the subquery. Each row evaluated by the outer <code class="ph codeph">WHERE</code> |
| clause can be evaluated using a different set of values. These kinds of subqueries are restricted in the |
| kinds of comparisons they can do between columns of the inner and outer tables. (See the following |
| <strong class="ph b">Restrictions</strong> item.) |
| </p> |
| |
| |
| <p class="p"> |
| For example, the following query finds all the employees with salaries that are higher than average for their |
| department. The subquery potentially computes a different <code class="ph codeph">AVG()</code> value for each employee. |
| </p> |
| |
| |
| |
| |
| <pre class="pre codeblock"><code>SELECT employee_name, employee_id FROM employees one WHERE |
| salary > (SELECT avg(salary) FROM employees two WHERE one.dept_id = two.dept_id); |
| </code></pre> |
| |
| <p class="p"> |
| <strong class="ph b">Syntax:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Subquery in the <code class="ph codeph">FROM</code> clause:</strong> |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT <var class="keyword varname">select_list</var> FROM <var class="keyword varname">table_ref</var> [, <var class="keyword varname">table_ref</var> ...] |
| |
| <var class="keyword varname">table_ref</var> ::= <var class="keyword varname">table_name</var> | (<var class="keyword varname">select_statement</var>) |
| </code></pre> |
| |
| <p class="p"> |
| <strong class="ph b">Subqueries in <code class="ph codeph">WHERE</code> clause:</strong> |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>WHERE <var class="keyword varname">value</var> <var class="keyword varname">comparison_operator</var> (<var class="keyword varname">scalar_select_statement</var>) |
| WHERE <var class="keyword varname">value</var> [NOT] IN (<var class="keyword varname">select_statement</var>) |
| WHERE [NOT] EXISTS (<var class="keyword varname">correlated_select_statement</var>) |
| WHERE NOT EXISTS (<var class="keyword varname">correlated_select_statement</var>) |
| </code></pre> |
| |
| <p class="p"> |
| <code class="ph codeph">comparison_operator</code> is a numeric comparison such as <code class="ph codeph">=</code>, |
| <code class="ph codeph"><=</code>, <code class="ph codeph">!=</code>, and so on, or a string comparison operator such as |
| <code class="ph codeph">LIKE</code> or <code class="ph codeph">REGEXP</code>. |
| </p> |
| |
| |
| <p class="p"> |
| Although you can use non-equality comparison operators such as <code class="ph codeph"><</code> or |
| <code class="ph codeph">>=</code>, the subquery must include at least one equality comparison between the columns of the |
| inner and outer query blocks. |
| </p> |
| |
| |
| <p class="p"> |
| All syntax is available for both correlated and uncorrelated queries, except that the <code class="ph codeph">NOT |
| EXISTS</code> clause cannot be used with an uncorrelated subquery. |
| </p> |
| |
| |
| <p class="p"> |
| Impala subqueries can be nested arbitrarily deep. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Standards compliance:</strong> Introduced in |
| <a class="xref" href="http://en.wikipedia.org/wiki/SQL:1999" target="_blank">SQL:1999</a>. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Examples:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| This example illustrates how subqueries can be used in the <code class="ph codeph">FROM</code> clause to organize the table |
| names, column names, and column values by producing intermediate result sets, especially for join queries. |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT avg(t1.x), max(t2.y) FROM |
| (SELECT id, cast(a AS DECIMAL(10,5)) AS x FROM raw_data WHERE a BETWEEN 0 AND 100) AS t1 |
| JOIN |
| (SELECT id, length(s) AS y FROM raw_data WHERE s LIKE 'A%') AS t2; |
| USING (id); |
| </code></pre> |
| |
| <p class="p"> |
| These examples show how a query can test for the existence of values in a separate table using the |
| <code class="ph codeph">EXISTS()</code> operator with a subquery. |
| |
| </p> |
| |
| |
| <p class="p"> |
| The following examples show how a value can be compared against a set of values returned by a subquery. |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT count(x) FROM t1 WHERE EXISTS(SELECT 1 FROM t2 WHERE t1.x = t2.y * 10); |
| |
| SELECT x FROM t1 WHERE x IN (SELECT y FROM t2 WHERE state = 'CA'); |
| </code></pre> |
| |
| <p class="p"> |
| The following examples demonstrate scalar subqueries. When a subquery is known to return a single value, you |
| can substitute it where you would normally put a constant value. |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>SELECT x FROM t1 WHERE y = (SELECT max(z) FROM t2); |
| SELECT x FROM t1 WHERE y > (SELECT count(z) FROM t2); |
| </code></pre> |
| |
| |
| |
| |
| |
| |
| |
| <p class="p"> |
| <strong class="ph b">Usage notes:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| If the same table is referenced in both the outer and inner query blocks, construct a table alias in the |
| outer query block and use a fully qualified name to distinguish the inner and outer table references: |
| </p> |
| |
| |
| |
| |
| <pre class="pre codeblock"><code>SELECT * FROM t1 one WHERE id IN (SELECT parent FROM t1 two WHERE t1.parent = t2.id); |
| </code></pre> |
| |
| <p class="p"> |
| The <code class="ph codeph">STRAIGHT_JOIN</code> hint affects the join order of table references in |
| the query block containing the hint. It does not affect the join order of nested |
| queries, such as views, inline views, or <code class="ph codeph">WHERE</code>-clause subqueries. To |
| use this hint for performance tuning of complex queries, apply the hint to all query |
| blocks that need a fixed join order. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Internal details:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| Internally, subqueries involving <code class="ph codeph">IN</code>, <code class="ph codeph">NOT IN</code>, <code class="ph codeph">EXISTS</code>, or |
| <code class="ph codeph">NOT EXISTS</code> clauses are rewritten into join queries. Depending on the syntax, the subquery |
| might be rewritten to an outer join, semi join, cross join, or anti join. |
| </p> |
| |
| |
| <p class="p"> |
| A query is processed differently depending on whether the subquery calls any aggregation functions. There are |
| correlated and uncorrelated forms, with and without calls to aggregation functions. Each of these four |
| categories is rewritten differently. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Column statistics considerations:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| Because queries that include correlated and uncorrelated subqueries in the <code class="ph codeph">WHERE</code> clause are |
| written into join queries, to achieve best performance, follow the same guidelines for running the |
| <code class="ph codeph">COMPUTE STATS</code> statement as you do for tables involved in regular join queries. Run the |
| <code class="ph codeph">COMPUTE STATS</code> statement for each associated tables after loading or substantially changing |
| the data in that table. See <a class="xref" href="impala_perf_stats.html#perf_stats">Table and Column Statistics</a> for details. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Added in:</strong> Subqueries are substantially enhanced starting in Impala 2.0. Now, |
| they can be used in the <code class="ph codeph">WHERE</code> clause, in combination with clauses such as |
| <code class="ph codeph">EXISTS</code> and <code class="ph codeph">IN</code>, rather than just in the <code class="ph codeph">FROM</code> clause. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Restrictions:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| The initial Impala support for nested subqueries addresses the most common use cases. Some restrictions |
| remain: |
| </p> |
| |
| |
| <ul class="ul"> |
| <li class="li"> |
| <p class="p"> |
| Although you can use subqueries in a query involving <code class="ph codeph">UNION</code> or <code class="ph codeph">UNION ALL</code> |
| in Impala 2.1.0 and higher, currently you cannot construct a union of two subqueries (for example, in the |
| argument of an <code class="ph codeph">IN</code> or <code class="ph codeph">EXISTS</code> operator). |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| Subqueries returning scalar values cannot be used with the operators <code class="ph codeph">ANY</code> or |
| <code class="ph codeph">ALL</code>. (Impala does not currently have a <code class="ph codeph">SOME</code> operator, but if it did, |
| the same restriction would apply.) |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| For the <code class="ph codeph">EXISTS</code> and <code class="ph codeph">NOT EXISTS</code> clauses, any subquery comparing values |
| from the outer query block to another table must use at least one equality comparison, not exclusively |
| other kinds of comparisons such as less than, greater than, <code class="ph codeph">BETWEEN</code>, or |
| <code class="ph codeph">!=</code>. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| |
| <p class="p"> |
| Currently, a scalar subquery cannot be used as the first or second argument to the |
| <code class="ph codeph">BETWEEN</code> operator. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| A subquery cannot be used inside an <code class="ph codeph">OR</code> conjunction. Expressions inside a subquery, for |
| example in the <code class="ph codeph">WHERE</code> clause, can use <code class="ph codeph">OR</code> conjunctions; the restriction |
| only applies to parts of the query <span class="q">"above"</span> the subquery. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| Scalar subqueries are only supported in numeric contexts. You cannot use a scalar subquery as an argument |
| to the <code class="ph codeph">LIKE</code>, <code class="ph codeph">REGEXP</code>, or <code class="ph codeph">RLIKE</code> operators, or compare it |
| to a value of a non-numeric type such as <code class="ph codeph">TIMESTAMP</code> or <code class="ph codeph">BOOLEAN</code>. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| |
| You cannot use subqueries with the <code class="ph codeph">CASE</code> function to generate the comparison value, the |
| values to be compared against, or the return value. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| A subquery is not allowed in the filter condition for the <code class="ph codeph">HAVING</code> clause. (Strictly |
| speaking, a subquery cannot appear anywhere outside the <code class="ph codeph">WITH</code>, <code class="ph codeph">FROM</code>, and |
| <code class="ph codeph">WHERE</code> clauses.) |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| You must use a fully qualified name |
| (<code class="ph codeph"><var class="keyword varname">table_name</var>.<var class="keyword varname">column_name</var></code> or |
| <code class="ph codeph"><var class="keyword varname">database_name</var>.<var class="keyword varname">table_name</var>.<var class="keyword varname">column_name</var></code>) |
| when referring to any column from the outer query block within a subquery. |
| </p> |
| |
| </li> |
| |
| <li class="li"> |
| <p class="p"> |
| The <code class="ph codeph">TABLESAMPLE</code> clause of the <code class="ph codeph">SELECT</code> statement does |
| not apply to a table reference derived from a view, a subquery, or anything other than a |
| real base table. This clause only works for tables backed by HDFS or HDFS-like data |
| files, therefore it does not apply to Kudu or HBase tables. |
| </p> |
| |
| </li> |
| |
| </ul> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Complex type considerations:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| For the complex types (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>, and <code class="ph codeph">MAP</code>) |
| available in <span class="keyword">Impala 2.3</span> and higher, the join queries that <span class="q">"unpack"</span> complex type |
| columns often use correlated subqueries in the <code class="ph codeph">FROM</code> clause. |
| For example, if the first table in the join clause is <code class="ph codeph">CUSTOMER</code>, the second |
| join clause might have a subquery that selects from the column <code class="ph codeph">CUSTOMER.C_ORDERS</code>, |
| which is an <code class="ph codeph">ARRAY</code>. The subquery re-evaluates the <code class="ph codeph">ARRAY</code> elements |
| corresponding to each row from the <code class="ph codeph">CUSTOMER</code> table. |
| See <a class="xref" href="impala_complex_types.html#complex_types">Complex Types (Impala 2.3 or higher only)</a> for details and examples of |
| using subqueries with complex types. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Related information:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| <a class="xref" href="impala_operators.html#exists">EXISTS Operator</a>, <a class="xref" href="impala_operators.html#in">IN Operator</a> |
| </p> |
| |
| </div> |
| |
| <div class="related-links"> |
| <div class="familylinks"> |
| <div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_select.html">SELECT Statement</a></div> |
| </div> |
| </div></body> |
| </html> |
