Oracle MSSQL Compared
Oracle MSSQL Compared
Structured Query Language (SQL)[1]
This article describes SQL and the differences between PL/SQL[2] and MS-SQL.[3]
SQL Snippets[4]
MS-SQL T/SQL Reference[7]
Oracle: Case-Sensitive by default.
MS-SQL: Case-Insensitive by default.
Contents
Operators
Operator | Description | Example |
---|---|---|
= | Equal to | Author = 'Alcott' |
<> or != | Not equal to | Dept <> 'Sales' |
> | Greater than | Hire_Date > '2012-01-31' |
< | Less than | Bonus < 50000.00 |
>= | Greater than or equal | Dependents >= 2 |
<= | Less than or equal | Rate <= 0.05 |
BETWEEN | Between an inclusive range | Cost BETWEEN 100.00 AND 500.00 |
LIKE | Match a character pattern | First_Name LIKE 'Will%' |
IN | Equal to one of multiple possible values | DeptCode IN (101, 103, 209) |
IS or IS NOT | Compare to null (missing data) | Address IS NOT NULL |
Arrays
PL/SQL[8]
Arrays are part of Collections, along with bags, lists, nested tables, sets, and trees.
T/SQL[9]
T/SQL has no array functionality or other storage class type. But it can be improvised with a temporary table.
Conditional (CASE) expressions
- No difference between PL/SQL and T/SQL
SQL has a case/when/then/else/end
expression, which was introduced in SQL-92[10]. In its most general form, which is called a "searched case" in the SQL standard, it works like Else if|else if in other programming languages:
CASE WHEN n > 0 THEN 'positive' WHEN n < 0 THEN 'negative' ELSE 'zero' END
The WHEN
conditions are tested in the order in which they appear in the source. If no ELSE
expression is specified, it defaults to ELSE NULL
. An abbreviated syntax exists mirroring switch statement[11]s; it is called "simple case" in the SQL standard:
CASE n WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'i cannot count that high' END
This syntax uses implicit equality comparisons, with the usual caveats for comparing with NULL.
For the Oracle-SQL dialect, the latter can be shortened to an equivalent DECODE
construct:
SELECT DECODE(n, 1, "one", 2, "two", "i cannot count that high") FROM some_table;
The last value is the default; if none is specified, it also defaults to NULL
.
However, unlike the standard's "simple case", Oracle's DECODE
considers two NULL
s to be equal with each other.
Handling Errors
PL/SQL[12]
Offers EXCEPTIONS, both internal and user defined.
Offers RAISE, which calls a pre-defined exception.
T/SQL[13][14]
Offers EXCEPTIONS, both internal and user defined.
Offers RAISERROR, which behaves like an ASSERT in the C language.
CURSOR
PL/SQL[15]
-- CURSOR syntax CURSOR cursor_name [(parameter[, parameter]...)] [RETURN return_type] IS select_statement; -- CURSOR example DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10
T/SQL[16]
-- ISO and T/SQL syntax ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
Note: T/SQL is a whole lot more complex
IF - THEN - ELSE
PL/SQL[17]
IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF;
T/SQL[18]
IF 1 = 1 PRINT 'Boolean_expression is true.' ELSE PRINT 'Boolean_expression is false.' ;
Note: T/SQL has no THEN or END IF clause.
JOINs
Cartesian Product
A select statement with two tables and no join clause. For example:
SELECT pt.product_type_id, p.product_id FROM product_types pt, products p;
will produce all the rows in product_types matched with all the rows in products.
If product_types has M rows and products has N rows the result set will be M * N rows.
Equi Joins use the equality operator (=).
Natural Joins is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns. This assumes a consistent naming convention in a Level 3 Normalized Database.
Non-equijoins use an operator other than the equality operator, such as <, >, BETWEEN, and so on.
Self Joins return rows joined on the same table.
Inner Joins return a row only when both columns in the tables satisfy the join condition.
select pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID as PID, pat.patient_id, to_char( sum(ch.PATIENT_RESPONSIBILITY - ch.PATIENT_PAYMENTS), '$9,999.00') as OWED from charge ch, patient pat where ch.patient_id = pat.patient_id and pat.PATIENT_ACCOUNT_CODE_ID = '34' and ch.date_of_service <= (SYSDATE - 14) and ch.balance != '0' Group By (pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID, pat.patient_id) order by OWED DESC select pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID as PID, pat.patient_id, to_char( sum(ch.PATIENT_RESPONSIBILITY - ch.PATIENT_PAYMENTS), '$9,999.00') as OWED from charge ch INNER JOIN patient pat ON ch.patient_id = pat.patient_id and pat.PATIENT_ACCOUNT_CODE_ID = '34' and ch.date_of_service <= (SYSDATE - 14) and ch.balance != '0' Group By (pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID, pat.patient_id) order by OWED DESC
Outer Joins return a row even when one of the columns in the join condition contains a null value.
Full Outer Joins A full outer join uses all rows in the joined tables, including those that have null values in either of the columns used in the join.
SELECT p.name, pt.name FROM products p FULL OUTER JOIN product_types pt USING (product_type_id) ORDER BY p.name; NAME NAME ------------------------------ ---------- 2412: The Return Video Chemistry Book Creative Yell CD From Another Planet DVD Modern Science Book My Front Line Pop 3 CD Space Force 9 DVD Supernova Video Magazine
Left Outer Join returns all the rows on the Left and NULLs for missing rows on the Right.
For example:
SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id (+)
ORDER BY p.name;
NAME NAME ------------------------------ ---------- 2412: The Return Video Chemistry Book Classical Music CD Creative Yell CD From Another Planet DVD Modern Science Book My Front Line Pop 3 CD Space Force 9 DVD
Right Outer Join returns all the rows on the Right and NULLs for missing rows on the Left.
For example:
SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id (+) = pt.product_type_id
ORDER BY p.name;
NAME NAME ------------------------------ ---------- 2412: The Return Video Chemistry Book Classical Music CD Creative Yell CD From Another Planet DVD Modern Science Book My Front Line Book Pop 3 CD Space Force 9 DVD Magazine
Outer Joins Limitations
Use (+) on only one side, not both.
You cannot use an outer join condition with the IN operator.
You cannot use an outer join condition with another join using the OR operator.
USING Keyword SQL/92
from charge ch, patient pat
where ch.patient_id = pat.patient_id
becomes
from charge ch INNER JOIN patient pat
USING ( patient_id )
But patient_id can not be in the select clause.
Language Elements
PL/SQL[19]
T/SQL[20]
LOOPS
WHILE LOOP
PL/SQL[21]
LOOP ... IF credit_rating < 3 THEN ... EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here
This is a WHILE loop with an embedded IF-EXIT-END IF conditional control.
T/SQL[22]
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 END
T/SQL WHILE LOOP example.
FOR LOOP
PL/SQL[23]
FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i sequence_of_statements -- executes three times END LOOP;
T/SQL
There is no FOR LOOP in T/SQL but a WHILE LOOP can be written to produce the same behavior.
Native Dynamic SQL
PL/SQL[24]
SQL query based on run-time variables.
T/SQL[25]
SQL query based on run-time variables.
Object Types
PL/SQL[26]
Supports abstract, user-defined objects.
T/SQL[27]
Supports abstract, user-defined objects.
Packages
PL/SQL[28]
A package is a schema object that groups logically related PL/SQL types, items, and subprograms.
T/SQL[29]
Procedures can be grouped in Management Studio script project or in a schema, but there is no package as in PL/SQL.
Sub Programs
PL/SQL[30]
Has procedures and functions where procedures perform an action and functions compute a value.
T/SQL[31][32]
Has procedures and functions where procedures perform an action and functions compute a value.
Tuning Applications
PL/SQL[33]
T/SQL[34]
Internal Links
Parent Article: Microsoft SQL Server (Notes)