DML & DDL Differences Across Database Engines

Intermediate

Overview

This guide provides a comprehensive comparison of Data Definition Language (DDL) and Data Manipulation Language (DML) syntax across major SQL database engines. Understanding these differences is crucial for writing portable SQL or migrating between databases.

DDL (Data Definition Language): Commands that define database structure

• CREATE, ALTER, DROP (tables, indexes, schemas)
• TRUNCATE
• Constraints and data types

DML (Data Manipulation Language): Commands that manipulate data

• SELECT, INSERT, UPDATE, DELETE
• MERGE/UPSERT operations
• Bulk operations

DDL Differences

CREATE TABLE

Basic Table Creation

PostgreSQL:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary NUMERIC(10, 2) DEFAULT 0,
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT true,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

MySQL:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary DECIMAL(10, 2) DEFAULT 0,
    hire_date DATE DEFAULT (CURRENT_DATE),
    is_active TINYINT(1) DEFAULT 1,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SQL Server:

CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) UNIQUE,
    salary DECIMAL(10, 2) DEFAULT 0,
    hire_date DATE DEFAULT GETDATE(),
    is_active BIT DEFAULT 1,
    metadata NVARCHAR(MAX) CHECK (ISJSON(metadata) = 1),
    created_at DATETIME2 DEFAULT GETDATE()
);

Oracle:

CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    email VARCHAR2(255) UNIQUE,
    salary NUMBER(10, 2) DEFAULT 0,
    hire_date DATE DEFAULT SYSDATE,
    is_active NUMBER(1) DEFAULT 1 CHECK (is_active IN (0, 1)),
    metadata CLOB CHECK (metadata IS JSON),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

SQLite:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    salary REAL DEFAULT 0,
    hire_date TEXT DEFAULT (DATE('now')),
    is_active INTEGER DEFAULT 1,
    metadata TEXT,  -- Store JSON as text
    created_at TEXT DEFAULT (DATETIME('now'))
);

BigQuery:

