Summary: in this tutorial, you will learn how to use the logical operator SQL AND to combine two Boolean expressions to form flexible queries.
Introduction to SQL AND operator
The AND operator is a logical operator that combines two Boolean expressions. The following expression uses the AND operator.
expression1 AND expression2;
The AND operator returns true if both expressions evaluate to true. If one of the two expressions is false, the AND operator returns false even the other expression is NULL.
The following table illustrates the results when we compare true, false, and NULL values by using the AND operator.
SQL AND operator examples
We take the
employees table in the sample database to demonstrate the SQL AND operator.
The following statement retrieves information about employees who have both job id of 1 and salary greater than 5000.
job_id = 9
AND salary > 5000;
To find all the employees who joined the company between 1997 and 1998, you use the AND operator as follows:
YEAR (hire_date) >=1997
AND YEAR (hire_date) <= 1998;
SQL AND and short-circuit evaluation
The short-circuit feature allows the database system to stop processing the remaining parts of the logical expression as soon as it can determine the result.
Let’s take a look at an example to get a better understanding of how the to short-circuit works.
See the following expression.
1 = 0 AND 1 = 1;
The database system processes two comparisons first and uses the AND operator to evaluate the two results.
However, with the short-circuit feature, the database system just has to evaluate the left part of the expression because the left part (1 = 0) returns false that causes the whole expression returns false regardless of the result of the right part of the expression.
The short-circuit feature, therefore, decreases the CPU computation time and in some cases, it helps prevent runtime-error. See the following expression.
1 = 0 AND 1/0;
If the database system supports the short-circuit feature, it will not evaluate the right part of the expression (1/0) that causes the division by zero error.
Now, you should understand how the SQL AND operator works and how to apply it to form a complex condition in your queries.