Comparison 比较操作符 oracle /mssql 对比

来源：互联网发布：小欧工程师软件编辑：程序博客网时间：2024/05/29 17:52

Same in Both SQL Server Server
Operator Databases Only Oracle Only

Equal to =

Not equal to != ^=

Less than <

Greater than >

Less than or equal to <= !>

Greater than or equal to >= !<

Greater than or equal to x BETWEEN x
and less than or equal to y AND y

                    Less than x or greater than y   NOT
                                                    BETWEEN x
                                                    AND y

Pattern Matches LIKE 'a%' LIKE'a[x-z]' LIKE 'a\%'

a followed by 0 or more LIKE 'a_' LIKE'a[^x-z]' ESCAPE '\'
characters

a followed by exactly 1
character

a followed by any character
between x and z

                    a followed by any character
                    except those between x and
                    z

a followed by %

Does not match pattern NOT LIKE

No value exists IS NULL

A value exists IS NOT NULL

At least one row returned by EXISTS
query (query)

No rows returned by query NOT EXISTS
(query)

Equal to a member of set IN =ANY = SOME

                    Not equal to a member of set     NOT IN    !=                            != SOME     <>
                                                    ANY     <>                               SOME
                                                    ANY

Less than a member of set < ANY < SOME

Greater than a member of set > ANY > SOME

Less than or equal to a <= ANY !> ANY <= SOME
member of set

Greater than or equal to a >= ANY !< ANY >= SOME
member of set

Equal to every member of set =ALL

Not equal to every member of != ALL <>
set ALL

Less than every member of < ALL
set

Greater than every member > ALL
of set

Less than or equal to every <= ALL !> ALL
member of set

Greater than or equal to every >= ALL !< ALL
member of set

Recommendations:

1. Convert all !< and !> to >= and <=

Convert the following in Microsoft SQL Server or Sybase Adaptive Server:

WHERE col1 !< 100

to this for Oracle:

WHERE col1 >= 100

1. Convert like comparisons which use [ ] and [^]

     SELECT title
     FROM titles
     WHERE title like "[A-F]%"

1. Change NULL constructs:

Table 2–12 shows that in Oracle, NULL is never equal to NULL. Change the all =
NULL constructs to IS NULL to retain the same functionality.

Table 2–12 Changing NULL Constructs

Microsoft SQL Server or
NULL Construct Sybase Adaptive Server Oracle

where col1 = NULL depends on the data FALSE

where col1 != NULL depends on the data TRUE

where col1 IS NULL depends on the data depends on the data

where col1 IS NOT NULL depends on the data depends on the data

where NULL = NULL TRUE FALSE

If you have the following in Microsoft SQL Server or Sybase Adaptive Server:

WHERE col1 = NULL

Convert it as follows for Oracle:

WHERE col1 IS NULL