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)