SQLite AND/OR Operators

SQLite'sANDandOROperators are used to compile multiple conditions to narrow down the data selected in SQLite statements. These two operators are called conjunctive operators.

These operators provide the possibility for multiple comparisons between different operators in the same SQLite statement.

AND Operator

ANDThe operator allows the existence of multiple conditions in the WHERE clause of a SQL statement. When using the AND operator, the entire condition is true only when all conditions are true. For example, [condition1] AND [condition2] is true only when both condition1 and condition2 are true.

Syntax

The basic syntax of the AND operator with a WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can use the AND operator to combine N number of conditions. The action that the SQLite statement needs to perform, whether it is a transaction or a query, is that all conditions separated by AND must be TRUE.

Example

Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following SELECT statement lists all records where AGE is greater than or equal to 25andand SALARY is greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

OR Operator

ORThe operator is also used to combine multiple conditions in the WHERE clause of a SQL statement. When using the OR operator, the entire condition is true as long as any one of the conditions is true. For example, [condition1] OR [condition2] is true as long as either condition1 or condition2 is true.

Syntax

The basic syntax of the OR operator with a WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can use the OR operator to combine N number of conditions. The action that the SQLite statement needs to perform, whether it is a transaction or a query, only requires that any one of the conditions separated by OR is TRUE.

Example

Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following SELECT statement lists all records where AGE is greater than or equal to 25orand SALARY is greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
Other Extensions