Joins, Subqueries & Advanced SQL

Snowflake · Data Types & SQL Fundamentals

Theory

Snowflake supports standard ANSI SQL for combining data: INNER, LEFT, RIGHT, FULL OUTER and CROSS joins, subqueries in SELECT, FROM and WHERE, and common table expressions (WITH). WITH RECURSIVE walks hierarchies such as an employee-manager tree. Window functions like ROW_NUMBER, RANK and SUM() OVER compute values across related rows without collapsing them.

Snowflake adds QUALIFY, which filters on the result of a window function the way HAVING filters on aggregates. For example, to keep the top earner per department: SELECT name, dept, salary FROM employees QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1, with no wrapping subquery.

Why we use it

Real questions need data from several tables and comparisons across rows: top-N per group, running totals, deduplication. These tools express that directly in SQL, and QUALIFY keeps such queries short.

When to use it

Use joins to combine tables, CTEs to break long logic into readable steps, window functions for rankings and running totals, and QUALIFY for top-N per group or deduplication. Use recursive CTEs only for real hierarchies.

Joins and Subqueries

INNER, LEFT, RIGHT, and FULL OUTER joins work exactly as in any standard SQL database; subqueries can appear in the WHERE, FROM, or SELECT clause and are optimized the same way a top-level query would be.

CTEs and Recursive CTEs

WITH clauses (common table expressions) make multi-step queries readable, and WITH RECURSIVE specifically supports hierarchical queries like org charts or category trees.

QUALIFY and Window Functions

The QUALIFY clause lets you filter directly on a window function's result (like ROW_NUMBER() = 1) without wrapping the query in a subquery — a Snowflake convenience most other databases don't have.

How it works

  1. Query starts: FROM and JOINs
  2. WHERE filters rows
  3. GROUP BY and HAVING aggregate
  4. Window functions are computed
  5. Does the row pass the QUALIFY condition? Yes: Keep the row. No: Discard the row.
  6. SELECT, ORDER BY and LIMIT produce the result

Example

-- INNER JOIN: only matching rows
SELECT e.name, d.dept_name FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT JOIN: keep unmatched employees too
SELECT e.name, COALESCE(d.dept_name,'Unassigned') AS dept
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- CTE
WITH dept_stats AS (
  SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id)
SELECT d.dept_name, ds.avg_salary FROM dept_stats ds JOIN departments d ON ds.dept_id = d.dept_id;

-- QUALIFY: top earner per department, no subquery needed
SELECT name, dept_id, salary FROM employees
QUALIFY RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) = 1;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic