SQLite - AND & OR Clauses

SQLite - Combining Conditions with AND & OR

The AND and OR operators are conjunctive operators used in the WHERE clause to filter data based on multiple conditions. They are fundamental to building powerful and precise queries.

The AND Operator

The AND operator returns a row only if all specified conditions evaluate to true. It is used to narrow down a search by adding successive requirements.

Syntax

SELECT column_list
FROM table_name
WHERE [condition1] AND [condition2] AND ... [conditionN];

Example

Using our employees table, let’s find all employees who are older than 25 and work in the ‘Sales’ department.

SELECT * FROM employees WHERE age > 25 AND department = 'Sales';

id | name | age | address    | salary
---|------|-----|------------|--------
1  | Paul | 32  | California | 50000.0

This query returns only ‘Paul’ because he is the only employee who meets both criteria.

The OR Operator

The OR operator returns a row if any of the specified conditions evaluate to true. It is used to broaden a search by including rows that match one of several criteria.

Syntax

SELECT column_list
FROM table_name
WHERE [condition1] OR [condition2] OR ... [conditionN];

Example

Let’s find all employees who are older than 30 or earn a salary greater than 60000.00.

SELECT * FROM employees WHERE age > 30 OR salary > 60000;

id | name | age | address    | salary
---|------|-----|------------|--------
1  | Paul | 32  | California | 50000.0
4  | Mark | 25  | Rich-Mond  | 65000.0
5  | David| 27  | Texas      | 85000.0

‘Paul’ is included because his age is over 30. ‘Mark’ and ‘David’ are included because their salaries are over 60000, even though their ages are not over 30.

Best Practice: Combining AND and OR with Parentheses

A common source of bugs is misunderstanding operator precedence. SQLite evaluates AND operators before OR operators. To ensure your logic is executed correctly and to improve readability, always use parentheses () to group your conditions explicitly.

Imagine you want to find all employees from ‘Texas’ or ‘California’ who are older than 25. Without parentheses, the query is ambiguous.

-- Incorrect and ambiguous query
SELECT * FROM employees WHERE address = 'Texas' OR address = 'California' AND age > 25;

The correct way to write this is by grouping the OR conditions:

-- Correct and clear query
SELECT * FROM employees
WHERE (address = 'Texas' OR address = 'California') AND age > 25;

id | name | age | address    | salary
---|------|-----|------------|--------
1  | Paul | 32  | California | 50000.0
5  | David| 27  | Texas      | 85000.0

Using parentheses removes all ambiguity and ensures your query behaves exactly as intended.