Other dialects: SQL Server, Oracle, DuckDB

Beyond PostgreSQL, MySQL and SQLite, three dialects turn up most often in job descriptions and data work: T-SQL, the SQL of Microsoft SQL Server; Oracle SQL, which adds its own row limits, sequences and DUAL table; and DuckDB, a small analytical engine that reads files as if they were tables and adds shorthands such as QUALIFY and GROUP BY ALL. The good news is that most of what you know transfers: the relational model, SELECT, joins, GROUP BY, subqueries, common table expressions and window functions are the same language in all of them. What differs is the edges, and the edges are short enough to list.

DuckDB runs in this lesson, with its own recorded output, because it is pinned here as DuckDB 1.5.6. T-SQL and Oracle SQL are described from their official documentation only, with no outputs: the next module takes those two engines side by side with results recorded on their own servers.

What transfers, and what each dialect spells its own way

In this track’s three engines you write LIMIT … OFFSET for a row limit, an identity column for a generated key, ON CONFLICT … DO UPDATE for an upsert, || to join two strings, a common table expression to filter on a window result, and a bare SELECT 1 when no table is needed. Here is what the other three want instead:

Everything in that table is one line of code. The differences that cost a day are the ones in behaviour, and three of them are worth memorising. Oracle stores text of length zero as a null, and its manual warns that this may change in a future release, so code should not lean on it. T-SQL sorts nulls as the lowest value, which puts them first in an ascending sort, as in MySQL and SQLite but not as in PostgreSQL. And DuckDB divides with two operators, / for floating point and // for whole numbers, so the division trap of this track’s three engines has a third answer.

DuckDB: QUALIFY, and the query it replaces

QUALIFY keeps or drops rows by what a window function returned, as HAVING does for an aggregate. DuckDB’s documentation describes it as a way to do that filtering without a subquery or a WITH clause, and places it after the WINDOW clause and before ORDER BY.

"""The statements of ../duckdb/qualify.sql on DuckDB 1.5.6, as its shell prints them."""

from duckdb_server import duckdb_file

duckdb_file("../duckdb/qualify.sql")

┌────────┬──────────┬────────┐
│  city  │ customer │ amount │
├────────┼──────────┼────────┤
│ Jaipur │ Tara     │ 600    │
│ Kochi  │ Meena    │ 700    │
│ Pune   │ Kabir    │ 900    │
└────────┴──────────┴────────┘
┌────────┬──────────┬────────┐
│  city  │ customer │ amount │
├────────┼──────────┼────────┤
│ Jaipur │ Tara     │ 600    │
│ Kochi  │ Meena    │ 700    │
│ Pune   │ Kabir    │ 900    │
└────────┴──────────┴────────┘
┌────────┬──────────┬────────┐
│  city  │ customer │ amount │
├────────┼──────────┼────────┤
│ Jaipur │ Tara     │ 600    │
│ Kochi  │ Meena    │ 700    │
│ Pune   │ Ravi     │ 900    │
└────────┴──────────┴────────┘
┌────────┬──────────┬────────┐
│  city  │ customer │ amount │
├────────┼──────────┼────────┤
│ Jaipur │ Tara     │ 600    │
│ Kochi  │ Meena    │ 700    │
│ Pune   │ Kabir    │ 900    │
│ Pune   │ Ravi     │ 900    │
└────────┴──────────┴────────┘

-- DuckDB 1.5.6: the biggest order of each city, three ways, and what a tie does.
CREATE TABLE orders (id INTEGER PRIMARY KEY, city VARCHAR NOT NULL, customer VARCHAR NOT NULL, amount INTEGER NOT NULL);
INSERT INTO orders VALUES
  (1, 'Pune', 'Asha', 400), (2, 'Pune', 'Ravi', 900), (3, 'Kochi', 'Meena', 700),
  (4, 'Kochi', 'Arjun', 250), (5, 'Jaipur', 'Tara', 600), (6, 'Pune', 'Kabir', 900);

-- 1. QUALIFY filters on the window function itself, with a named window and a tie-breaker.
SELECT city, customer, amount
FROM orders
WINDOW w AS (PARTITION BY city ORDER BY amount DESC, customer)
QUALIFY row_number() OVER w = 1
ORDER BY city;

-- 2. The same answer as a common table expression, which PostgreSQL, MySQL and SQLite also run.
WITH ranked AS (
  SELECT city, customer, amount,
         row_number() OVER (PARTITION BY city ORDER BY amount DESC, customer) AS rn
  FROM orders
)
SELECT city, customer, amount FROM ranked WHERE rn = 1 ORDER BY city;

-- 3. The same QUALIFY without the tie-breaker: Pune has two orders of 900, and one of them wins by chance.
SELECT city, customer, amount
FROM orders
QUALIFY row_number() OVER (PARTITION BY city ORDER BY amount DESC) = 1
ORDER BY city;

-- 4. Every tied row kept, by ranking instead of numbering.
SELECT city, customer, amount
FROM orders
QUALIFY rank() OVER (PARTITION BY city ORDER BY amount DESC) = 1
ORDER BY city, customer;

The first two queries return the same three rows, so QUALIFY is a shorthand, not a new ability. The third shows why the tie-breaker matters: Pune has two orders of 900, and without , customer in the window’s ORDER BY the engine picks one of them, here Ravi rather than Kabir. The fourth keeps both tied rows by ranking instead of numbering.

