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_idemp_nameemp_phoneemp_genderdept_codeemp_location
1270Kalyan Purkayastha9620139678M119Kolkata
1271Rajesh Pandey9611895588M121Bangalore
1272Annie Bora8884692570F121Bangalore
1273Dipankar Karmakar9957889640M119Kolkata
1274Sunitha Rai9742067708F109Mumbai
1276Parag Barman8254066054M121Kolkata
1277Vinitha Sharma9435746645F121Mumbai
Employees Table (Containing data of employees)

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.

1272Annie Bora8884692570F121Bangalore
1277Vinitha Sharma9435746645F121Mumbai

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.

1270Kalyan Purkayastha9620139678M111Kolkata
1273Dipankar Karmakar9957889640M111Kolkata

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.

1276Parag Barman8254066054M121Kolkata
1277Vinitha Sharma9435746645F121Mumbai

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_idemp_nameemp_phoneemp_genderdept_codeemp_location
1270Kalyan Purkayastha9620139678M111Kolkata
1271Rajesh Pandey9611895588M121Bangalore
1272Annie Bora8884692570F121Bangalore
1273Dipankar Karmakar9957889640M111Kolkata
1274Sunitha Rai9742067708F109Mumbai
1276Parag Barman8254066054M121Kolkata
1277Vinitha Sharma9435746645F121Mumbai
Employees Table (Containing data of employees)
dept_codedept_namedept_headdept_name
109Seema ShenoyMumbaiHR
117Narasimha PrabhuKolkataIT
119Amitabh BarmanMumbaiSales
121Ganesh DasBangaloreFinance
Department Table (Containing data of the different departments)

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.

1271Rajesh Pandey9611895588M121Bangalore
1272Annie Bora8884692570F121Bangalore

Advertisement