Nested queries (subqueries; a query within another query) can be performed in Snowflake.
When the subqueries are in a WHERE or FROM clause, they are used to provide data that will be used to have a limit or compare the data that is returned by the containing query.
There also are different types of subqueries:
Correlated VS Uncorrelated
-Correlated subqueries contain columns from outside the subquery. In correlated subqueries, the columns are generally referenced in the WHERE clause, as if the subquery were evaluated on every row of the table in the outer query.
-Uncorrelated subqueries have no external column references. This query is independent and its results are returned one time only (not for each row) and used by the outer query.
Scalar VS Non-scalar
-Scalar subqueries return a single value (one column of one row). When no rows are eligible to be returned, the subquery returns NULL.
-The result of a non-scalar subquery can be zero, one or more rows, with one or more columns in each row. Whenever a column does not have a value to return, the subquery returns NULL. The subquery returns 0 rows if no rows qualify to be returned (not NULLs).
But the specific subqueries that are supported by Snowflake are:
-Uncorrelated scalar subqueries in any place where a value expression can be used
-Correlated scalar subqueries the clause WHERE
-EXISTS, ANY / ALL, and IN subqueries in WHERE clauses and they can be correlated or uncorrelated