Oracle SQL vs Other SQLs - 60+ Differences
1. Dual Table Requirement
Oracle requires 'FROM DUAL' in SELECT queries without a table. Others do not.
2. String Concatenation
Oracle uses '||', MySQL uses CONCAT(), SQL Server uses '+'.
3. Row Limiting
Oracle: FETCH FIRST, MySQL: LIMIT, SQL Server: TOP.
4. SYSDATE vs CURRENT_DATE
Oracle uses SYSDATE, MySQL uses CURRENT_DATE().
5. Auto Increment
Oracle uses SEQUENCE, others use AUTO_INCREMENT or IDENTITY.
6. NULL Substitution
Oracle: NVL, MySQL: IFNULL, Standard: COALESCE.
7. String Functions
LENGTH, SUBSTR, CHAR_LENGTH differ slightly across systems.
8. Date Addition
Oracle: SYSDATE + 7, MySQL: NOW() + INTERVAL 7 DAY.
9. Alias in ORDER BY
Oracle supports using SELECT alias in ORDER BY.
10. Pagination
Oracle SQL vs Other SQLs - 60+ Differences
Oracle (old): ROWNUM, Oracle 12c+: OFFSET FETCH, MySQL: LIMIT OFFSET.
11. DECODE vs CASE
Oracle uses DECODE, others prefer CASE.
12. MERGE vs UPSERT
Oracle uses MERGE, others use INSERT ... ON DUPLICATE/CONFLICT.
13. FULL OUTER JOIN
Supported in Oracle and PostgreSQL. MySQL needs workaround.
14. REGEXP
Oracle uses REGEXP_LIKE, MySQL uses REGEXP.
15. Sequences
Native in Oracle. MySQL added support in 8.0+.
16. PL/SQL Blocks
Only Oracle supports full anonymous PL/SQL blocks.
17. BOOLEAN Type
Not available in Oracle tables. Available in others.
18. Triggers on Views
Oracle supports INSTEAD OF triggers. MySQL does not.
19. INSTR vs LOCATE
Oracle: INSTR, MySQL: LOCATE.
Oracle SQL vs Other SQLs - 60+ Differences
20. NULL Comparisons
All treat NULL = NULL as UNKNOWN, use IS NULL instead.
21. Recursive Queries
All support CTEs now, but Oracle uses 'CONNECT BY' too.
22. UPDATE with JOIN
Syntax differs. MySQL allows direct JOIN in UPDATE.
23. Function Overloading
Only Oracle allows overloading stored functions.
24. Materialized Views
Oracle & PostgreSQL support. MySQL does not.
25. Hierarchical Queries
Oracle uses CONNECT BY; others use recursive CTEs.
26. Data Type Differences
e.g., VARCHAR2 (Oracle), TEXT/BLOB vary across systems.
27. Locking Hints
Oracle: FOR UPDATE, SQL Server: WITH(ROWLOCK, UPDLOCK).
28. Percent-based Row Limiting
Oracle supports FETCH FIRST 20 PERCENT.
29. Stored Proc Syntax
Oracle SQL vs Other SQLs - 60+ Differences
Oracle: IN/OUT/IN OUT, MySQL has basic support.
30. Temp Tables
Syntax and behavior differ across Oracle, MySQL, SQL Server.
31. Exception Handling
Oracle: EXCEPTION block, MySQL: DECLARE HANDLER.
32. User-defined Types
Oracle supports OBJECT types. Others do not.
33. Packages
Only Oracle supports procedure/function packages.
34. MINUS vs EXCEPT
Oracle: MINUS. Others: EXCEPT.
35. Invisible Columns
Only Oracle supports them.
36. Read Consistency
Oracle has best-in-class MVCC using undo segments.
37. Compressed Tables
Oracle has table-level compression hints.
38. Parallel Hints
Oracle allows SQL-level parallel execution hints.
Oracle SQL vs Other SQLs - 60+ Differences
39. Window Functions
All modern DBs support now; Oracle was earliest.
40. RETURNING Clause
Oracle supports RETURNING INTO. Others have limited support.
41. SYNONYMS
Oracle supports CREATE SYNONYM. Others don't.
42. Edition-based Redefinition
Oracle-only feature for zero-downtime upgrades.
43. Flashback Queries
Only Oracle supports querying past versions by timestamp.
44. Virtual Columns
Oracle, MySQL support; syntax varies.
45. Fine-Grained Access Control
Oracle has DBMS_RLS for row-level security.
46. Outer Join (+) Syntax
Only Oracle supports legacy (+) syntax.
47. Data Redaction
Oracle Enterprise has DBMS_REDACT.
48. Advanced Queuing
Oracle SQL vs Other SQLs - 60+ Differences
Oracle has built-in pub-sub queues.
49. PL/SQL Labels
Oracle supports labeled code blocks.
50. Profiles
Oracle: CPU, login control via profiles. Others lack this granularity.
