Relational Database Differences

Comparing MySQL, PostgreSQL, MSSQL, Oracle, Snowflake, SAP HANA, SQLite, and DuckDB

Author

Steven Lloyd
April 11, 2025

I love relational databases (RDBMS) and have used them for many, many years. Knowing about them and how to use them has enabled me to provide for my family. Over the years, I have found the following differences between the various databases. Here are the big ones.

Case Sensitivity - when searching a database it is imperative to understand how your database handles case. For instance WHERE color=’red’ and WHERE color=’Red’ may return different results depending on case sensitivity. Here is a breakdown by database:

  • MySQL: Case sensitivity depends on the collation used.

    • utf8_general_ci: Case insensitive

    • utf8_bin: Case sensitive

    • utf8mb4_general_ci: Case insensitive

    • utf8mb4_bin: Case sensitive

    • utf8mb4_unicode_ci: Case insensitive with better Unicode support

    • latin1_general_ci: Case insensitive

    • latin1_bin: Case sensitive

  • PostgreSQL: Case-sensitive by default. Use ILIKE or LOWER() for case-insensitive searches.

  • MSSQL: Case-insensitive by default. For case-sensitive searches, use a case-sensitive collation or COLLATE SQL_Latin1_General_CP1_CS_AS.

  • Oracle: Case-sensitive by default for string comparisons.

  • Snowflake: Case-insensitive by default. Use COLLATE 'case_sensitive' to make searches case-sensitive.

  • SAP HANA: Case-sensitive by default. Use UPPER() or LOWER() functions for case-insensitive searches.

  • SQLite: Default is case-insensitive. Use PRAGMA case_sensitive_like=ON to enable case-sensitive searches.

  • DuckDB: Case-insensitive by default. Use the COLLATE keyword to change this behavior.

NOTE: when dealing with JSON fields, all searches are typically case-sensitive regardless of the table's collation or database type.

Next is pagination. While many databases support LIMIT and OFFSET, not all do.

  • MySQL: Uses LIMIT x OFFSET y syntax.

  • PostgreSQL: Supports both LIMIT x OFFSET y and OFFSET y FETCH FIRST x ROWS ONLY.

  • MSSQL: Uses OFFSET y ROWS FETCH NEXT x ROWS ONLY (SQL Server 2012+) or older TOP x with nested queries.

  • Oracle: Uses OFFSET y ROWS FETCH NEXT x ROWS ONLY (12c+) or ROWNUM for older versions.

  • Snowflake: Supports both LIMIT x OFFSET y and OFFSET y FETCH FIRST x ROWS ONLY.

  • SAP HANA: Uses LIMIT x OFFSET y.

  • SQLite: Supports LIMIT x OFFSET y.

  • DuckDB: Supports LIMIT x OFFSET y.

Next is nulls. In database systems, NULL represents the absence of a value or an unknown value. Here are the key concepts:

  • NULL is not a value, but a marker indicating missing information

  • NULL comparisons use three-valued logic (true/false/unknown)

  • NULL in calculations typically propagates (NULL + 5 = NULL)

  • NULL handling uses specific operators:

    • IS NULL / IS NOT NULL for checking existence

    • COALESCE() to provide default values

    • NULLIF() to convert specific values to NULL

NULL behavior varies slightly between database systems, particularly in how NULLs are sorted and in functions like GROUP BY and aggregations. Note that all eight databases support the COALESCE function and the NULLIF function. It is the ISNULL and IFNULL functions that vary:

  • MySQL: COALESCE(), NULLIF(), IFNULL() 

  • PostgreSQL: COALESCE(),NULLIF()

  • MSSQL: COALESCE(), NULLIF(), ISNULL()

  • Oracle: COALESCE(), NULLIF(), NVL()

  • Snowflake: COALESCE(), NULLIF(), IFNULL(), NVL(), ZEROIFNULL(), NULLIFZERO(),  

  • SAP HANA: COALESCE(), NULLIF(), IFNULL()

  • SQLite: COALESCE(), NULLIF(), IFNULL(), IIF()

  • DuckDB: COALESCE(), NULLIF(), IFNULL()

On to Date functions. From my experience the date functions are the ones you will be using most often in your queries as you build reports. Here are examples for four common date uses. 

-- Add a specified time interval (30 days) to a date
-- MySQL
SELECT DATE_ADD('2025-04-04', INTERVAL 30 DAY);
-- PostgreSQL
SELECT '2025-04-04'::date + INTERVAL '30 days';
-- MsSQL (SQLServer)
SELECT DATEADD(day, 30, '2025-04-04');
-- Oracle
SELECT DATE '2025-04-04' + 30 FROM dual;
-- SnowFlake
SELECT DATEADD(day, 30, '2025-04-04'::date);
-- SAP HANA
SELECT ADD_DAYS('2025-04-04', 30) FROM dummy;
-- SQLite
SELECT date('2025-04-04', '+30 days');
-- DuckDB
SELECT DATE '2025-04-04' + INTERVAL '30 days';


