SQL AND
Summary: This tutorial introduces you to the SQL AND operator and shows you how to apply it to form flexible conditions in the WHERE
the clause of an SQL statement.
Introduction to SQL AND operator
The AND
the operator is a logical operator that combines two Boolean expressions in the WHERE
clause of the SELECT
, UPDATE
, or DELETE
statement. The following illustrates the syntax of the AND
operator:
expression1 AND expression2;
The AND
the operator returns true if both expressions evaluate to be true. If one of the two expressions is false, then the AND
operator returns false even one of the expressions is NULL.
The following table illustrates the results of the AND
operator when comparing true, false, and NULL values:
TRUE | FALSE | NULL | |
---|---|---|---|
TRUE | TRUE | FALSE | NULL |
FALSE | FALSE | FALSE | FALSE |
NULL | NULL | FALSE | NULL |
SQL AND operator examples
We will use the employees
table from the sample database to demonstrate the SQL AND operator.
The following statement finds all employees who have both job id 9 and salary greater than 5,000:
SELECT first_name, last_name, job_id, salary FROM employees WHERE 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:
SELECT
first_name,
last_name,
hire_date
FROM
employees
WHERE
YEAR (hire_date) >=1997
AND YEAR (hire_date) <= 1998;
SQL AND operator and short-circuit evaluation
The short-circuit feature allows the database system to stop evaluating the remaining parts of a 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 evaluation feature works.
See the following condition:
1 = 0 AND 1 = 1;
The database system processes the two comparisons first and uses the AND
operator to evaluate the two results.
However, with the short-circuit evaluation 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 condition returns false regardless of the result of the right part of the condition.
The short-circuit feature, therefore, decreases the CPU computation time and in some cases helps prevent runtime error. Consider the following condition:
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.
0 Comments
CAN FEEDBACK
Emoji