Learning Snowflake SQL and Scripting

Chapter 8. Subqueries

Subqueries are a powerful tool that can be used in select, update, insert, delete, and merge statements. This chapter will explore the different types of subqueries, and the different ways they can interact with your SQL statements.

Subqueries Defined

A subquery is a query that is contained within another SQL statement (referred to as the containing statement or containing query for the rest of this chapter). Subqueries are always surrounded by parentheses and generally run prior to the containing statement. Like any query, subqueries return a result set that can consist of a single row or multiple rows, and a single column or multiple columns. The type of result set returned by the subquery determines which  operators the containing statement may use to interact with the data returned by the subquery.

Here’s a simple example to get started:

PUBLIC>select n_nationkey, n_name from nation
       where n_regionkey = 
        (select r_regionkey from region where r_name = 'ASIA');
+-------------+-----------+
| N_NATIONKEY | N_NAME    |
|-------------+-----------|
|           8 | INDIA     |
|           9 | INDONESIA |
|          12 | JAPAN     |
|          18 | CHINA     |
|          21 | VIETNAM   |
+-------------+-----------+

In this statement, the containing query is retrieving data from the Nation table, and the subquery returns the regionkey value for Asia from the Region table. The subquery runs first and returns the value 2, and the containing query then returns ...