# SQL statements

***

### SQL Statements

{% embed url="<https://duckdb.org/docs/stable/sql/introduction>" %}

***

#### 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):

```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`:

```sql
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                         |

***