On an engine without QUALIFY, the common table expression is the answer, because a window function may not appear in WHERE at all:

-- The same question in SQLite 3.49.1, which has no QUALIFY: the biggest order of each city.
CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO orders VALUES
  (1, 'Pune', 'Asha', 400), (2, 'Pune', 'Ravi', 900), (3, 'Kochi', 'Meena', 700),
  (4, 'Kochi', 'Arjun', 250), (5, 'Jaipur', 'Tara', 600), (6, 'Pune', 'Kabir', 900);

-- 1. The common table expression that stands in for QUALIFY, with a tie-breaker.
WITH ranked AS (
  SELECT city, customer, amount,
         row_number() OVER (PARTITION BY city ORDER BY amount DESC, customer) AS rn
  FROM orders
)
SELECT city, customer, amount FROM ranked WHERE rn = 1 ORDER BY city;

-- 2. A window function in WHERE is refused: that is the rule QUALIFY exists to work around.
SELECT city, customer FROM orders WHERE row_number() OVER (PARTITION BY city ORDER BY amount DESC) = 1;

┌────────┬──────────┬────────┐
│  city  │ customer │ amount │
├────────┼──────────┼────────┤
│ Jaipur │ Tara     │ 600    │
│ Kochi  │ Meena    │ 700    │
│ Pune   │ Kabir    │ 900    │
└────────┴──────────┴────────┘

Parse error near line 16: misuse of window function row_number()

SQLite answers the second query with misuse of window function row_number(). PostgreSQL and MySQL refuse it too: WHERE is evaluated before window functions are computed, which is the rule QUALIFY exists to work around. Oracle has QUALIFY, and its manual pins the order of operations: FROM, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, DISTINCT, ORDER BY, FETCH FIRST.

The other shorthands, named

DuckDB adds five more conveniences, each of which stands for a longer form you already know. GROUP BY ALL groups by every column of the select list that is not inside an aggregate. ORDER BY ALL sorts by all the selected columns, left to right. SELECT * EXCLUDE (a, b) returns every column except the ones named. COLUMNS('…') picks the columns whose names match a pattern and applies one function to each. A file as a table lets SELECT … FROM 'orders.csv' read a CSV or Parquet file with no CREATE TABLE at all.

The part below runs all five and gives the plain form beside each, then turns to T-SQL and Oracle SQL: what maps to what, which traps cost a day, and how to confirm each claim on a server of your own.

Your turn

SELECT city, customer, amount FROM orders
QUALIFY row_number() OVER (PARTITION BY city ORDER BY amount DESC, customer) = 1
ORDER BY city;

-- A start: every order with its position inside its city. Return only the first of each city.
SELECT city, customer, amount,
       row_number() OVER (PARTITION BY city ORDER BY amount DESC, customer) AS rn
FROM orders
ORDER BY city, rn;

# Sample tests: each one builds a small orders table, runs your SQL on it and compares the rows it returns, in order.
tests:
  - name: three cities, one tie broken by the customer name
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 'Pune', 'Asha', 400), (2, 'Pune', 'Ravi', 900), (3, 'Kochi', 'Meena', 700),
        (4, 'Kochi', 'Arjun', 250), (5, 'Jaipur', 'Tara', 600), (6, 'Pune', 'Kabir', 900);
    expected:
      columns: [city, customer, amount]
      rows:
        - [Jaipur, Tara, 600]
        - [Kochi, Meena, 700]
        - [Pune, Kabir, 900]
  - name: one city with one order
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 'Patna', 'Zoya', 1000);
    expected:
      columns: [city, customer, amount]
      rows:
        - [Patna, Zoya, 1000]
  - name: every order of a city has the same amount
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 'Surat', 'Dev', 500), (2, 'Surat', 'Bhavna', 500), (3, 'Surat', 'Chetan', 500);
    expected:
      columns: [city, customer, amount]
      rows:
        - [Surat, Bhavna, 500]
  - name: cities sorted by name, not by amount
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 'Mysuru', 'Latha', 100), (2, 'Bhopal', 'Imran', 900), (3, 'Agra', 'Nisha', 450),
        (4, 'Agra', 'Omar', 450);
    expected:
      columns: [city, customer, amount]
      rows:
        - [Agra, Nisha, 450]
        - [Bhopal, Imran, 900]
        - [Mysuru, Latha, 100]

The five DuckDB shorthands run, with the plain form beside each Pro: opens with a Pro, Premium or Ultimate pass

T-SQL and Oracle SQL mapped from what you already write, with the traps named Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• The relational core transfers: SELECT, joins, GROUP BY, subqueries, common table expressions and window functions are the same in T-SQL, Oracle SQL and DuckDB.
• Row limits, generated keys, upserts, string concatenation and “no table needed” are the five edges spelled differently in each dialect.
• QUALIFY filters on a window result in Oracle and DuckDB; everywhere else the same query is a common table expression, because WHERE runs before window functions.
• DuckDB’s shorthands each stand for a plain form, and reading files as tables guesses the types.
• Oracle treats an empty string as NULL, and T-SQL sorts NULLs first with no NULLS LAST to change it.