sql query - select all rows where (field) does not = (value)

Question

Post reply

sql query - select all rows where (field) does not = (value)

paul welsh

SSC Enthusiast

Points: 154
More actions
April 28, 2005 at 10:57 am

#185857

Hi again,

I have a table with names and addresses. Some rows have been marked with a '1' in for certain customers who have cancelled an order.
I want to run a query that pulls all the customers who do not have a '1' against them e.g. all the customers who did not cancel.

I have been trying different syntax but I keep getting errors.

Any help would be appreciated.

thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select custId, lName, fName from dbo.Customers where CanceledOrder = 0

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

Or even:

Select custId, lName, fName from dbo.Customers where CanceledOrder != 1

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You're right... I was assuming a bit column but it could be something else... It's just that = usually works faster that because indexes can be used.

Also should be used instead of != (even if it does the same thing)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

I also believe that the optimizer works better with "=VALUE" as opposed to "!=VALUE" or "<>VALUE" if the column is indexed.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That's why I gave my first answer as =

but that makes the assumption that the column is a bit... or can only have only 2 values. If this is not the case, then is a safer way to go even if it may be slower. However in the case of a column like this, an index would prove useless since their would be only 2 different values in the index.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

No always ... with no index on the column a 'table scan' would be used. With an index on the column, worst case, an 'index scan' is performed. Still not the most efficient (exactly the same amount of logical reads) but far less I/O (much less physical reads since the index is far smaller than the table data).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Isn't it why sql server creates and maintains statistics? I wonder if someone can come up with a sample script to show what really happens.

ron k SSCertifiable Points: 6053 More actions · Answer 8

Of course if the coulmn is nullable then you would need something like:

Select custId, lName, fName from dbo.Customers

where CanceledOrder <> 1

OR CanceledOrder is NUll

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

We could debate this for weeks (with all the knowlegde of this community), but we don't have the table def so I don't see the point of continuing this discussion at this time... but you're obviously absolutely right about that.

ron k SSCertifiable Points: 6053 More actions · Answer 10

Paul,

If you are still having a problem then please post the statement you are trying and the error message you are getting.

ron