CREATE TABLE employees (
    id INT64,
    name STRING NOT NULL,
    email STRING,
    salary NUMERIC(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE(),
    is_active BOOL DEFAULT true,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(hire_date)
CLUSTER BY id;

Snowflake:

CREATE TABLE employees (
    id INTEGER AUTOINCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary NUMBER(10, 2) DEFAULT 0,
    hire_date DATE DEFAULT CURRENT_DATE(),
    is_active BOOLEAN DEFAULT true,
    metadata VARIANT,  -- Semi-structured data
    created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

Key Differences in CREATE TABLE

Feature	PostgreSQL	MySQL	SQL Server	Oracle	SQLite	BigQuery	Snowflake
Auto-increment	SERIAL, IDENTITY	AUTO_INCREMENT	IDENTITY(1,1)	GENERATED AS IDENTITY	AUTOINCREMENT	None (use app)	AUTOINCREMENT
Boolean type	BOOLEAN	TINYINT(1)	BIT	NUMBER(1)	INTEGER	BOOL	BOOLEAN
String type	VARCHAR, TEXT	VARCHAR, TEXT	NVARCHAR, VARCHAR	VARCHAR2, CLOB	TEXT	STRING	VARCHAR, TEXT
JSON type	JSON, JSONB	JSON	NVARCHAR(MAX)	JSON (21c), CLOB	TEXT	JSON	VARIANT, OBJECT
Timestamp	TIMESTAMP, TIMESTAMPTZ	TIMESTAMP, DATETIME	DATETIME2	TIMESTAMP	TEXT	TIMESTAMP	TIMESTAMP_NTZ, TIMESTAMP_TZ
Current time	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	GETDATE()	SYSTIMESTAMP	DATETIME('now')	CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP()
Table options	-	ENGINE, CHARSET	-	-	-	PARTITION, CLUSTER	-

CREATE TABLE AS SELECT (CTAS)

PostgreSQL:

-- Create table from query
CREATE TABLE high_earners AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

-- With no data
CREATE TABLE employees_template AS
SELECT * FROM employees
WHERE 1=0;

MySQL:

CREATE TABLE high_earners AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

-- MySQL doesn't preserve indexes/constraints
-- Need to add them separately
ALTER TABLE high_earners ADD PRIMARY KEY (id);

SQL Server:

-- Standard CTAS
SELECT id, name, salary, hire_date
INTO high_earners
FROM employees
WHERE salary > 100000;

-- Structure only
SELECT *
INTO employees_template
FROM employees
WHERE 1=0;

Oracle:

CREATE TABLE high_earners AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

-- Preserves NOT NULL constraints but not other constraints

BigQuery:

CREATE TABLE high_earners AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

-- With partitioning and clustering
CREATE TABLE high_earners
PARTITION BY DATE(hire_date)
CLUSTER BY salary AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

Snowflake:

CREATE TABLE high_earners AS
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 100000;

-- Clone existing table (zero-copy)
CREATE TABLE employees_backup CLONE employees;

ALTER TABLE

Adding Columns

PostgreSQL:

-- Add single column
ALTER TABLE employees ADD COLUMN department_id INTEGER;

-- Add multiple columns
ALTER TABLE employees
    ADD COLUMN department_id INTEGER,
    ADD COLUMN manager_id INTEGER,
    ADD COLUMN title VARCHAR(100);

-- Add with constraint
ALTER TABLE employees
    ADD COLUMN department_id INTEGER NOT NULL DEFAULT 1;

MySQL:

-- Add single column
ALTER TABLE employees ADD COLUMN department_id INT;

-- Add multiple columns (comma-separated)
ALTER TABLE employees
    ADD COLUMN department_id INT,
    ADD COLUMN manager_id INT,
    ADD COLUMN title VARCHAR(100);

-- Specify position
ALTER TABLE employees ADD COLUMN department_id INT AFTER name;
ALTER TABLE employees ADD COLUMN employee_code VARCHAR(20) FIRST;

SQL Server:

-- Add single column
ALTER TABLE employees ADD department_id INT;

-- Add multiple columns
ALTER TABLE employees ADD
    department_id INT,
    manager_id INT,
    title NVARCHAR(100);

-- Add with default constraint (named)
ALTER TABLE employees
    ADD department_id INT CONSTRAINT DF_emp_dept DEFAULT 1;

Oracle:

-- Add single column
ALTER TABLE employees ADD (department_id NUMBER);

-- Add multiple columns (parentheses required)
ALTER TABLE employees ADD (
    department_id NUMBER,
    manager_id NUMBER,
    title VARCHAR2(100)
);

SQLite:

-- SQLite can only add one column at a time
ALTER TABLE employees ADD COLUMN department_id INTEGER;
ALTER TABLE employees ADD COLUMN manager_id INTEGER;

-- Cannot add NOT NULL column without default
-- Cannot drop columns (before 3.35.0)

BigQuery:

-- Add single column
ALTER TABLE employees ADD COLUMN department_id INT64;

-- BigQuery doesn't support adding multiple columns in one statement
ALTER TABLE employees ADD COLUMN manager_id INT64;
ALTER TABLE employees ADD COLUMN title STRING;

-- Add with options
ALTER TABLE employees
    ADD COLUMN IF NOT EXISTS department_id INT64
    OPTIONS(description="Department identifier");

Snowflake:

-- Add single column
ALTER TABLE employees ADD COLUMN department_id INTEGER;

-- Add multiple columns
ALTER TABLE employees ADD COLUMN
    department_id INTEGER,
    manager_id INTEGER,
    title VARCHAR(100);

Modifying Columns

PostgreSQL:

-- Change data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- Set/drop NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
ALTER TABLE employees ALTER COLUMN phone DROP NOT NULL;

-- Change default
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT true;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;

MySQL:

-- Modify column (change type, constraints, default)
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2) NOT NULL;

-- Change column (rename + modify)
ALTER TABLE employees CHANGE COLUMN name full_name VARCHAR(200) NOT NULL;

-- Set/drop default
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT 1;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;

SQL Server:

-- Change data type
ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);

-- Set/drop NOT NULL
ALTER TABLE employees ALTER COLUMN email NVARCHAR(255) NOT NULL;
ALTER TABLE employees ALTER COLUMN phone NVARCHAR(20) NULL;

-- Default requires dropping and recreating constraint
ALTER TABLE employees DROP CONSTRAINT DF_emp_active;
ALTER TABLE employees ADD CONSTRAINT DF_emp_active DEFAULT 1 FOR is_active;

-- Rename column
EXEC sp_rename 'employees.name', 'full_name', 'COLUMN';

Oracle:

-- Modify column
ALTER TABLE employees MODIFY (salary NUMBER(12, 2));

-- Multiple modifications
ALTER TABLE employees MODIFY (
    salary NUMBER(12, 2) NOT NULL,
    email VARCHAR2(300)
);

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;

SQLite:

-- SQLite has very limited ALTER TABLE support
-- Cannot modify columns directly
-- Must create new table and copy data

-- Rename column (3.25.0+)
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- For type changes, need to recreate table:
-- 1. Create new table with desired schema
-- 2. Copy data
-- 3. Drop old table
-- 4. Rename new table

BigQuery:

-- BigQuery has limited column modification
-- Can only relax column mode (REQUIRED -> NULLABLE)

-- Cannot change data type
-- Cannot rename column (must select into new table)

-- Drop NOT NULL (relax mode)
ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;

-- Set OPTIONS
ALTER TABLE employees ALTER COLUMN email
    SET OPTIONS(description="Employee email address");

Snowflake:

-- Change data type
ALTER TABLE employees ALTER COLUMN salary SET DATA TYPE NUMBER(12, 2);

-- Set/drop NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
ALTER TABLE employees ALTER COLUMN phone DROP NOT NULL;

-- Change default
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT true;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;

Dropping Columns

PostgreSQL:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE employees
    DROP COLUMN phone,
    DROP COLUMN fax;

-- Drop with CASCADE (drops dependent objects)
ALTER TABLE employees DROP COLUMN department_id CASCADE;

-- Drop if exists
ALTER TABLE employees DROP COLUMN IF EXISTS phone;

MySQL:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE employees
    DROP COLUMN phone,
    DROP COLUMN fax;

SQL Server:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE employees DROP COLUMN phone, fax;

-- Must drop dependent constraints first
ALTER TABLE employees DROP CONSTRAINT DF_emp_phone;
ALTER TABLE employees DROP COLUMN phone;

Oracle:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE employees DROP (phone, fax);

-- Set unused (logical delete, faster)
ALTER TABLE employees SET UNUSED (phone);
ALTER TABLE employees DROP UNUSED COLUMNS;

SQLite:

-- SQLite cannot drop columns before version 3.35.0

-- SQLite 3.35.0+:
ALTER TABLE employees DROP COLUMN phone;

-- Older versions require table recreation

BigQuery:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- BigQuery doesn't support dropping multiple columns in one statement
ALTER TABLE employees DROP COLUMN fax;

Snowflake:

-- Drop single column
ALTER TABLE employees DROP COLUMN phone;

-- Drop multiple columns
ALTER TABLE employees DROP COLUMN phone, fax;

Constraints

Primary Key

PostgreSQL:

-- During CREATE TABLE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

-- Or with constraint name
CREATE TABLE users (
    id SERIAL,
    username VARCHAR(50),
    CONSTRAINT pk_users PRIMARY KEY (id)
);

-- Composite primary key
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
);

