--fred
differences in sql syntax
last updated: 3/21/2001
applies to: oracle 7.3+, ms sql server 6.5+
the following table shows the different syntax used in oracle and ms sql server for the same sql operations:
| description | oracle | ms sql server |
|---|---|---|
| left outer join | where column1 = column2(+) | from table1 left outer join table2 on table1.column1 = table2.column2 note: the following syntax is also supported, but is no longer recommended: where column1 *= column2 |
| right outer join | where column1(+) = column2 | from table1 right outer join table2 on table1.column1 = table2.column2 note: the following syntax is also supported, but is no longer recommended: where column1 =* column2 |
| full outer join | from table1 full outer join table2 on table1.column1 = table2.column2 | |
| select without from | select 'hello world' from dual | select 'hello world' |
| select data into a table | create table as select ... | select ... into |
| intersection of 2 selects | select ... intersect select ... | select ... where exists (select ...) |
| subtraction of 2 selects | select ... minus select ... | select ... where not exists (select ...) |
| insert into a join | insert into select ... | create a view and insert into it. |
| update data in a join | update select... | create a view and insert into it. |
| update one table based on criteria in another table | <not supported> | update table from ... |
| delete rows from one table based on criteria in another table | <not supported> | delete from table from ... |
| drop a column from a table | <not supported until oracle 8i> | alter table table_name drop column column_name |
| readonly view | create view ... with readonly | grant select ... |
| save point | savepoint | save transaction |
| table lock | lock table...in share mode | select...table_name (tablock) |
| exclusive table lock | lock table...in exclusive mode | select...table_name (tablockx) |
| reserving index space | pctfree=0 | fillfactor=100 |
| declaring a local variable | declare varname type; | declare @varname type |
| initializing a local variable | declare varname type := value; | <not supported> |
| declaring a constant | declare varname constant type := value; | <not supported> |
| assigning to a variable | varname := value select value into varname |
set @varname = value select @varname = value |
| assigning to a variable from a cursor | fetch cursorname into varname | fetch next from cursorname into varname |
| declaring a cursor | cursor curname (params) is select ...; |
declare curname cursor for select ... |
| if statement | if ... then elsif ... then else endif |
if ... begin ... end else begin ... end |
| while loop | while ... loop end loop |
while ... begin ... end |
| other loops | for ... end loop loop ... end loop |
<not supported> |
| loop exit | exit, exit when | break, continue |
| print output | dbms_output.put_line | |
| raise error | raise_application_error | raiserror |
| statement terminator | semi-colon (;) | <none required> |