-- Get Days between two dates
-- MySQL
SELECT DATEDIFF('2025-05-04', '2025-04-04');
-- PostgreSQL
SELECT '2025-05-04'::date - '2025-04-04'::date;
-- MsSQL (SQLServer)
SELECT DATEDIFF(day, '2025-04-04', '2025-05-04');
-- Oracle
SELECT DATE '2025-05-04' - DATE '2025-04-04' FROM dual;
-- SnowFlake
SELECT DATEDIFF(day, '2025-04-04', '2025-05-04');
-- SAP HANA
SELECT DAYS_BETWEEN('2025-04-04', '2025-05-04') FROM dummy;
-- SQLite
SELECT julianday('2025-05-04') - julianday('2025-04-04');
-- DuckDB
SELECT DATE '2025-05-04' - DATE '2025-04-04';


-- Get the Current Date
-- MySQL
SELECT CURDATE(); -- YYYY-MM-DD
SELECT CURRENT_DATE(); -- YYYY-MM-DD
-- PostgreSQL
SELECT CURRENT_DATE; -- YYYY-MM-DD
-- MsSQL (SQLServer)
SELECT CONVERT(date, GETDATE()); -- YYYY-MM-DD
SELECT CAST(GETDATE() AS date); -- YYYY-MM-DD
-- Oracle
SELECT TRUNC(SYSDATE) FROM dual; -- Get date without time
SELECT CURRENT_DATE FROM dual; -- With session timezone
-- SnowFlake
SELECT CURRENT_DATE(); -- YYYY-MM-DD
-- SAP HANA
SELECT CURRENT_DATE FROM dummy; -- YYYY-MM-DD
-- SQLite
SELECT date('now'); -- YYYY-MM-DD
-- DuckDB
SELECT CURRENT_DATE; -- YYYY-MM-DD


-- Get the current time
-- MySQL
SELECT CURTIME(); -- HH:MM:SS
SELECT CURRENT_TIME(); -- HH:MM:SS
-- PostgreSQL
SELECT CURRENT_TIME; -- HH:MM:SS+TZ
SELECT LOCALTIME; -- HH:MM:SS without timezone
-- MsSQL (SQLServer)
SELECT CONVERT(time, GETDATE()); -- HH:MM:SS.sss
SELECT CAST(GETDATE() AS time); -- HH:MM:SS.sss
-- Oracle
SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') FROM dual; -- HH:MM:SS
SELECT CURRENT_TIMESTAMP FROM dual; -- Full timestamp with TZ
-- SnowFlake
SELECT CURRENT_TIME(); -- HH:MM:SS.sss
-- SAP HANA
SELECT CURRENT_TIME FROM dummy; -- HH:MM:SS
-- SQLite
SELECT time('now'); -- HH:MM:SS
-- DuckDB
SELECT CURRENT_TIME; -- HH:MM:SS

In recent years JSON column support has been adopted by nearly all relational databases but how they operate on JSON is slightly different between systems. For instance, if you are using JSON fields you are going to want to query specific fields in the JSON data. Here is how you do that in each: 

-- Querying Specific Fields
-- Mysql
SELECT JSON_EXTRACT(data, '$.name') FROM users;
SELECT data->'$.name' FROM users;
SELECT data->'$.address.city' FROM users;
-- PostgreSQL
SELECT data->>'name' FROM users;
SELECT data->'address'->>'city' FROM users;
SELECT data->'phones'->0->>'number' FROM users;
-- MsSQL (SQL SERVER)
SELECT JSON_VALUE(data, '$.name') FROM users;
SELECT JSON_QUERY(data, '$.address') FROM users;
SELECT * FROM users WHERE JSON_VALUE(data, '$.active') = 'true';
-- Oracle
SELECT JSON_VALUE(data, '$.name') FROM users;
SELECT JSON_VALUE(data, '$.address.city') FROM users;
SELECT JSON_VALUE(data, '$.phones[0].number') FROM users;
-- Snowflake
SELECT data:name::STRING FROM users;
SELECT data:address.city::STRING FROM users;
SELECT data:tags[0]::STRING FROM users;
-- SAP HANA
SELECT JSON_VALUE(data, '$.name') FROM users;
SELECT JSON_VALUE(data, '$.address.city') FROM users;
-- SQLite
SELECT json_extract(data, '$.name') FROM users;
SELECT data->'$.name' FROM users;
SELECT data->'$.address.city' FROM users;
-- DuckDB
SELECT data->'name' FROM users;
SELECT JSON_EXTRACT(data, '$.name') FROM users;
SELECT data->'address'->'city' FROM users;