-- Add to existing table
ALTER TABLE users ADD PRIMARY KEY (id);
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT pk_users;

MySQL:

-- During CREATE TABLE
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- Composite primary key
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

-- Add to existing table
ALTER TABLE users ADD PRIMARY KEY (id);

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

SQL Server:

-- During CREATE TABLE
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(50)
);

-- With constraint name
CREATE TABLE users (
    id INT IDENTITY(1,1),
    username NVARCHAR(50),
    CONSTRAINT PK_users PRIMARY KEY (id)
);

-- Add to existing table
ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (id);

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT PK_users;

Oracle:

-- During CREATE TABLE
CREATE TABLE users (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50)
);

-- Add to existing table
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT pk_users;
ALTER TABLE users DROP PRIMARY KEY;  -- If no name specified

BigQuery & Snowflake:

-- Primary keys are not enforced in BigQuery
-- They are metadata only for query optimization

-- BigQuery
CREATE TABLE users (
    id INT64 NOT NULL,
    username STRING
)
PRIMARY KEY (id) NOT ENFORCED;

-- Snowflake (enforced in Enterprise edition with constraints enabled)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50)
);

Foreign Key

PostgreSQL:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE
);

-- With actions
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

-- Composite foreign key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (order_id, product_id)
        REFERENCES order_products(order_id, product_id)
);

