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
- Query starts: FROM and JOINs
- WHERE filters rows
- GROUP BY and HAVING aggregate
- Window functions are computed
- Does the row pass the QUALIFY condition? Yes: Keep the row. No: Discard the row.
- 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
- QUALIFY filters on window functions without a wrapping subquery
- QUALIFY is evaluated after window functions, like HAVING after GROUP BY
- WITH RECURSIVE handles hierarchical, org-chart style queries
- ROW_NUMBER picks one row per group; RANK keeps ties
- Joins and subqueries behave like standard ANSI SQL