MySQL vs Oracle SQL Command Cheat Sheet
Operation MySQL Syntax Oracle Syntax Notes / Differences
Select all rows SELECT * FROM table_name; SELECT * FROM table_name; Same syntax.
SELECT * FROM table_name FETCH FIRST 5 ROWS ONLY;
OR
Select top N rows SELECT * FROM table_name LIMIT 5; SELECT * FROM table_name WHERE ROWNUM
MySQL uses
<= 5;LIMIT; Oracle uses FETCH or RO
No direct command — Use schemas/users:
CREATE USER dbuser IDENTIFIED BY password;
Create Database CREATE DATABASE dbname; GRANT CREATE SESSION, CREATE TABLE Oracle
TOdoesn’t
dbuser;have databases like MySQL; sch
Use Database USE dbname; ALTER SESSION SET CURRENT_SCHEMA
Oracle
= schema_name;
doesn’t switch databases — you connec
Create Table CREATE TABLE t (id INT, name VARCHAR(50));
CREATE TABLE t (id NUMBER, name VARCHAR2(50));
Oracle uses NUMBER and VARCHAR2.
Drop Table DROP TABLE t; DROP TABLE t; Same.
Describe Table DESCRIBE table_name; OR SHOW COLUMNS
DESC FROM
table_name;
table_name; Similar but keywords differ.
Add Column ALTER TABLE t ADD colname INT; ALTER TABLE t ADD (colname NUMBER);Oracle allows parentheses for multiple columns
Modify Column ALTER TABLE t MODIFY COLUMN nameALTER
VARCHAR(20);
TABLE t MODIFY (name VARCHAR2(20));
MySQL uses MODIFY COLUMN, Oracle omits
Change Column Name ALTER TABLE t CHANGE oldname newname
ALTER
VARCHAR(20);
TABLE t RENAME COLUMN oldname
Completely
TO newname;
different syntax.
Drop Column ALTER TABLE t DROP COLUMN colname;
ALTER TABLE t DROP COLUMN colname;
Same syntax.
Rename Table RENAME TABLE oldname TO newname; ALTER TABLE oldname RENAME TO newname;
Different command style.
Primary Key at creation id INT PRIMARY KEY id NUMBER PRIMARY KEY Only type names differ.
Add Primary Key ALTER TABLE t ADD PRIMARY KEY (id);ALTER TABLE t ADD CONSTRAINT pk_name
Oracle
PRIMARY
requires constraint
KEY (id); name.
Auto Increment id INT AUTO_INCREMENT PRIMARY KEY
id NUMBER GENERATED BY DEFAULT AS
Oracle
IDENTITY
uses IDENTITY
PRIMARYcolumns.
KEY
Unsigned age INT UNSIGNED Not supported Oracle only has signed numbers.
String concatenation CONCAT(col1, col2) col1 || col2 Operators differ.
Current date/time NOW() SYSDATE Function name differs.
String length LENGTH(str) LENGTH(str) Same.
Substring SUBSTRING(str, 2, 5) SUBSTR(str, 2, 5) Oracle uses SUBSTR.
Limit results LIMIT n OFFSET m OFFSET m ROWS FETCH NEXT n ROWSOracle
ONLY12c+ supports OFFSET/FETCH; older u
Show tables SHOW TABLES; SELECT table_name FROM user_tables; Oracle requires querying dictionary views.
Show databases SHOW DATABASES; SELECT username AS schema_name FROM
Oracle
all_users;
uses schemas/users instead of database
Check version SELECT VERSION(); SELECT * FROM v$version; Different system functions/views.
-- single line -- single line
Comments /* multi-line */ /* multi-line */ Same syntax.
If exists (table) DROP TABLE IF EXISTS t; DROP TABLE t PURGE; Oracle errors if table doesn’t exist — no IF EXIS
Insert data INSERT INTO t (id, name) VALUES (1, 'John');
INSERT INTO t (id, name) VALUES (1, 'John');
Same basic syntax.
Update data UPDATE t SET name = 'Mike' WHERE id =UPDATE
1; t SET name = 'Mike' WHERE id =Same
1; syntax.
Delete data DELETE FROM t WHERE id = 1; DELETE FROM t WHERE id = 1; Same syntax.
Truncate table TRUNCATE TABLE t; TRUNCATE TABLE t; Same syntax.
Inner join SELECT * FROM a INNER JOIN b ON a.idSELECT
= b.id; * FROM a INNER JOIN b ON a.idSame
= b.id;syntax.
Operation MySQL Syntax Oracle Syntax Notes / Differences
Left join SELECT * FROM a LEFT JOIN b ON a.id =
SELECT
b.id; * FROM a LEFT JOIN b ON a.id =
Same
b.id; syntax.
Right join SELECT * FROM a RIGHT JOIN b ON a.idSELECT
= b.id; * FROM a RIGHT JOIN b ON a.idSame
= b.id;syntax.
Full outer join SELECT * FROM a LEFT JOIN b ON ... UNION
SELECT
SELECT
* FROM
* FROM
a FULL
a RIGHT
OUTERJOIN
JOINbbON
Oracle
ON...;
...; supports FULL OUTER JOIN directly; M
Create index CREATE INDEX idx_name ON t(col1); CREATE INDEX idx_name ON t(col1); Same syntax.
Drop index DROP INDEX idx_name ON t; DROP INDEX idx_name; Oracle does not use ON table_name.
Sequences AUTO_INCREMENT column CREATE SEQUENCE seq_name START WITH
MySQL1 uses
INCREMENT
AUTO_INCREMENT;
BY 1; Oracle uses
