Snowflake Type of Subqueries and Examples

In general, subquery in a database is a nested query block in a query statement. It is simply a SELECT expression enclosed in a parenthesis. The Subquery may return zero to one or more values to its upper or parent SELECT statements. In this article, we will check Snowflake type of subqueries with an examples.

Snowflake Subqueries

A subquery in Snowflake is a nested select statement, that return zero or more records to is upper select statement.

The outer SELECT statement that contains subquery is sometimes referred to as a super or parent query. You can use subqueries to calculate or derive the values. The derived value is later consumed by the parent or outer query.

Snowflake supports sub-query block in any of the following SQL statements:

SELECT
INSERT
DELETE
UPDATE
CREATE TABLE AS
INSERT INTO

Snowflake Type of Subqueries

There are various types of subqueries that you can use in your SQL query. You can use these sub-queries to simplify the complex logic.

Following are the different types of subqueries.

Row Subquery
Scalar Subquery
Table Subquery
Correlated subquery

Now, let us check these subqueries in brief with an examples.

Snowflake Row Subquery

A single row subquery returns at most one row to the outer SQL statement. The returned value will be associated with all the columns that are mentioned in the SELECT statement.

Snowflake also allows you to use row subquery in a clause such as WHERE, or a FROM.

For example, following SQL query uses a single row subquery in a SELECT statement:

> select *,(select '1') as dummy_value from TEST;
+------+------+-------------+
| COL1 | COL2 | DUMMY_VALUE |
|------+------+-------------|
|    1 |    1 | 1           |
|    2 |    2 | 1           |
|    3 |    3 | 1           |
+------+------+-------------+

Following SQL statement uses row subquery in the WHERE clause.

> select * from TEST where col1 = (select '1' as dummy_value from dual);
+------+------+
| COL1 | COL2 |
|------+------|
|    1 |    1 |
+------+------+

Snowflake Scalar Subquery

A scalar subquery is a regular SELECT query in parentheses that returns exactly one value. i.e. one row with one column.

For example, consider following SQL statement that contains a scalar subquery.

select *, (select max(col1) as max_c from test) as max_c from TEST;
+------+------+-------+
| COL1 | COL2 | MAX_C |
|------+------+-------|
|    1 |    1 |     3 |
|    2 |    2 |     3 |
|    3 |    3 |     3 |
+------+------+-------+

The scalar subquery is similar to row subquery.

Snowflake Table Subquery

A table subquery returns multiple rows and multiple columns. You can use these type of subqueries in a FROM clause. You can also use a table subquery as an argument of an EXISTS, IN, ANY, or ALL clauses.

For example, consider following SQL statement with table subquery.

select * from TEST where col1 in (select col1 from test);
+------+------+
| COL1 | COL2 |
|------+------|
|    2 |    2 |
|    3 |    3 |
|    1 |    1 |
+------+------+

Snowflake Correlated subquery

Correlated subquery is a query within a query that refer the columns from the parent or outer query. This kind of subquery contains one or more correlations between its columns and the columns produced by the outer query.

Snowflake supports only scalar correlated subquery in WHERE, EXISTS, ANY / ALL, and IN clause.

select * from TEST as t1 
where exists (select col1 from test as t2 where t1.col1 = t2.col2);
+------+------+
| COL1 | COL2 |
|------+------|
|    2 |    2 |
|    3 |    3 |
|    1 |    1 |
+------+------+