The SQL AND and OR
operators are used to combine multiple conditions to filter data in an SQL
statement.
The SQL AND and OR operators provides
multiple compressions with different operator in the same SQL Statement.
SQL AND Operator
The AND Operator is used to display result on the basis of
both condition are TRUE (means first and second condition are true).
You can use multiple conditions through SQL AND Operator in
an SQL Statement.
The AND Operator return the Boolean expressions that is TRUE
or FALSE.
When more than one logical operator is
used in a statement, the AND operators are evaluated first.
Syntax:
Syntax:
The basic
syntax of AND operator with WHERE clause is as follows:
SELECT column1, column2, columnN
FROM table_name
WHERE
[condition1] AND [condition2]...AND [conditionN];
Example:
Consider the EMPLOYEE table having the following records:
CREATE TABLE [dbo].[Employee](
[Employee_id] [int] IDENTITY(1,1) NOT NULL,
[First_name] [nvarchar](300) NULL,
[Last_name] [nvarchar](300) NULL,
[Salary] [decimal](20, 2) NULL,
[Joining_date] [datetime] NULL,
[Department] [nvarchar](200) NULL,
[Department_ID] [int] NULL,
[City] [nvarchar](250) NULL,
[Age] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('John', 'Abraham', 1000000.00, 1905-06-07, 'Banking', 1, 'Delhi',25)
INSERT [dbo].[Employee] ([Last_name],
[Salary], [Joining_date], [Department], [Department_ID], [City],[Age]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance', 2, 'Mumbai',30)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance', 1, 'Delhi',33)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('Roy', 'Thomas', 700000.00, 1894-06-27, 'Banking', 1, 'Mumbai',28)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('Tom', 'Jose', 600000.00, 1894-06-27, 'Insurance', 3, 'kolkata',26)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('Jerry', 'Pinto', 650000.00, 1894-06-27, 'Insurance', 3, 'Delhi',24)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('Philip', 'Mathew',750000.00, 1894-06-28, 'Services', 2, 'kolkata',24)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('TestName1', '123', 650000.00, 1894-06-28, 'Services', 2, 'Delhi',35)
INSERT [dbo].[Employee] ([First_name],
[Last_name], [Salary],
[Joining_date], [Department], [Department_ID],
[City],[Age]) VALUES ('TestName2', 'Lname%',600000.00, 1894-06-27, 'Insurance', 2, 'Mumbai',36)
Example1:
Fetch Employee_id, First_name, Salary,
Department and Age fields from the EMPLOYEE
table where salary is greater than 700000 AND age is less than 25
years:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE SALARY > 700000.00 AND
age < 25;
Example2:
Fetch Employee_id, First_name, Salary,
Department and Age fields from the EMPLOYEE
table where Department is Insurance AND age is greater than 25
years:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE
[Department] ='Insurance'
AND age > 25;
Example3:
Fetch Employee_id, First_name,
Salary, Department and Age fields from the EMPLOYEE
table where Department is Insurance AND City is Mumbai:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE
[Department] ='Insurance'
AND City='Mumbai';
The
OR Operator:
The OR Operator is used to display
result on the basis of any one condition are TRUE (means either first or second
condition is true).
The OR Operator returns the Boolean expressions that are
TRUE or FALSE.
The OR operator is used to combine
multiple conditions in an SQL statement with WHERE clause.
Syntax:
The basic syntax of OR operator with WHERE clause is as
follows:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR
[conditionN];
Example1:
Fetch Employee_id, First_name,
Salary, Department and Age fields from the EMPLOYEE
table where salary is greater than 700000 OR age is less than 25 years:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE SALARY > 700000.00 OR age
< 25;
Example2:
Fetch Employee_id, First_name, Salary,
Department and Age fields from the EMPLOYEE
table where Department is Insurance OR age is greater than 25
years:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE
[Department] ='Insurance'
OR age > 25;
Example3:
Fetch Employee_id, First_name,
Salary, Department and Age fields from the EMPLOYEE
table where Department is Insurance OR City is Mumbai:
SELECT
Employee_id, [First_name], [Salary],[Department],[Age]
FROM [dbo].[Employee]
WHERE
[Department] ='Insurance'
OR City='Mumbai';
