1

I have this Oracle SQL query which I use to make checks for number:

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID = ?

I want to select all values different than the value 12. How I must edit the query?

3 Answers3

1

There are two not equals operators in Oracle. The <> operator, which is the ANSI SQL standard, and the != operator, which is supported by most if not all relational databases. Conceptually, they should provide the same results, however according to this post, they might result in different execution plans affecting performance.

Your query can be expressed as:

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID != 12

or:

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID <> 12

You could also use the NOT IN operator if you wanted to support multiple values. If you wanted to exclude 12, 15 and 20, you could do:

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID NOT IN (12, 15, 20)
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
  • The "official" (read ANSI SQL) operator would be `<>`, but nearly all (if not all) DBMS also support the non-standard `!=` operator. –  Nov 28 '12 at 18:47
  • @a_horse_with_no_name - Yea, the link I provided goes into some interesting details on that, pointing out `!=` and `<>` might result in different execution plans. Who knew. – Mike Christensen Nov 28 '12 at 18:52
  • 1
    Interesting read, but I guess Stéphane's explanation (new execution plan/bind variable peeking) is probably the most likely reason. The `<>` isn't different by default. –  Nov 28 '12 at 19:03
  • That particular article was discussed (a lot) and pretty much debunked in http://stackoverflow.com/questions/12003127/oracle-operators, cc @a_horse_with_no_name – Ben Nov 28 '12 at 20:14
1

Are you trying to return values that are not set at all?

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID != 12

Will only return values that have MSYSTEMGROUPID set to a value.

If you want Both values not equal to 12 and values that are not set at all, use this:

SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID  IS NULL OR MSYSTEMGROUPID != 12
A B
  • 4,068
  • 1
  • 20
  • 23
0

Try this:-

 SELECT * from MANAGEDSYSTEMGROUPS where MSYSTEMGROUPID != 12
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331