Another thing you will likely want to do is transform the JSON data into rows and columns. Here is how in each: 

-- Transforming JSON to Relational rows and columns
-- MySQL
SELECT t.* FROM users,
JSON_TABLE(data, '$' COLUMNS(
  id INT PATH '$.id',
  name VARCHAR(200) PATH '$.name',
  city VARCHAR(200) PATH '$.address.city'
)) AS t;

-- PostgreSQL (two ways)
-- Using jsonb_to_recordset
SELECT p.name, p.email
FROM users,
JSONB_TO_RECORDSET(data->'contacts') AS p(name text, email text);
-- Using json_each
SELECT key, value FROM users, json_each(data);

-- MSSQL (SQL Server)
SELECT * FROM users
CROSS APPLY OPENJSON(data)
WITH (
  id INT '$.id',
  name NVARCHAR(200) '$.name',
  city NVARCHAR(200) '$.address.city'
);

-- Oracle
SELECT j.id, j.name, j.city
FROM users,
JSON_TABLE(data, '$' COLUMNS (
  id NUMBER PATH '$.id',
  name VARCHAR2(200) PATH '$.name',
  city VARCHAR2(200) PATH '$.address.city'
)) j;

-- Snowflake (two ways)
-- Using FLATTEN to unnest arrays
SELECT 
  value:id::INT as id,
  value:name::STRING as name
FROM users,
LATERAL FLATTEN(input => data:items);
-- Using PARSE_JSON
SELECT 
  PARSE_JSON(data):name::STRING as name,
  PARSE_JSON(data):email::STRING as email
FROM users;

-- SAP HANA
SELECT jt.*
FROM users,
JSON_TABLE(data, '$' COLUMNS(
  "ID" INTEGER PATH '$.id',
  "NAME" VARCHAR(200) PATH '$.name',
  "CITY" VARCHAR(200) PATH '$.address.city'
)) AS jt;

-- SQLite
SELECT 
  json_extract(value, '$.id') AS id,
  json_extract(value, '$.name') AS name
FROM users, json_each(data->'$.items');

-- DuckDB
SELECT * FROM users, 
UNNEST(json_object_keys(data)) AS t(key);

SELECT 
  JSON_EXTRACT_STRING(data, '$.id') AS id,
  JSON_EXTRACT_STRING(data, '$.name') AS name
FROM users;

Finally, if you are going to query your JSON data you will want to index it for performance. Here is how in each. 

-- MySQL supports both direct and generated column indexes
CREATE INDEX idx_user_name ON users ((data->'$.name'));

ALTER TABLE users ADD COLUMN name VARCHAR(255) 
GENERATED ALWAYS AS (data->'$.name') STORED;
CREATE INDEX idx_name ON users(name);


-- PostgreSQL supports both direct and generated column indexes
-- GIN index for containment operators
CREATE INDEX idx_data ON users USING GIN (data);
-- Index specific path (JSONB only)
CREATE INDEX idx_name ON users USING GIN ((data->'name'));
-- BTREE index for equality
CREATE INDEX idx_email ON users ((data->>'email'));

ALTER TABLE users ADD COLUMN name TEXT 
GENERATED ALWAYS AS (data->>'name') STORED;
CREATE INDEX idx_name ON users(name);


-- MSSQL (SQL Server) ONLY supports generated column indexes
ALTER TABLE users ADD name AS JSON_VALUE(data, '$.name');
CREATE INDEX idx_name ON users(name);


-- Oracle supports both direct and generated column indexes
-- Function-based index
CREATE INDEX idx_name ON users(
  JSON_VALUE(data, '$.name' RETURNING VARCHAR2(255))
);
-- Specialized JSON search index
CREATE SEARCH INDEX idx_json ON users(data) FOR JSON;

ALTER TABLE users ADD (name VARCHAR2(255) 
GENERATED ALWAYS AS (JSON_VALUE(data, '$.name')));
CREATE INDEX idx_name ON users(name);


-- Snowflake DOES NOT SUPPORT EITHER - use materialized views instead


-- SAP HANA ONLY supports generated column indexes
ALTER TABLE users ADD (name_virtual VARCHAR(255) 
  GENERATED ALWAYS AS (JSON_VALUE(data, '$.name')));
CREATE INDEX idx_name ON users(name_virtual);


-- SQLite supports both direct and generated column indexes
CREATE INDEX idx_name ON users(json_extract(data, '$.name'));
-- Generated columns added in SQLite 3.31.0
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  data TEXT,
  name TEXT GENERATED ALWAYS AS (json_extract(data, '$.name'))
);
CREATE INDEX idx_name ON users_new(name);


-- DuckDB DOES NOT SUPPORT EITHER - use materialized views instead

Congratulations on making it to the end of this article! Thanks again for reading. Please like and SHARE. 🙂