The SELECT statement syntax for Oracle and Microsoft® SQL Server™ is similar.
| Oracle | SQL Server |
|---|---|
| SELECT [/*+ optimizer_hints*/] [ALL | DISTINCT] select_list [FROM {table_name | view_name | select_statement}] [WHERE clause] [GROUP BY clause] [HAVING clause] [START WITH ... CONNECT BY] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT ...] [ORDER BY clause] [FOR UPDATE] |
SELECT [ALL | DISTINCT] select_list [INTO [new_table_name]] [FROM {table_name | view_name | select_statement} [JOIN {table_name | view_name | select_statement} ON search_conditions] [(optimizer_hints)] [WHERE clause] [GROUP BY clause] [HAVING clause] [{UNION | UNION ALL} SELECT ...] [ORDER BY clause] [COMPUTE clause] [FOR BROWSE] [OPTION (query_hints)] |
Oracle-specific, cost-based optimizer hints are not supported by SQL Server. They must be removed from your statement code. The recommended technique is to assume that the default SQL Server cost-based optimization will work well.
SQL Server does not support the Oracle START WITH...CONNECT BY clause. If your application currently takes advantage of this hierarchical reporting capability, those statements that include this clause must be rewritten. You can replace this in SQL Server with a user-written procedure that performs the same task.
The INTERSECT set operator is not supported by SQL Server; however, the EXISTS clause can be used to obtain the same result. This example uses the INTERSECT operator to find the course code and course name for all classes that have students. The EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical, regardless of query.
| Oracle | SQL Server |
|---|---|
| SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS INTERSECT SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE |
SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS C WHERE EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE) |
The MINUS set operator is not supported by SQL Server, but the NOT EXISTS clause can be used to obtain the same result. This example uses the MINUS operator to find those classes that do not have any students. The EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical, regardless of query.
| Oracle | SQL Server |
|---|---|
| SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS MINUS SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE |
SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASSC WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE) |
Oracle uses the FOR UPDATE clause to lock rows specified in the SELECT statement. Usually you do not need to use the equivalent clause in SQL Server. Do not confuse the FOR UPDATE and the FOR BROWSE clauses. The FOR BROWSE clause is a specialized facility for use in client application programs that need additional metadata at run time.
The SQL Server COMPUTE clause is used to generate row aggregate functions (SUM, AVG, MIN, MAX, and COUNT), which appear as additional rows in the query results. It allows you to see detail and summary rows in one set of results. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group.
Note The COMPUTE clause works just like the COMPUTE command found in the Oracle SQL*Plus query tool.