Comparison 比较操作符 oracle /mssql 对比

 

                                                   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