I’ve been using Oracle DBMS for years, and sometimes it’s hard to differentiate what is an “Oracle built-in” and a standard SQL feature.
In teaching a Database Modeling course at RIT in MySQL I have found out that there are some differences between Oracle and MySQL:
MySQL comparisons are case-insensitive, so
SELECT * FROM table WHERE name='ABC';returns the same results as
SELECT * FROM table WHERE name='abc';
MySQL table names are case-sensitive but only on Linux.
MySQL has a richer set of datatypes, especially for integer types:
bigint, but is missing
CLOBand native(-ish) XML.
Single and double-quotes are allowed for string literals (but cannot be mixed).
Executing queries in an Oracle database are done by specifying the database name in the connect string. MySQL supports the
USEcommand to choose a database to run.
There are a lot fewer functions available than in Oracle (which someone could write a an-oracle-function-a-day series and have a year’s worth of material).
CHECKconstraints are “supported” in DDL but before MySQL 8 are only verified to be syntatically correct.
MINUSis not supported, but can be simulated with
NOT IN (<subquery>)
||isn’t supported, must use
CONCAT(). MySQL allows string literals to be concatenated without operators (
SELECT 'foo' 'bar';).
tables can be aliased by backticks with or without the
ASkeyword because “ and ‘ are both allowed for string literals. Oracle lets you use a
"quoted"value for a table alias without
AS, for example
SELECT 'foo' AS foo_col, 'bar' "bar_col" FROM dual;
nested queries must have a table alias in MySQL even if table name isn’t referenced. For instance,
AS my_salesis mandatory in this statement:
SELECT count(*) from (SELECT distinct * from sales) AS my_sales;
Joins inside nested queries need to have unique column names in MySQL. Oracle will rename the columns (generally by appending “_1” to the duplicate column name) but an error is returned. For instance, given tables STOCKTRADES(cusip, price, trade_date, commission) and BONDTRADES(cusip, price, trade_date, commission). In Oracle,
SELECT count(*) FROM (SELECT * FROM stocktrades UNION SELECT * FROM bondtrades) AS all_trades;
NVL()is not supported, use
Good summary is at this SlideShare but it’s sometimes not clear what the original Oracle analog would be and it’s a little dated.