SQL Server: AND Condition

In this post explains how to use the AND condition in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) AND condition (also called the AND Operator) is used to test for two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND condition in SQL Server (Transact-SQL) is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

All of the conditions that must be met for the records to be selected.

Note

• The SQL Server AND condition allows you to test 2 or more conditions.
• The SQL Server AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

The first SQL Server AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND employee_id  499;

This SQL Server AND example would return all employees who have a last_name of 'Smith' and have an employee_id less than 499. Because the * is used in the SELECT statement, all fields from the employees table would appear in the result set.

Example - JOINING Tables

Our next SQL Server AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees, contacts
WHERE employees.employee_id = contacts.contact_id
AND employees.first_name = 'Julie';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees
INNER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Julie';

This SQL Server AND condition example would return all rows where the first_name in the employees table is 'Julie'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table. You will notice that all of the fields are prefixed with the table names (ie: contacts.last_name). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the employees and the contacts tables.

In this case, the result set would only display the employee_id and last_name fields (as listed in the first part of the SELECT statement.).

Example - With INSERT Statement

This next SQL Server AND example demonstrates how the AND condition can be used in the INSERT statement.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE first_name = 'Joanne'
AND employee_id >= 800;

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table where the first_name is 'Joanne' and the employee_id is greater than or equal to 800.

Example - With UPDATE Statement

This SQL Server AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE employees
SET last_name = 'Johnson'
WHERE last_name = 'TBD'
AND employee_id  300;

This SQL Server AND condition example would update all last_name values in the employees table to 'Johnson' where the last_name is 'TBD' and the employee_id is less than 300.

Example - With DELETE Statement

Finally, this last SQL Server AND example demonstrates how the AND condition can be used in the DELETE statement.

For example:

DELETE FROM employees
WHERE first_name = 'Darlene'
AND last_name = 'Henderson';

This SQL Server AND condition example would delete all records from the employees table whose first_name is 'Darlene' and last_name is 'Henderson'.