-- Add to existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Deferrable constraints (checked at transaction end)
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    DEFERRABLE INITIALLY DEFERRED;

MySQL:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;  -- InnoDB required for FK

-- Add to existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

SQL Server:

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    CONSTRAINT FK_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

-- Add to existing table
ALTER TABLE orders
    ADD CONSTRAINT FK_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

Oracle:

CREATE TABLE orders (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
);

-- Deferrable constraints
CREATE TABLE orders (
    id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        DEFERRABLE INITIALLY DEFERRED
);

SQLite:

-- Foreign keys must be enabled
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION,
    order_date TEXT
);

-- Cannot add FK to existing table
-- Must recreate table

BigQuery & Snowflake:

-- BigQuery: Foreign keys not enforced (metadata only)
CREATE TABLE orders (
    id INT64,
    customer_id INT64,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) NOT ENFORCED
);

-- Snowflake: Not enforced by default
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CHECK Constraints

PostgreSQL:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2) CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    status VARCHAR(20) CHECK (status IN ('active', 'discontinued')),
    -- Table-level check
    CONSTRAINT chk_price_stock CHECK (price > 0 OR stock = 0)
);

-- Add to existing table
ALTER TABLE products
    ADD CONSTRAINT chk_price CHECK (price >= 0);

MySQL:

-- MySQL 8.0.16+ supports CHECK constraints
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price >= 0),
    stock INT CHECK (stock >= 0),
    status VARCHAR(20) CHECK (status IN ('active', 'discontinued'))
);

-- Older MySQL versions: use triggers instead

SQL Server:

CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100),
    price DECIMAL(10, 2) CONSTRAINT CHK_price CHECK (price >= 0),
    stock INT CONSTRAINT CHK_stock CHECK (stock >= 0),
    status NVARCHAR(20) CHECK (status IN ('active', 'discontinued'))
);

-- Add to existing table
ALTER TABLE products
    ADD CONSTRAINT CHK_price CHECK (price >= 0);

Oracle:

CREATE TABLE products (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100),
    price NUMBER(10, 2) CHECK (price >= 0),
    stock NUMBER CHECK (stock >= 0),
    status VARCHAR2(20) CHECK (status IN ('active', 'discontinued'))
);

SQLite:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    price REAL CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    status TEXT CHECK (status IN ('active', 'discontinued'))
);

Snowflake:

CREATE TABLE products (
    id INTEGER AUTOINCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price NUMBER(10, 2) CONSTRAINT chk_price CHECK (price >= 0),
    stock INTEGER CONSTRAINT chk_stock CHECK (stock >= 0),
    status VARCHAR(20) CHECK (status IN ('active', 'discontinued'))
);

Indexes

Creating Indexes

PostgreSQL:

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Partial index (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Different index types
CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('english', name));
CREATE INDEX idx_location_gist ON locations USING gist(coordinates);

