SQL Server AND

The objective of this tutorial is to teach you how to use the SQL Server AND operator to evaluate multiple conditions in a SQL query.

What is the AND operator in SQL Server?

The AND operator evaluates all the conditions specified in a query and executes the query only when all the conditions are satisfied.

Operation

The SQL Server AND operator is used with and follows the WHERE keyword in a SQL query. A SQL statement can contain one or more AND operators to string together multiple conditions for evaluation and the condition check can be performed on one or more tables. It can be used with SELECT, UPDATE and DELETE queries. When used with SELECT it returns only those records where all the conditions specified evaluate to TRUE. When used with UPDATE it updates the values for the specified columns when all the specified conditions evaluate to TRUE. When used in DELETE query it deletes records from the table when all the specified conditions evaluate to TRUE.

SQL Server AND operator syntax

The basic syntax of SQL Server AND operator is as follows.

SELECT expressions
FROM tables
WHERE [conditions AND condition X];

In this syntax,

• expressions – expressions defined here are the column(s) or calculations you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
• tables – one or more than one table from where you want to retrieve data.
• WHERE conditions – Mandatory with AND. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
• AND –This is used to specify one or more conditions with the where clause.

SQL Server AND Operator Examples

Let us see how it works in the different scenarios. NOTE: Columns are also referred to as fields and the terms are used interchangeably. Suppose we have an Employees table with the following data.

Emp_id	emp_name	emp_phone	emp_gender	dept_code	emp_location
1270	Kalyan Purkayastha	9620139678	M	119	Kolkata
1271	Rajesh Pandey	9611895588	M	121	Bangalore
1272	Annie Bora	8884692570	F	121	Bangalore
1273	Dipankar Karmakar	9957889640	M	119	Kolkata
1274	Sunitha Rai	9742067708	F	109	Mumbai
1276	Parag Barman	8254066054	M	121	Kolkata
1277	Vinitha Sharma	9435746645	F	121	Mumbai

1) SQL Server AND Example – SELECT query

The following SELECT statement evaluates 2 conditions – one checks for a character and another for a number. So, to validate 2 different conditions, we have to use AND condition along with the WHERE clause.

SELECT * FROM employees
WHERE emp_gender=’F’
AND dept_code=121;

The output generated by the query is below.

1272	Annie Bora	8884692570	F	121	Bangalore
1277	Vinitha Sharma	9435746645	F	121	Mumbai

2) SQL Server AND Example – UPDATE query

The following UPDATE query updates the value of dept_code to 111 where the conditions specified for dept_code and emp_location match the values given in the query.

UPDATE employees
SET dept_code=111
WHERE dept_code=119
AND emp_location=’Kolkata’;

Output

We can check the result of the update query by running a select for the new dept_code 111.

SELECT * FROM employees
WHERE dept_code=111;

The output is given below.

1270	Kalyan Purkayastha	9620139678	M	111	Kolkata
1273	Dipankar Karmakar	9957889640	M	111	Kolkata

3) SQL Server AND Example – DELETE query

The following DELETE query removes rows from the table where the conditions specified for dept_code and emp_location match the values given in the query.

DELETE FROM employees
WHERE dept_code=121
AND emp_location=’Bangalore’;

Output

We can check the result of the delete query by running a select for dept_code=121 which shows that employee location Bangalore does not exist for the department anymore.

SELECT * FROM employees
WHERE dept_code=121;

The output is given below.

1276	Parag Barman	8254066054	M	121	Kolkata
1277	Vinitha Sharma	9435746645	F	121	Mumbai

4) SQL Server AND Example – querying conditions from multiple tables using JOIN

Let us consider the below 2 tables (employees and department) for illustrating how AND can be used to evaluate conditions across tables.

Emp_id	emp_name	emp_phone	emp_gender	dept_code	emp_location
1270	Kalyan Purkayastha	9620139678	M	111	Kolkata
1271	Rajesh Pandey	9611895588	M	121	Bangalore
1272	Annie Bora	8884692570	F	121	Bangalore
1273	Dipankar Karmakar	9957889640	M	111	Kolkata
1274	Sunitha Rai	9742067708	F	109	Mumbai
1276	Parag Barman	8254066054	M	121	Kolkata
1277	Vinitha Sharma	9435746645	F	121	Mumbai

dept_code	dept_name	dept_head	dept_name
109	Seema Shenoy	Mumbai	HR
117	Narasimha Prabhu	Kolkata	IT
119	Amitabh Barman	Mumbai	Sales
121	Ganesh Das	Bangalore	Finance

The following SELECT query select rows from the table where one condition checks to see that the dept_code is the same for both tables in the result set and another condition operates only on the employees table and checks for the specified location.

SELECT employees.emp_id, employees.emp_name, employees.emp_gender
FROM employees
INNER JOIN department
ON employees.dept_code=department.dept_code
AND emp_location="Bangalore";

On executing the query, we get the below result which satisfies both the conditions specified.

1271	Rajesh Pandey	9611895588	M	121	Bangalore
1272	Annie Bora	8884692570	F	121	Bangalore