SQL dialects

Integer division#

DuckDB returns 2.5 for 5 / 2. Postgres, SQL Server and SQLite drop the remainder when both sides are integers, so they return 2, and a rate such as 3 / 4 comes out as 0. Multiply one side by 1.0 before dividing, and all four return the same answer.

Averaging a column of integers#

AVG over the integers 1 and 2 returns 1.5 in DuckDB, Postgres and SQLite. SQL Server averages integers as integers and returns 1. Write AVG(x * 1.0) when the column holds integers.

See: §13. Aggregate functions

Rounding a value that ends in 5#

On a DECIMAL column, ROUND(2.5, 0) returns 3 on all four engines, which is what Excel does. On a DOUBLE, Postgres usually rounds a value ending in exactly 5 to the nearest even number, so ROUND(x) returned 2 there and 3 on the other three. Postgres also refuses ROUND(x, 0) on a DOUBLE. Cast to DECIMAL before rounding money, and the four engines agree.

See: ROUND in the Python phrasebook, where Python and polars round to the nearest even number

Dividing by zero#

1 / 0 stops the query with an error in Postgres and SQL Server, returns NULL in SQLite, and returns inf, which is infinity, in DuckDB. NULLIF(denominator, 0) turns a zero denominator into NULL before the division, and the division then returns NULL on all four.

Comparing text that differs only in case#

The course's AdventureWorks database uses the collation SQL_Latin1_General_CP1_CI_AS, where CI stands for case-insensitive, so 'abc' = 'ABC' is true there. DuckDB and Postgres treat the two strings as different. SQLite treats them as different for = and as equal for LIKE. A text filter can therefore match more rows on SQL Server than the same filter matches in DuckDB. The same collation merges values that differ only in case when SQL Server counts distinct values or forms groups, so 'abc', 'ABC' and 'Abc' count as one value there and as three on the other engines.

See: §7. WHERE · §14. GROUP BY

Comparing text with a number#

'01' = 1 is true in DuckDB, Postgres and SQL Server, which convert the text to a number before comparing. SQLite compares a text value and a number as two different types and returns false.

See: §7. WHERE

Spaces at the end of text#

SQL Server ignores spaces at the end of a string in two places. Its LEN('Chai ') returns 4, where LENGTH('Chai ') returns 5 on the other three engines. And 'Chai' = 'Chai ' is true on SQL Server and false on the rest. Northwind stores one customer id as 'Val2 ', so WHERE "CustomerID" = 'Val2' finds nothing in DuckDB, and the same comparison is true on SQL Server.

See: §7. WHERE

Where NULL goes in a sort#

In an ascending sort, NULL comes last in DuckDB and Postgres and first in SQL Server and SQLite, so the first rows of the same sort can differ between engines. In a descending sort, only Postgres puts NULL first. NULLS LAST and NULLS FIRST fix the position in DuckDB, Postgres and SQLite. SQL Server rejects both keywords. Sort on CASE WHEN x IS NULL THEN 1 ELSE 0 END first and on x second, which puts NULL last on all four, ascending or descending.

See: §8. ORDER BY and LIMIT

NULL inside NOT IN#

All four engines agree on this one. When the subquery returns a NULL, WHERE id NOT IN (subquery) keeps no rows at all. NOT EXISTS keeps the rows you meant. In the table, the outer query holds 3 and 4 and the subquery returns 1 and NULL.

See: §21. Subqueries

Taking the first rows#

DuckDB, Postgres and SQLite end a query with LIMIT n. SQL Server rejects LIMIT and writes TOP n directly after SELECT.

See: §8. ORDER BY and LIMIT

Joining strings#

DuckDB, Postgres and SQLite join text with two vertical bars, as in first_name || last_name. SQL Server joins it with +. With either operator, a single NULL makes the whole result NULL. CONCAT() works on all four and treats a NULL as empty text.

Median#

MEDIAN() exists in DuckDB and in the SQLite build tested here. Postgres and SQL Server have no such function, and neither did an older SQLite, version 3.45.1. The standard form is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x), which DuckDB and Postgres accept as written and SQLite rejects. SQL Server rejects it as written and accepts SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER () FROM t.

See: §13. Aggregate functions

Using a column alias in WHERE#

DuckDB and SQLite accept a name defined in SELECT inside WHERE. Postgres and SQL Server reject it, because they evaluate WHERE before SELECT has named anything. Repeating the expression in WHERE works on all four.

See: §16. The order the clauses actually run in

True and false#

SELECT TRUE returns true in DuckDB and Postgres, and 1 in SQLite, which stores true and false as 1 and 0. SQL Server has no TRUE keyword. A SQL Server column that holds true or false has the type bit, and you compare it with 1 or 0.

Writing a date#

The handbook writes a date as DATE '2016-07-04', and turns text into a date with ::DATE. DuckDB and Postgres accept both forms. SQL Server and SQLite reject both. CAST(text AS DATE) runs on all four engines, but SQLite returns the number 2016 without an error, so on SQLite write date('2016-07-04 10:30:00').

See: §12. Dates: truncating, differencing, grouping by month

Days between dates#

The handbook writes date_diff('day', a, b), which only DuckDB accepts. DuckDB and Postgres also subtract one date from another directly. SQL Server writes DATEDIFF(DAY, a, b), and SQLite subtracts two julianday() values, which gives a decimal number.

See: §12. Dates: truncating, differencing, grouping by month

First day of the month#

DuckDB and Postgres write date_trunc('month', d), SQL Server writes DATETRUNC(month, d), and SQLite writes date(d, 'start of month'). For 2016-07-16 all four land on 2016-07-01, and they return it as four different types. Cast the result with CAST(... AS DATE) in DuckDB and Postgres to get a date.

See: §12. Dates: truncating, differencing, grouping by month

Names with spaces#

A table or column name that contains a space, such as "Order Details", goes in double quotes, and all four engines accept that. SQL Server and SQLite also accept square brackets, as in [Order Details]. DuckDB and Postgres reject the brackets.

See: What a database is

Shortcuts only DuckDB accepts#

ANTI JOIN, SEMI JOIN and GROUP BY ALL run in DuckDB and fail in the other three. When a query has to run on another engine, write the anti join as NOT EXISTS or as a LEFT JOIN followed by WHERE ... IS NULL, and list the grouping columns in GROUP BY.

See: §18. LEFT JOIN, and what it preserves · §14. GROUP BY