SQLite OR Operator

Summary: in this tutorial, you will learn how to use the SQLite OR operator to combine multiple conditions in a WHERE clause to filter rows if at least one condition is true.

Introduction to SQLite OR operator

In SQLite, the OR operator allows you to combine multiple conditions in a WHERE clause to filter rows based on at least one condition being true.

Here’s the syntax of the OR operator:

A OR BCode language: SQL (Structured Query Language) (sql)

In this syntax:

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

If A and B are non-null, the OR operator returns true if either A or B is true.

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

SQLite OR operator examples

Let’s take some examples of using the OR operator. We’ll use the invoices table from the sample database for the demonstration:

1) Basic SQLite OR operator example

The following example uses the OR operator to retrieve the invoices with the billing city is New York or Chicago:

SELECT
  BillingAddress,
  BillingCity,
  Total
FROM
  invoices
WHERE
  BillingCity = 'New York'
  OR BillingCity = 'Chicago'
ORDER BY 
  BillingCity;Code language: SQL (Structured Query Language) (sql)

Output:

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

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

In this example, the OR operator includes rows whose values are the BillingCity are either New York or Chicago.

2) Combining OR operator with AND operator

The following statement uses the OR operator with the AND operator to retrieve the invoices with the billing city is either New York or Chicago and the Total is greater than 10:

SELECT
  BillingAddress,
  BillingCity,
  Total
FROM
  invoices
WHERE
  (BillingCity = 'New York' OR BillingCity = 'Chicago') AND
  Total > 10
ORDER BY
  Total;Code language: SQL (Structured Query Language) (sql)

Output:

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

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

Summary

• Use the OR operator to combine multiple conditions in a WHERE clause to filter rows based on at least one condition is true.