Oracle and Microsoft SQL Server comparison operators are nearly identical.
| Operator | Oracle | Microsoft SQL Server |
|---|---|---|
| Equal to | (=) | (=) |
| Greater than | (>) | (>) |
| Less than | (<) | (<) |
| Greater than or equal to | (>=) | (>=) |
| Less than or equal to | (<=) | (<=) |
| Not equal to | (!=, <>, ^=) | (!=, <>, ^=) |
| Not greater than, not less than | N/A | !> , !< |
| In any member in set | IN | IN |
| Not in any member in set | NOT IN | NOT IN |
| Any value in set | ANY, SOME | ANY, SOME |
| Referring to all values in set |
!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL, != SOME, <> SOME, < SOME, > SOME, <= SOME, >= SOME |
!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL, != SOME, <> SOME, < SOME, > SOME, <= SOME, >= SOME |
| Like pattern | LIKE | LIKE |
| Not like pattern | NOT LIKE | NOT LIKE |
| Value between x and y | BETWEEN x AND y | BETWEEN x AND y |
| Value not between | NOT BETWEEN | NOT BETWEEN |
| Value exists | EXISTS | EXISTS |
| Value does not exist | NOT EXISTS | NOT EXISTS |
| Value {is | is not} NULL | IS NULL, IS NOT NULL | Same. Also = NULL, != NULL for backward compatibility (not recommended). |
The SQL Server LIKE keyword offers useful wildcard search options that are not supported by Oracle. In addition to supporting the % and _ wildcard characters common to both RDBMSs, the [ ] and [^] characters are also supported by SQL Server.
The [ ] character set is used to search for any single character within a specified range. For example, if you search for the characters a through f in a single character position, you can specify this with LIKE '[a-f]' or LIKE '[abcdef]'. The usefulness of these additional wildcard characters is shown in this table.
| Oracle | Microsoft SQL Server |
|---|---|
| SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE 'A%' OR LNAME LIKE 'B%' OR LNAME LIKE 'C%' |
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE '[ABC]%' |
The [^] wildcard character set is used to specify characters NOT in the specified range. For example, if any character except a through f is acceptable, you use LIKE '[^a - f]' or LIKE '[^abcdef]'.
For more information about the LIKE keyword, see SQL Server Books Online.
Although Microsoft SQL Server traditionally has supported the SQL-92–standard as well as some nonstandard NULL behaviors, it supports the use of NULL in Oracle.
SET ANSI_NULLS should be set to ON for executing distributed queries.
The SQL Server ODBC driver and OLE DB Provider for SQL Server automatically SET ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. SET ANSI_NULLS defaults to OFF for connections from DB-Library applications.
When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.
For more information about the use of NULL, see SQL Server Books Online.
Oracle uses two pipe symbols (||) as the string concatenation operator, and SQL Server uses the plus sign (+). This difference requires minor revision in your application program code.
| Oracle | Microsoft SQL Server |
|---|---|
| SELECT FNAME||' '||LNAME AS NAME FROM STUDENT_ADMIN.STUDENT |
SELECT FNAME +' '+ LNAME AS NAME FROM STUDENT_ADMIN.STUDENT |