SQLite AND Operator

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to use the SQLite AND operator to combine two conditions in a WHERE clause to filter rows based on all conditions being true.

Introduction to SQLite AND operator #

In SQLite, the AND operator allows you to combine two conditions in a WHERE clause to filter rows if all conditions are true.

Here’s the syntax of the AND operator:

A AND BCopy

In this syntax:

• A and B are boolean expressions that evaluate a value of true, false, and null.

The AND operator returns true only if A and B are true.

The following table illustrates the result of the AND operator when combining two conditions A and B:

SQLite AND operator examples #

Let’s explore some examples of using the AND operator. We’ll use the invoices table from the sample database:

1) Basic SQLite AND operator example #

The following statement uses the AND operator in the WHERE clause to get the invoices whose billing city is New York and Total is greater than 5:

SELECT
  BillingAddress,
  BillingCity,
  Total
FROM
  invoices
WHERE
  BillingCity = 'New York'
  AND Total > 5
ORDER BY
  Total;Copy

Output:

BillingAddress | BillingCity | Total
---------------+-------------+------
627 Broadway   | New York    | 5.94
627 Broadway   | New York    | 8.91
627 Broadway   | New York    | 13.86

(3 rows)Copy

In this example, the AND operator includes only rows whose values are the BillingCity are New York and values in the Total column are greater than 5.

2) Combining AND operator with OR operator #

The following example uses the AND operator with the OR operator to retrieve the invoices with the billing city as either New York or Chicago and the Total greater than 5.

SELECT
  BillingAddress,
  BillingCity,
  Total
FROM
  invoices
WHERE
  Total > 5 AND
  (BillingCity = 'New York' OR BillingCity = 'Chicago')
ORDER BY
  Total;Copy

Output:

BillingAddress        | BillingCity | Total
----------------------+-------------+------
627 Broadway          | New York    | 5.94
162 E Superior Street | Chicago     | 5.94
162 E Superior Street | Chicago     | 7.96
162 E Superior Street | Chicago     | 8.91
627 Broadway          | New York    | 8.91
627 Broadway          | New York    | 13.86
162 E Superior Street | Chicago     | 15.86

(7 rows)Copy

Summary #

• Use the AND operator to combine multiple conditions in a WHERE clause to filter rows if all conditions are true.