SQL Differences by Database — Cheat Sheet

The SQL in this tutorial is written for SQLite. Each lesson covers DB-specific differences using MySQL/PostgreSQL tabs, so this page serves as a quick-reference summary table.

Rather than writing SQL that works across all databases, it's more efficient to leverage each DB's native syntax.

Lesson-by-Lesson DB Difference Guide

These are topics covered in detail with SQLite/MySQL/PostgreSQL tabs in each lesson. Refer to the respective lessons.

Data Type Comparison

SQLite uses a dynamic type system. The type names above represent "type affinity" — in practice, any value can be stored.

Identifier Quoting

When wrapping identifiers that contain reserved words or spaces:

Auto-Increment Comparison

Details: Lesson 16 DDL

MERGE Statement

MERGE is an ANSI SQL standard that conditionally performs INSERT/UPDATE/DELETE in a single statement.

MERGE INTO products AS target
USING staging_products AS source
ON target.sku = source.sku
WHEN MATCHED AND source.is_active = 0 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET name = source.name, price = source.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price)
    VALUES (source.sku, source.name, source.price);

MERGE INTO products target
USING staging_products source
ON (target.sku = source.sku)
WHEN MATCHED THEN
    UPDATE SET name = source.name, price = source.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price)
    VALUES (source.sku, source.name, source.price);

UPSERT details: Lesson 15 DML

Feature Support Version Matrix

Tutorial SQL Conversion Checklist

Items to check when running this tutorial's SQLite queries on other databases: