SQL Server: AND Condition
This SQL Server tutorial explains how to use the AND condition in SQL Server (Transact-SQL) with syntax and examples.
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.
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.
- 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.
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.
SELECT employees.employee_id, contacts.last_name FROM employees, contacts WHERE employees.employee_id = contacts.contact_id AND employees.first_name = 'Sarah';
Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.
SELECT employees.employee_id, contacts.last_name FROM employees INNER JOIN contacts ON employees.employee_id = contacts.contact_id WHERE employees.first_name = 'Sarah';
This SQL Server AND condition example would return all rows where the first_name in the employees table is ‘Sarah’. 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.
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.
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.
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’.