SQL AND Operator

Summary: This tutorial introduces you to the SQL AND operator and shows you how to apply it to form flexible conditions in the WHERE clause of a query.

Introduction to SQL AND operator #

The AND operator is a logical operator that combines two Boolean expressions in the WHERE clause of the SELECT, UPDATE, or DELETE statement.

Here’s the syntax of the AND operator:

expression1 AND expression2;

The AND operator returns true if both expressions are evaluated to true. If one of the two expressions is false or null, then the AND operator returns false even if one of the expressions is NULL.

The following table illustrates the results of the AND operator when combining true, false, and NULL values using the AND operator:

SQL AND operator examples #

We will use the employees table from the sample database to demonstrate the AND operator.

The following statement finds all employees who have both job id 9 and a salary greater than 5,000:

SELECT
  first_name,
  last_name,
  job_id,
  salary
FROM
  employees
WHERE
  job_id = 9
  AND salary > 5000;

Try it

Output:

 first_name | last_name | job_id | salary
------------+-----------+--------+---------
 Alexander  | Hunold    |      9 | 9000.00
 Bruce      | Ernst     |      9 | 6000.00

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
  EXTRACT(year from hire_date) >= 1998
  AND EXTRACT(year from hire_date) <= 1999
ORDER BY hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name  | hire_date
-------------+-------------+------------
 Valli       | Pataballa   | 1998-02-05
 Jose Manuel | Urman       | 1998-03-07
 Jonathon    | Taylor      | 1998-03-24
 Jack        | Livingston  | 1998-04-23
 Irene       | Mikkilineni | 1998-09-28
 Guy         | Himuro      | 1998-11-15
 Diana       | Lorentz     | 1999-02-07
 Kimberely   | Grant       | 1999-05-24
 Karen       | Colmenares  | 1999-08-10
 Luis        | Popp        | 1999-12-07

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 short-circuit evaluation feature works.

See the following statement:

SELECT
  1 = 0
  AND 1 = 1 AS result;Code language: PHP (php)

Try it

Output:

 result
--------
 f

The database system processes the two comparisons and uses the AND operator to evaluate the two results.

Due to the short-circuit evaluation feature, the database system has to evaluate the first expression only:

1 = 0

The reason is that the first expression returns false that causes the whole condition to returns false regardless of the result of the second expression.

The short-circuit feature decreases the CPU computation and, in some cases, helps prevent runtime errors. For example:

SELECT
  1 = 0
  AND 1 / 0 = 1 AS result;Code language: PHP (php)

Try it

Output:

 result
--------
 f

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.

Summary #

• Use the AND operator to combine two Boolean expressions.
• The AND operator returns true if both expressions are evaluated to true.

Databases #

• Oracle AND operator
• SQL Server AND operator
• MySQL AND operator
• SQLite AND operator
• Db2 AND operator