MySQL vs PostgreSQL vs SQLite vs SQL Server

Why the differences matter

SQL is a standard, but every engine adds its own words and leaves out others. These local versions are called dialects. Your SELECT, WHERE, JOIN, GROUP BY and window functions will work almost unchanged across engines. The surprises are in the edges: keys, limits, dates, text and upserts. Knowing them helps in interviews and stops "it worked on my laptop" bugs.

Who makes them and where they are used

• MySQL: owned by Oracle. The Community Edition is open source under the GPL; paid editions add support and tools. Very common for websites and PHP apps (WordPress), and in many Indian startups. MariaDB is a community fork.
• PostgreSQL: open source under the permissive PostgreSQL Licence, built by a global community. Known for strict standards, rich types (JSONB, arrays) and extensions such as PostGIS. A frequent default for new backends.
• SQLite: public domain, a library inside your app with no server. Used in every Android and iOS phone, browsers, desktop apps and small tools. Not meant for many writers at once over a network.
• SQL Server: Microsoft's commercial database, with free Express and Developer editions. Common in banks, government projects and companies built on .NET and Windows.

The comparison table

A few notes on the table. In MySQL, || means OR by default, not "join text", which surprises people coming from PostgreSQL. In SQLite, a column declared INTEGER PRIMARY KEY becomes the row id and fills itself in; adding AUTOINCREMENT only stops old ids from being reused. In SQL Server, OFFSET ... FETCH needs an ORDER BY.

Oracle in one paragraph

Oracle Database, from Oracle Corporation, is the long-time leader in large enterprises, banks and telecom companies. It is commercial (a free edition exists for learning). Its dialect has some habits worth knowing: SELECT needs FROM dual when there is no table, text columns are usually VARCHAR2, an empty string '' is treated as NULL, procedures are written in PL/SQL, and row limits use FETCH FIRST n ROWS ONLY (12c and later) or the older ROWNUM. Check the current Oracle docs for newer features.

Writing portable SQL

Portable SQL runs on many engines with no change. It matters for libraries, tools and teams that might switch engines. A few habits get you most of the way:

• Prefer standard functions: COALESCE instead of IFNULL, ISNULL or NVL; CASE instead of IF() or IIF(); CAST(x AS ...) instead of engine-only casts.
• Use window functions (ROW_NUMBER) for top-N when you must avoid LIMIT and TOP; they work in all four engines today.
• Write explicit JOIN ... ON, and name every column in INSERT.
• Avoid reserved words such as order, user and date as names, so you never need backticks, quotes or brackets.
• Store dates as real date types (or YYYY-MM-DD text in SQLite) and keep date maths in one place.

SELECT name, price
FROM (SELECT name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
      FROM products) AS ranked
WHERE rn <= 3
ORDER BY price DESC;
-- Running Shoes      | 2499
-- Bluetooth Earbuds  | 1999
-- Pressure Cooker 3L | 1450

Common mistakes

• Using || to join text in MySQL and getting 0 or 1. Fix: use CONCAT(), which works in all four engines.
• Assuming 'pune' = 'Pune' behaves the same everywhere. Fix: compare lower(city) = 'pune' when case must not matter, and know your collation.
• Copying LIMIT into SQL Server. Fix: TOP n or OFFSET ... FETCH with ORDER BY.
• Expecting FULL OUTER JOIN in MySQL. Fix: LEFT JOIN UNION RIGHT JOIN.
• Testing on SQLite and deploying on PostgreSQL without running the tests on PostgreSQL. Fix: test on the engine you deploy to.