SQL portability is the ability to write SQL that works across different databases (MySQL, PostgreSQL, SQL Server, Oracle, SQLite). The challenge: All databases support ANSI SQL standard (SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY), but each adds proprietary extensions (MySQL: LIMIT, PostgreSQL: RETURNING, SQL Server: TOP, Oracle: ROWNUM). Using proprietary features = vendor lock-in (can't switch databases without rewriting SQL). Solution: (1) Stick to ANSI SQL standard (portable but limited), (2) Use abstraction layer (ORM, query builder) that generates database-specific SQL, (3) Write conditional SQL (detect database, generate appropriate syntax). Real-world: GitLab uses ActiveRecord ORM (Ruby) to support PostgreSQL and MySQL with same code. WordPress uses wpdb abstraction layer (PHP) to support MySQL and MariaDB. Metabase uses HoneySQL (Clojure) to support 10+ databases. Trade-off: Portability vs performance. Standard SQL is slower than optimized database-specific SQL. Example: PostgreSQL RETURNING clause (1 query) vs standard SQL (2 queries: INSERT + SELECT).

**ANSI SQL Standard - The Portable Core:**

ANSI SQL (American National Standards Institute) defines standard SQL syntax that all databases support:

- **Data retrieval**: SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY

- **Data modification**: INSERT, UPDATE, DELETE

- **Data definition**: CREATE TABLE, ALTER TABLE, DROP TABLE

- **Transactions**: BEGIN, COMMIT, ROLLBACK

- **Constraints**: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK

If you stick to ANSI SQL, your queries work on all databases. But ANSI SQL is limited - no pagination (LIMIT), no auto-increment, no date functions.

**Common Compatibility Issues:**

**1. Pagination (LIMIT vs TOP vs FETCH):**

| Database | Syntax | Example |

|----------|--------|----------|

| MySQL, PostgreSQL, SQLite | LIMIT offset, count | SELECT * FROM users LIMIT 10 OFFSET 20 |

| SQL Server (old) | TOP n | SELECT TOP 10 * FROM users |

| SQL Server (new) | OFFSET/FETCH | SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |

| Oracle (old) | ROWNUM | SELECT * FROM (SELECT * FROM users WHERE ROWNUM <= 30) WHERE ROWNUM > 20 |

| Oracle (new) | OFFSET/FETCH | SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |

Portable solution: Use OFFSET/FETCH (SQL:2008 standard, supported by PostgreSQL 8.4+, SQL Server 2012+, Oracle 12c+). MySQL doesn't support OFFSET/FETCH, so use ORM.

**2. String Concatenation:**

| Database | Syntax | Example |

|----------|--------|----------|

| PostgreSQL, SQLite, Oracle | || | SELECT first_name || ' ' || last_name FROM users |

| SQL Server | + | SELECT first_name + ' ' + last_name FROM users |

| MySQL | CONCAT() | SELECT CONCAT(first_name, ' ', last_name) FROM users |

| All (ANSI SQL) | CONCAT() | SELECT CONCAT(first_name, ' ', last_name) FROM users |

Portable solution: Use CONCAT() function (ANSI SQL, supported by all databases).

**3. Auto-Increment:**

| Database | Syntax | Example |

|----------|--------|----------|

| MySQL | AUTO_INCREMENT | id INT AUTO_INCREMENT PRIMARY KEY |

| PostgreSQL | SERIAL or IDENTITY | id SERIAL PRIMARY KEY or id INT GENERATED ALWAYS AS IDENTITY |

| SQL Server | IDENTITY | id INT IDENTITY(1,1) PRIMARY KEY |

| Oracle | SEQUENCE + TRIGGER | CREATE SEQUENCE user_seq; id NUMBER DEFAULT user_seq.NEXTVAL |

| SQLite | AUTOINCREMENT | id INTEGER PRIMARY KEY AUTOINCREMENT |

Portable solution: Use ORM (generates appropriate syntax per database) or IDENTITY (SQL:2003 standard, supported by PostgreSQL 10+, SQL Server, Oracle 12c+).

**4. Date/Time Functions:**

| Database | Current timestamp | Date arithmetic |

|----------|-------------------|------------------|

| MySQL | NOW() or CURRENT_TIMESTAMP | DATE_ADD(date, INTERVAL 1 DAY) |