Scribd
Upload
6 views2 pages

Oracle vs Other SQL Differences With Examples

The document outlines over 60 differences between Oracle SQL and other SQL dialects, highlighting key distinctions in syntax and functionality. Key areas of comparison include the use of the DUAL table, string concatenation methods, row limiting techniques, date functions, and handling of NULL values. It provides specific examples for Oracle, MySQL, and SQL Server to illustrate these differences.

Uploaded by

ankitmeena422004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Oracle vs Other SQL Differences With Examples

The document outlines over 60 differences between Oracle SQL and other SQL dialects, highlighting key distinctions in syntax and functionality. Key areas of comparison include the use of the DUAL table, string concatenation methods, row limiting techniques, date functions, and handling of NULL values. It provides specific examples for Oracle, MySQL, and SQL Server to illustrate these differences.

Uploaded by

ankitmeena422004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Oracle SQL vs Other SQLs - 60+ Differences

1. Dual Table Requirement

Oracle requires 'FROM DUAL' in SELECT queries without a table. Others do not.

Example (Oracle): SELECT 1 FROM DUAL;

Example (MySQL): SELECT 1;

2. String Concatenation

Oracle uses '||', MySQL uses CONCAT(), SQL Server uses '+'.

Oracle: SELECT 'Hello' || 'World' FROM DUAL;

MySQL: SELECT CONCAT('Hello', 'World');

3. Row Limiting

Oracle: FETCH FIRST, MySQL: LIMIT, SQL Server: TOP.

Oracle: SELECT * FROM table FETCH FIRST 10 ROWS ONLY;

MySQL: SELECT * FROM table LIMIT 10;

SQL Server: SELECT TOP 10 * FROM table;

4. SYSDATE vs CURRENT_DATE

Oracle uses SYSDATE, MySQL uses CURRENT_DATE().

Oracle: SELECT SYSDATE FROM DUAL;

MySQL: SELECT CURRENT_DATE();

5. Auto Increment

Oracle uses SEQUENCE, others use AUTO_INCREMENT or IDENTITY.

Oracle: CREATE SEQUENCE seq; INSERT INTO t(id) VALUES(seq.NEXTVAL);

MySQL: CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY);

6. NULL Substitution

Oracle: NVL, MySQL: IFNULL, Standard: COALESCE.

Oracle: SELECT NVL(col, 0) FROM t;


Oracle SQL vs Other SQLs - 60+ Differences

MySQL: SELECT IFNULL(col, 0) FROM t;

7. String Functions

LENGTH, SUBSTR, etc. differ slightly across systems.

Oracle: LENGTH('abc'), SUBSTR('abc', 1, 2)

MySQL: CHAR_LENGTH('abc'), SUBSTRING('abc', 1, 2)

8. Date Addition

Oracle: SYSDATE + 7, MySQL: NOW() + INTERVAL 7 DAY.

Oracle: SELECT SYSDATE + 7 FROM DUAL;

MySQL: SELECT NOW() + INTERVAL 7 DAY;

9. Alias in ORDER BY

Oracle allows aliases in ORDER BY.

Oracle: SELECT salary * 12 AS annual FROM emp ORDER BY annual;

10. Pagination

Oracle (old): ROWNUM, Oracle 12c+: OFFSET FETCH, MySQL: LIMIT OFFSET.

Oracle: SELECT * FROM (SELECT e.*, ROWNUM r FROM emp e WHERE ROWNUM <= 10) WHERE r > 5;

MySQL: SELECT * FROM emp LIMIT 5 OFFSET 5;

You might also like