Wednesday, August 13, 2014

AND and OR Operator in Sql Server

And & Or:- These Operators are used to further filter the recordset return by SQL queries when more than one conditions are specified. In case of "AND" all the conditions must be true and in case of "OR" any of the condition must be true.
In this article, we are going to use the table Employee for explaining examples. Structure of this table is given below:-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


Following Insert queries will insert data into the table Employee

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Nidhi','Sharma','nidhi@abcmail.com',270000)


For example, in case of "AND", suppose we want to get records from the table Employee whose last name is "Sharma" and salary less than 300000, then we can use the following query:-

Select * from employee where EmpLastName='Sharma' AND salary < 300000

This query will return the record of the employee whose empnumber is "A004".

In case of "OR", suppose we want to get records from the table Employee whose last name is either Sharma or salary is less than 300000, then we can use the following query

Select EmpNumber from employee where EmpLastName='Sharma' OR salary  <  300000

This query will returns all the records:-

A001
A002
A003
A004


No comments:

Post a Comment