SQL AND and OR clauses

SQL AND & OR clauses are used to combine multiple conditions to filter out the records in an SQL statement. These two operators are called as the conjunctive operators. 

These operators can be combined to test multiple conditions in SELECT, UPDATE or DELETE SQL queries. 

The AND operator

The AND operator are used to set multiple conditions along with WHERE clause in SELECT, UPDATE or DELETE SQL queries. 

Syntax

The basic syntax of SQL AND operator with a WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using the AND operator. All the conditions separated by the AND operator must be TRUE in order to execute an action by the SQL statement.

Example

Consider the Employee table with following records.

Following is an example which retrieve emp_id, emp_name and emp_salary from the Employee table WHERE salary is greater than equal to 45000 AND age is less than 35 years:

SELECT emp_id, emp_name, emp_salary 
FROM Employee
WHERE salary >= 45000
AND age < 35;

The output for the above query will be as below:

The OR operator

The OR operator is used to combine multiple conditions with WHERE clause in SELECT, UPDATE or DELETE SQL queries. The only difference between AND and OR is their behavior.

The AND operator combines multiple conditions and the records satisfying all the the specified conditions will be part of the result set.

But in case of the OR operator, at least one condition from the conditions specified must be satisfied by any record to be in the result set.

Syntax

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN];

You can combine N number of conditions using the OR operator. Any of the of conditions separated by the OR operator must be TRUE in order to execute an action by the SQL statement.

Example

Consider the Employee table with the following records.

The below example, fetch emp_id, emp_name and emp_salary from the Employee table WHERE salary is greater than 45000 OR age is less than 30 years:

SELECT emp_id, emp_name, emp_salary 
FROM Employee
WHERE salary > 45000
OR age < 30;

Summary: In this tutorial, you have learned about SQL AND and SQL OR operator in SQL.