-- Concurrent index creation (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

MySQL:

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Full-text index
CREATE FULLTEXT INDEX idx_products_description ON products(description);

-- Spatial index
CREATE SPATIAL INDEX idx_locations_point ON locations(coordinates);

-- Index with length prefix
CREATE INDEX idx_users_name ON users(name(20));

-- Add index via ALTER TABLE
ALTER TABLE users ADD INDEX idx_email (email);

SQL Server:

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Filtered index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

-- Covering index (INCLUDE columns)
CREATE INDEX idx_orders_customer
    ON orders(customer_id)
    INCLUDE (order_date, total_amount);

-- Columnstore index
CREATE COLUMNSTORE INDEX idx_sales_columnstore ON sales;

-- Online index creation
CREATE INDEX idx_users_email ON users(email) WITH (ONLINE = ON);

Oracle:

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Function-based index
CREATE INDEX idx_users_upper_email ON users(UPPER(email));

-- Bitmap index (for low-cardinality columns)
CREATE BITMAP INDEX idx_users_country ON users(country);

-- Reverse key index
CREATE INDEX idx_orders_id ON orders(order_id) REVERSE;

SQLite:

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

BigQuery:

-- BigQuery doesn't support explicit indexes
-- Uses automatic indexing and column pruning

-- Use clustering instead for performance
CREATE TABLE users
CLUSTER BY email AS
SELECT * FROM users_source;

-- Partitioning for time-based queries
CREATE TABLE orders
PARTITION BY DATE(order_date)
CLUSTER BY customer_id AS
SELECT * FROM orders_source;

Snowflake:

-- Snowflake doesn't support explicit indexes
-- Uses automatic micro-partitioning

-- Use clustering keys for frequently filtered columns
ALTER TABLE orders CLUSTER BY (customer_id, order_date);

-- Automatic clustering (Enterprise edition)
ALTER TABLE orders RESUME RECLUSTER;

DML Differences

INSERT

Single Row Insert

PostgreSQL:

-- Basic insert
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', CURRENT_TIMESTAMP);

-- Insert with RETURNING clause
INSERT INTO users (username, email)
VALUES ('jane_doe', 'jane@example.com')
RETURNING id, created_at;

-- Insert with DEFAULT keyword
INSERT INTO users (username, email, is_active)
VALUES ('bob', 'bob@example.com', DEFAULT);

MySQL:

-- Basic insert
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', CURRENT_TIMESTAMP);

-- Insert and get auto-increment value
INSERT INTO users (username, email)
VALUES ('jane_doe', 'jane@example.com');
SELECT LAST_INSERT_ID();

-- Insert with explicit DEFAULT
INSERT INTO users (username, email, is_active)
VALUES ('bob', 'bob@example.com', DEFAULT);

SQL Server:

-- Basic insert
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', GETDATE());

-- Insert with OUTPUT clause
INSERT INTO users (username, email)
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('jane_doe', 'jane@example.com');

-- Insert with DEFAULT
INSERT INTO users (username, email, is_active)
VALUES ('bob', 'bob@example.com', DEFAULT);

Oracle:

-- Basic insert
INSERT INTO users (id, username, email, created_at)
VALUES (users_seq.NEXTVAL, 'john_doe', 'john@example.com', SYSTIMESTAMP);

-- With IDENTITY column (12c+)
INSERT INTO users (username, email)
VALUES ('jane_doe', 'jane@example.com');

-- Insert with RETURNING clause
INSERT INTO users (username, email)
VALUES ('bob', 'bob@example.com')
RETURNING id INTO :id_variable;

SQLite:

-- Basic insert
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', DATETIME('now'));

-- Get last inserted ID
INSERT INTO users (username, email)
VALUES ('jane_doe', 'jane@example.com');
SELECT last_insert_rowid();

BigQuery:

-- Basic insert
INSERT INTO users (id, username, email, created_at)
VALUES (1, 'john_doe', 'john@example.com', CURRENT_TIMESTAMP());

-- BigQuery is optimized for bulk inserts, not single rows

Snowflake:

-- Basic insert
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', CURRENT_TIMESTAMP());

-- Multiple rows
INSERT INTO users (username, email)
VALUES
    ('jane_doe', 'jane@example.com'),
    ('bob', 'bob@example.com');

Bulk Insert

PostgreSQL:

-- Multiple values
INSERT INTO users (username, email)
VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- Insert from SELECT
INSERT INTO users_archive (id, username, email)
SELECT id, username, email
FROM users
WHERE created_at < CURRENT_DATE - INTERVAL '1 year';

-- COPY for bulk loading (fastest)
COPY users (username, email)
FROM '/path/to/file.csv'
WITH (FORMAT csv, HEADER true);

MySQL:

-- Multiple values
INSERT INTO users (username, email)
VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- Insert from SELECT
INSERT INTO users_archive (id, username, email)
SELECT id, username, email
FROM users
WHERE created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- LOAD DATA for bulk loading
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(username, email);

SQL Server:

-- Multiple values
INSERT INTO users (username, email)
VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- Insert from SELECT
INSERT INTO users_archive (id, username, email)
SELECT id, username, email
FROM users
WHERE created_at < DATEADD(YEAR, -1, GETDATE());

-- BULK INSERT for file loading
BULK INSERT users
FROM 'C:\path\to\file.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

BigQuery:

-- Multiple values (use sparingly)
INSERT INTO users (id, username, email)
VALUES
    (1, 'user1', 'user1@example.com'),
    (2, 'user2', 'user2@example.com'),
    (3, 'user3', 'user3@example.com');

-- Insert from SELECT (preferred for BigQuery)
INSERT INTO users_archive (id, username, email)
SELECT id, username, email
FROM users
WHERE created_at < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR);

