OR Operators - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OR Operators

Author

Topic

Phase3
Starting Member

1 Post

Posted - 2007-10-28 : 16:45:35

I apologised if this has been posted before. I am having so many problems with these operators.

I can get this to work..

" Select *
from emp
where job is not 'manager';

but not

" Select *
from emp
where job is not 'manager'
or where job is not 'president' ;

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 16:59:50

You only say "Where" once. Also "Is Not" is not a qualifier..so that probably wouldn't work anyway

Select *
From [Table]
WHERE {conditions}

In this case, you can use the IN operator which is below:
Select * from emp where job not in ('Manager','president')

The parenthetical IN reference is treated like a multiple OR statement (think of it like the comma is the OR statement)

also, and using the OR operator

Select * from emp where job <> 'Manager' or job <> 'President'

or using the NOT qualifier and IN and OR

Select * from emp where job not in('Manager') or job not in ( 'President')

the last one is the worst possible choice, but just so you could see the syntax

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-28 : 23:40:22

dataguru: actually your last two suggestions will return all rows.


declare @emp table(job varchar(30))
insert @emp select 'Manager' union all select 'President' union all select 'Lackey'

Select * from @emp where job <> 'Manager' or job <> 'President' -- wrong
Select * from @emp where job not in('Manager') or job not in ( 'President') -- wrong 

select * from @emp where job not in ('Manager','President') -- this is what you want.

even where job=manager or job=president. can you see why?

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources