SQLite AND Operator

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 BCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

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

(3 rows)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

Summary

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