-- Load from Cloud Storage (recommended)
LOAD DATA INTO users
FROM FILES (
    format = 'CSV',
    uris = ['gs://bucket/file.csv']
);

Snowflake:

-- Multiple values
INSERT INTO users (username, email)
VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- Insert from SELECT
INSERT INTO users_archive
SELECT id, username, email
FROM users
WHERE created_at < DATEADD(YEAR, -1, CURRENT_DATE());

-- Copy from stage (recommended for bulk)
COPY INTO users
FROM @my_stage/file.csv
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"');

UPSERT (Insert or Update)

PostgreSQL:

-- INSERT ... ON CONFLICT (9.5+)
INSERT INTO users (id, username, email, login_count)
VALUES (1, 'john', 'john@example.com', 1)
ON CONFLICT (id)
DO UPDATE SET
    login_count = users.login_count + 1,
    last_login = CURRENT_TIMESTAMP;

-- ON CONFLICT DO NOTHING
INSERT INTO users (username, email)
VALUES ('john', 'john@example.com')
ON CONFLICT (username) DO NOTHING;

-- Multiple conflict targets
INSERT INTO products (sku, name, price)
VALUES ('ABC123', 'Product', 19.99)
ON CONFLICT (sku)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = CURRENT_TIMESTAMP
WHERE products.price != EXCLUDED.price;

MySQL:

-- INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO users (id, username, email, login_count)
VALUES (1, 'john', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = CURRENT_TIMESTAMP();

-- INSERT IGNORE (inserts only if no conflict)
INSERT IGNORE INTO users (username, email)
VALUES ('john', 'john@example.com');

-- REPLACE (deletes old row, inserts new)
REPLACE INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com');

-- Using VALUES() to reference new values
INSERT INTO products (id, name, price)
VALUES (1, 'Product', 19.99)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

SQL Server:

-- MERGE statement
MERGE INTO users AS target
USING (SELECT 1 AS id, 'john' AS username, 'john@example.com' AS email) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET
        username = source.username,
        login_count = target.login_count + 1
WHEN NOT MATCHED THEN
    INSERT (id, username, email, login_count)
    VALUES (source.id, source.username, source.email, 1);

-- Merge from another table
MERGE INTO users AS target
USING users_staging AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET username = source.username
WHEN NOT MATCHED THEN INSERT (id, username) VALUES (source.id, source.username);

Oracle:

-- MERGE statement
MERGE INTO users target
USING (SELECT 1 AS id, 'john' AS username, 'john@example.com' AS email FROM dual) source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET
        username = source.username,
        login_count = target.login_count + 1
WHEN NOT MATCHED THEN
    INSERT (id, username, email, login_count)
    VALUES (source.id, source.username, source.email, 1);

SQLite:

-- INSERT OR REPLACE
INSERT OR REPLACE INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com');

-- INSERT OR IGNORE
INSERT OR IGNORE INTO users (username, email)
VALUES ('john', 'john@example.com');

-- UPSERT clause (3.24.0+)
INSERT INTO users (id, username, email, login_count)
VALUES (1, 'john', 'john@example.com', 1)
ON CONFLICT (id)
DO UPDATE SET
    login_count = login_count + 1;

BigQuery:

-- MERGE statement
MERGE INTO users AS target
USING users_staging AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET
        username = source.username,
        updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (id, username, email, created_at)
    VALUES (source.id, source.username, source.email, CURRENT_TIMESTAMP());

-- INSERT with ignore errors
INSERT INTO users (id, username, email)
SELECT id, username, email
FROM users_staging
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) = 1;

