SQL statements

---

SQL Statements

SQL IntroductionDuckDB

---

Searchable History

DuckDB supports Reverse Search. Press Ctrl + R and start typing a part of an old query (e.g., "COPY" or "SUMMARIZE"). It will find the last time you ran that command.

---

QUALIFY (The Postgres "Missing Link")

QUALIFY is a popular clause in BigQuery, Snowflake, and Teradata. It acts like a HAVING clause but for Window Functions (like ROW_NUMBER() or RANK()).

How It Works

The logical order of operations in SQL is typically: FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT.

Because WHERE happens before window functions are calculated, you can't use a window function inside a WHERE clause. QUALIFY acts like a HAVING clause, but specifically for those window calculations.

Example: Finding the Top Performer

Imagine you have a table of salespeople and you want to find only the top seller in each region.

The "Old" Way (Postgres/Standard SQL):

SELECT * FROM (
  SELECT name, region, sales,
         RANK() OVER (PARTITION BY region ORDER BY sales DESC) as pos
  FROM sales_team
) 
WHERE pos = 1;

The DuckDB Way with QUALIFY:

SELECT name, region, sales
FROM sales_team
QUALIFY RANK() OVER (PARTITION BY region ORDER BY sales DESC) = 1;

Key Advantages in DuckDB

• Less Code: You avoid the "Select * From (Select * From...)" nesting dolls.

• Calculations on the Fly: You don't even have to include the window function in your SELECT list to filter by it. DuckDB can calculate it in the background just for the QUALIFY check.

• Readability: It keeps the focus on your business logic (e.g., "Give me the most recent record") rather than the structural limitations of SQL.

DuckDB's Secret Weapon: COLUMNS()

DuckDB also allows you to combine QUALIFY with its COLUMNS() expression. For example, if you wanted to filter out any row where any numeric column’s moving average exceeds a certain threshold, you can do that very concisely.

Comparison Table: WHERE vs. HAVING vs. QUALIFY

Clause	Filters Based On...	Can use Window Functions?
WHERE	Individual row data	❌ No
HAVING	Grouped/Aggregated data (SUM, COUNT)	❌ No
QUALIFY	Window Function results (RANK, LEAD)	✅ Yes

---