SQL AND & OR Operators

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:

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';