Snowflake:

-- MERGE statement
MERGE INTO users AS target
USING users_staging AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET
        username = source.username,
        login_count = target.login_count + 1
WHEN NOT MATCHED THEN
    INSERT (id, username, email, login_count)
    VALUES (source.id, source.username, source.email, 1);

UPDATE

PostgreSQL:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Multiple columns
UPDATE users
SET
    email = 'newemail@example.com',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- UPDATE with FROM clause
UPDATE users u
SET is_premium = true
FROM orders o
WHERE u.id = o.customer_id
    AND o.total > 1000;

-- UPDATE with RETURNING
UPDATE users
SET login_count = login_count + 1
WHERE username = 'john'
RETURNING id, login_count, last_login;

MySQL:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Multiple table update
UPDATE users u
INNER JOIN orders o ON u.id = o.customer_id
SET u.is_premium = 1
WHERE o.total > 1000;

-- UPDATE with LIMIT
UPDATE users
SET is_active = 0
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;

-- UPDATE with ORDER BY and LIMIT
UPDATE products
SET discount = 0.1
WHERE category = 'electronics'
ORDER BY created_at DESC
LIMIT 10;

SQL Server:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- UPDATE with FROM clause
UPDATE u
SET u.is_premium = 1
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
WHERE o.total > 1000;

-- UPDATE with OUTPUT
UPDATE users
SET login_count = login_count + 1
OUTPUT INSERTED.id, INSERTED.login_count
WHERE username = 'john';

-- UPDATE with TOP
UPDATE TOP (100) users
SET is_active = 0
WHERE last_login < DATEADD(YEAR, -1, GETDATE());

Oracle:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Correlated subquery update
UPDATE users u
SET is_premium = 1
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = u.id AND o.total > 1000
);

-- UPDATE with RETURNING
UPDATE users
SET login_count = login_count + 1
WHERE username = 'john'
RETURNING id, login_count INTO :id_var, :count_var;

SQLite:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- UPDATE with FROM clause (3.33.0+)
UPDATE users
SET is_premium = 1
FROM orders
WHERE users.id = orders.customer_id
    AND orders.total > 1000;

-- Older SQLite versions use subquery
UPDATE users
SET is_premium = 1
WHERE id IN (
    SELECT customer_id FROM orders WHERE total > 1000
);

BigQuery:

-- Basic update
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

-- UPDATE with FROM clause
UPDATE users u
SET is_premium = true
FROM orders o
WHERE u.id = o.customer_id AND o.total > 1000;

-- Note: BigQuery charges for entire table scan
-- Consider INSERT/SELECT into new table instead

Snowflake:

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- UPDATE with FROM clause
UPDATE users
SET is_premium = TRUE
FROM orders
WHERE users.id = orders.customer_id
    AND orders.total > 1000;

-- Multi-table update
UPDATE users u
SET u.total_spent = o.total
FROM (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
) o
WHERE u.id = o.customer_id;

DELETE

PostgreSQL:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with USING clause
DELETE FROM users u
USING orders o
WHERE u.id = o.customer_id
    AND o.status = 'cancelled';

-- DELETE with RETURNING
DELETE FROM users
WHERE last_login < CURRENT_DATE - INTERVAL '1 year'
RETURNING id, username;

-- TRUNCATE (faster, resets sequences)
TRUNCATE TABLE users;
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

MySQL:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- Multi-table delete
DELETE u
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
WHERE o.status = 'cancelled';

-- DELETE with LIMIT
DELETE FROM users
WHERE is_active = 0
LIMIT 1000;

-- DELETE with ORDER BY
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 10000;

-- TRUNCATE
TRUNCATE TABLE users;

SQL Server:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with FROM/JOIN
DELETE u
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
WHERE o.status = 'cancelled';

-- DELETE with OUTPUT
DELETE FROM users
OUTPUT DELETED.id, DELETED.username
WHERE last_login < DATEADD(YEAR, -1, GETDATE());

-- DELETE with TOP
DELETE TOP (1000) FROM users WHERE is_active = 0;

-- TRUNCATE
TRUNCATE TABLE users;

