Combine AND and OR conditions in SQL Server

The article explains how to use AND conditions and OR conditions in SQL Server (Transact-SQL).

There are separate tutorials on AND conditions and OR conditions in SQL Server. But in addition, these two conditions can be used in combination with SELECT, INSERT, UPDATE and DELETE commands.

When combining these two conditions, it is important to remember to use parentheses to let the database know the order in which to execute each condition.

Syntax combining AND conditions and OR conditions

 WHERE 'điều kiện 1' 
 AND 'điều kiện 2' 
 … 
 OR 'điều kiện n'; 

Variable names and variable values

Condition 1, Condition 2 . Condition n

Conditions are evaluated to determine if the record is selected.

Note

1. AND and OR conditions allow multiple conditions to be checked
2. Don't forget the order of execution, determined by parentheses

For example - SELECT command

 SELECT * 
 FROM nhanvien 
 WHERE (ho = 'Anderson' AND ten = 'Sarah') 
 OR (nhanvien_id = 75); 

This command will return all employees with the last name Anderson and the name Sarah or have ID 75. Parentheses determine the order in which the conditions are executed.

 SELECT nhanvien_i d, ho, ten 
 FROM nhanvien 
 WHERE (ho = 'Smith') 
 OR (ho = 'Anderson' AND ten = 'Sarah') 
 OR (nhanvien_id > 1000 AND ba ng = 'California'); 

In this example, the result returns the employee ID, the last name and the first name if that person is Smith; or they are Anderson and his name is Sarah; or employee ID greater than 1000 and state is California.

Example - INSERT command

 INSERT INTO danhba 
 (ho, ten) 
 SELECT ho, ten 
 FROM nhanvien 
 WHERE (ho = 'Johnson' OR ho = 'Anderson') 
 AND nhanvien_id > 54; 

This example will insert into the list all the values ​​of the family name and name from those who have the last name Johnson or Anderson and whose ID is greater than 54

Example - UPDATE command

 UPDATE nhanvien 
 SET ho = 'TBD' 
 WHERE nhanvien_id <= 2000 
 AND (bang = 'California' OR bang = 'Arizona'); 

In this order, the employee's surname will be updated to TBD if the employee ID is less than or equal to 2000 and lives in California or Arizona.

Example - DELETE command

 DELETE FROM nhanvien 
 WHERE bang = 'California' 
 AND (ho = 'Johnson' OR ten = 'Joe'); 

In this example, the combination of the AND and OR conditions in the DELETE command will delete all records in the table if the state value is California and the employee has the last name Johnson or the name Joe.

Previous article: OR conditions in SQL Server

Next lesson: DISTINCT clause in SQL Server