Beginners guide: How to use And & OR Operator in SQL Server

Neerajprasadsharma
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 652 red flag

Learn the basics of And & OR operator in SQL Server

Beginners guide: How to use And & OR Operator in SQL Server 

As in all programming languages AND & OR operator are very important for some specific programming operations. Same goes to SQL Server as well. 
Here, we will look into some easy examples of And & OR operator with Where condition.
Let us create a table and analyze the And & OR operator.

Create Table TblAndOR (ID int , Name Varchar(30) , Age TinyInt )

Insert Into TblAndOR Values (1,'Neeraj' , 24 )
Insert Into TblAndOR Values (2,'Neeraj' , 23 )
Insert Into TblAndOR Values (3,'Mark' , 24 )
Insert Into TblAndOR Values (4,'Ricky' , 30 )
Insert Into TblAndOR Values (5,'Micky' , 32 )
Insert Into TblAndOR Values (6,'Neeraj' , 30 )





SQL Server AND operator:


Select * From TblAndOR where name='neeraj' And age=24
Select * From TblAndOR where name='neeraj' And Name = 'Mark'


Let us try to translate both the above query text into simple English first query says:
Find all records from tableandor which row has name Neeraj and has age 24,  and in the above table there is only 1 record exists where name is Neeraj and age is also 24 so SQL Server return 1 record.
Second query says:
Find all records from tableandor which row has name Neeraj and name Mark as well.
The above request is impossible, this cannot happen, so SQL Server did not return any record.

SQL Server OR operator

Let us use the above query again, but this time with OR operator and analyze the result.

 Select * From TblAndOR where name='neeraj' OR age=24
Select * From TblAndOR where name='neeraj' OR Name = 'Mark'


If we again try to translate the above query text in simple English, the first query says:
Find all records from tableandor where the name is 'Neeraj' or the age is 24.
There are 3 records whose name is 'Neeraj' and 2 records whose age is 24, and 1 is common among them, so there are 4 records which satisfy the above query.
The second query says:
Find all records from tableandor where name is 'Neeraj' or name is 'Mark'.
We can see in the table there are 3 records those name are 'Neeraj' and 1 record whose name is Mark so SQL Server returns 4 rows.

And & OR combination in SQL Server

Many times we have to use both and & or in our where condition. But we have to use the combination very carefully because the SQL Server compile query left to right in where condition.
For example: if we want to find data which has name 'Neeraj' and age can be 23 or 24.
So we can`t query like 
Select * From TblAndOR Where  Name= 'Neeraj' And Age=23  or age=24
Because it suggests to SQL Server that we are looking for name 'Neeraj' with age 24 or any data having age 24.
So the query should be like:
Select * From TblAndOR Where  Name= 'Neeraj' And (Age=23  or age=24)
Example below:
--wrong
Select * From TblAndOR Where  Name= 'neeraj' And Age=23  or age=24
--correct
Select * From TblAndOR Where  Name= 'neeraj' And (Age=23  or age=24)



   
Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)