Oracle:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with subquery
DELETE FROM users
WHERE id IN (
    SELECT customer_id FROM orders WHERE status = 'cancelled'
);

-- TRUNCATE
TRUNCATE TABLE users;

-- TRUNCATE with options
TRUNCATE TABLE users DROP STORAGE;
TRUNCATE TABLE users REUSE STORAGE;

SQLite:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with subquery
DELETE FROM users
WHERE id IN (
    SELECT customer_id FROM orders WHERE status = 'cancelled'
);

-- SQLite doesn't support LIMIT on DELETE before 3.35.0
-- Workaround using rowid:
DELETE FROM users
WHERE rowid IN (
    SELECT rowid FROM users WHERE is_active = 0 LIMIT 1000
);

-- TRUNCATE equivalent
DELETE FROM users;
VACUUM;

BigQuery:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with subquery
DELETE FROM users
WHERE id IN (
    SELECT customer_id FROM orders WHERE status = 'cancelled'
);

-- TRUNCATE TABLE
TRUNCATE TABLE users;

-- Note: Deletes are expensive in BigQuery
-- Consider partitioning and dropping partitions instead

Snowflake:

-- Basic delete
DELETE FROM users WHERE id = 1;

-- DELETE with FROM clause
DELETE FROM users
USING orders
WHERE users.id = orders.customer_id
    AND orders.status = 'cancelled';

-- TRUNCATE
TRUNCATE TABLE users;

-- Time travel recovery
-- Recover deleted data within retention period
INSERT INTO users
SELECT * FROM users AT(OFFSET => -3600);  -- 1 hour ago

Key Takeaways

DDL Summary

1. Auto-increment: Major syntax variation across databases

   • PostgreSQL: SERIAL, IDENTITY
   • MySQL: AUTO_INCREMENT
   • SQL Server: IDENTITY(1,1)
   • Oracle: GENERATED AS IDENTITY
   • Snowflake: AUTOINCREMENT

2. ALTER TABLE: Significant differences in column modification

   • PostgreSQL/Snowflake: Granular control (SET/DROP)
   • MySQL: MODIFY/CHANGE syntax
   • SQLite: Very limited (table recreation often needed)
   • BigQuery: Very limited (cannot change types)

3. Constraints: Enforcement varies

   • Traditional RDBMS: Full enforcement
   • BigQuery/Snowflake: Often metadata only
   • SQLite: Foreign keys must be enabled

4. Indexes: Different types and strategies

   • PostgreSQL: Rich index types (GIN, GiST, BRIN)
   • SQL Server: Columnstore, included columns
   • BigQuery/Snowflake: Automatic, use clustering instead

DML Summary

1. INSERT: RETURNING/OUTPUT clauses differ

   • PostgreSQL/Oracle: RETURNING
   • SQL Server: OUTPUT INSERTED
   • MySQL: LAST_INSERT_ID()

2. UPSERT: Different syntax patterns

   • PostgreSQL: ON CONFLICT
   • MySQL: ON DUPLICATE KEY UPDATE, REPLACE
   • SQL Server/Oracle/BigQuery/Snowflake: MERGE
   • SQLite: INSERT OR REPLACE, ON CONFLICT

3. UPDATE/DELETE: Join syntax varies

   • PostgreSQL: FROM/USING clause
   • MySQL: Direct JOIN in UPDATE/DELETE
   • SQL Server: FROM clause
   • Oracle: Correlated subqueries

Best Practices for Portability

1. Use standard SQL when possible

   • Prefer COALESCE over NVL or IFNULL
   • Use CASE WHEN instead of database-specific functions
   • Stick to common data types (INTEGER, VARCHAR, DATE)

2. Abstract database-specific features

   • Use ORM or abstraction layer
   • Create database-specific migration files
   • Document non-portable code

3. Test on target databases

   • Especially when migrating
   • Watch for subtle semantic differences
   • Performance characteristics may vary

4. Plan for constraints

   • Not all databases enforce all constraints
   • May need application-level validation
   • Consider using database checks for data integrity

See Also

• Feature Matrix - Feature comparison across engines
• Dialect Differences - Quick syntax reference
• Migration Guides - Step-by-step migration guides
• SQL Standards - SQL standard specifications