SQLSQL in different databases
SQL is a standard, and every database product follows most of it. Each also has its own habits, a little like regional forms of one language. This page gathers the differences that beginners meet first, so that you can take what you learn here to whichever database your college or workplace uses.
The lessons on this site run on SQLite. Nearly everything in the first eighteen lessons works unchanged everywhere. The differences cluster in a few places: limiting rows, text functions, dates, automatic id numbers and changing tables.
These notes are a guide written from general knowledge of each product. Products change with each version, so treat your database's own manual as the final word.
The five products
| Product | What it is | Where you meet it |
|---|---|---|
| MySQL | free, open-source server database, owned by Oracle | websites, web hosting, many college courses |
| PostgreSQL | free, open-source server database | newer applications, data analysis |
| SQL Server | Microsoft's database | offices that use Microsoft software |
| Oracle Database | Oracle's commercial database | banks, large companies, government systems |
| SQLite | free database engine that lives in a single file | inside phones, browsers and apps, and this site's practice box |
MariaDB is a close relative of MySQL and shares almost all of its syntax.
The first N rows
| Database | Form |
|---|---|
| SQLite, MySQL, PostgreSQL | SELECT name FROM products ORDER BY price DESC LIMIT 3 |
| SQL Server | SELECT TOP 3 name FROM products ORDER BY price DESC |
| Oracle 12c and later, PostgreSQL | ... ORDER BY price DESC FETCH FIRST 3 ROWS ONLY |
The last form is the one in the SQL standard. SQL Server 2012 and later accepts a close relative, ORDER BY price DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY. Lesson: LIMIT.
Joining text
| Database | Form |
|---|---|
| SQLite, PostgreSQL, Oracle | first_name || ' ' || last_name |
| MySQL | CONCAT(first_name, ' ', last_name) |
| SQL Server | first_name + ' ' + last_name or CONCAT(...) |
In MySQL, || means OR unless a setting is changed, so a query written for PostgreSQL can run there without an error and give wrong results. CONCAT also works in PostgreSQL and Oracle, though Oracle's takes only two values at a time.
Text functions
| Job | SQLite | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Length | LENGTH | CHAR_LENGTH | LENGTH | LEN | LENGTH |
| Part of a text | SUBSTR | SUBSTRING | SUBSTRING | SUBSTRING | SUBSTR |
| Position of a piece | INSTR | INSTR | POSITION | CHARINDEX | INSTR |
| Replace missing value | COALESCE, IFNULL | COALESCE, IFNULL | COALESCE | COALESCE, ISNULL | COALESCE, NVL |
COALESCE, UPPER, LOWER, TRIM, REPLACE and ROUND are the same everywhere. Lesson: functions.
Capital letters in comparisons
Whether 'delhi' = 'Delhi' is true depends on the product and its settings.
| Database | = on text, by default | LIKE, by default |
|---|---|---|
| SQLite | case sensitive | ignores case for A to Z |
| MySQL | ignores case | ignores case |
| PostgreSQL | case sensitive | case sensitive (ILIKE ignores case) |
| SQL Server | ignores case | ignores case |
| Oracle | case sensitive | case sensitive |
To be safe anywhere, compare LOWER(column) with a small-letter value.
Dates
This is where the products differ most. Lesson: dates.
| Job | SQLite | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|---|
| Today's date | date('now') | CURDATE() | CURRENT_DATE | CAST(GETDATE() AS DATE) |
| Year of a date | strftime('%Y', d) | YEAR(d) | EXTRACT(YEAR FROM d) | YEAR(d) |
| Add 7 days | date(d, '+7 days') | DATE_ADD(d, INTERVAL 7 DAY) | d + INTERVAL '7 days' | DATEADD(day, 7, d) |
| Days between | julianday(b) - julianday(a) | DATEDIFF(b, a) | b - a | DATEDIFF(day, a, b) |
Notice that DATEDIFF takes its dates in the opposite order in MySQL and SQL Server. Oracle uses SYSDATE for now, EXTRACT for parts, and plain addition of a number for days.
In every product, write a date in a query as '2026-03-15': year, month, day.
Automatic id numbers
| Database | In CREATE TABLE |
|---|---|
| SQLite | id INTEGER PRIMARY KEY |
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY |
| PostgreSQL | id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, or the older id SERIAL PRIMARY KEY |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
| Oracle 12c and later | id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY |
In all of them you leave the column out of the INSERT and the number is given. Lessons: INSERT and CREATE TABLE.
Quotation marks around names
Text values take single quotation marks in every product. Names of tables and columns that contain spaces or clash with a keyword need quoting, and here the products differ.
| Database | Quoting a name |
|---|---|
| Standard SQL, PostgreSQL, Oracle, SQLite | "order date" |
| MySQL | backticks (the key below Esc) around the name |
| SQL Server | [order date] |
The simplest course is to avoid the need: use names in small letters with underscores, such as order_date.
Division of whole numbers
SELECT 7 / 2 gives 3 in SQLite, PostgreSQL and SQL Server, where dividing two whole numbers drops the fraction. It gives 3.5 in MySQL and Oracle. Write 7 / 2.0, or multiply by 1.0, to get decimals everywhere.
Things SQLite does differently
Because the practice box runs SQLite, it helps to know where it is more relaxed than the server databases.
- Types are not enforced. A column declared
INTEGERwill accept text. Server databases refuse. - No separate date type. Dates are kept as text or numbers.
- Foreign keys are checked only if switched on. Elsewhere they are always checked.
- An ordinary column beside an aggregate without GROUP BY is allowed. Elsewhere it is an error, and the lessons tell you not to rely on it.
ALTER TABLEis limited. It can add, rename and drop columns, but cannot change a column's type.- No users or permissions.
GRANTandREVOKEdo not exist. A SQLite database is a file, and whoever can open the file can use it.
None of this affects what you learn about queries. It only means that the practice box forgives a few things that a server would reject.
Features that arrived late in some products
| Feature | Note |
|---|---|
| Window functions | MySQL from version 8.0; SQLite from 3.25 |
WITH (common table expressions) | MySQL from version 8.0 |
INTERSECT and EXCEPT | MySQL from version 8.0.31; Oracle writes MINUS for EXCEPT |
FULL JOIN | not in MySQL; SQLite from 3.39 |
SELECT without FROM | Oracle before version 23 needs FROM dual |
If a lesson's query fails on an old server at your college, the version is the first thing to check.
Moving between products
Someone who knows SQL well in one product is productive in another within days. The ideas carry over completely: tables, keys, joins, grouping, subqueries, window functions. What changes is a short list of function names and a few keywords. When a query fails after a move:
- Read the error. It usually names the function or keyword that the product does not know.
- Look up the job, not the name. Search the new product's manual for "add days to date" and not for the old function.
- Check the rules on case and on whole-number division, which change results without any error.
Which one should a beginner learn?
Whichever your course or job uses. If you have a free choice, PostgreSQL and MySQL are both free and widely used, and SQLite needs no installation at all. The practice box on this site is enough for all 26 lessons.
This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.