Database Index not used if the where criteria is !=?

Question

I have a index on a column and it is correctly used when the query is

select * from Table where x = 'somestring'

However it seems to be not used when the query is something like

select * from Table where x != 'someotherstring'

Is this normal or am I missing something else in the query? The actual query is of course much larger and so it could be caused by some other factor. Any other ideas why an index would not be used in a query?

Answer 1

This is normal. Index will only be used if you have a '=' condition. Searching index for != condition is not effective.

Similarly, this may use the index (in Oracle)

select * from Table where x like 'some%'

but this wouldn't

select * from Table where x like '%thing%'

Also, select * from Table where x between 1 and 10 will use the index

but not select * from Table where x not between 1 and 10

Answer 2

this is absolutely normal. index is used to look for exact something. where you start when I ask you to look a dictionary when I told you not start with 'S'.

you can always do this.

select * from Table a
where not exist (select * from table b where x = 'somestring' and a.key = b.key)