Home > DeveloperSection > Blogs > AND and OR Operators of SQLite

AND and OR Operators of SQLite


SQLite Database  Sqlite  Sqlite3 
Ratings:
0 Comment(s)
 618  View(s)
Rate this:

AND and OR Operators of SQLite


In this blog we will discuss about SQLite AND and OR operators are that are used to execute multiple conditions to narrow down the selected data in an SQLite statement. Both of these two operators known as conjunctive operators. AND and OR operators also used for making multiple comparisons with different operators with the same SQLite statement.

                         

AND Operator: AND operator allows the existence of multiple conditions in an SQLite statement's WHERE clause. AND returns TRUE if both inputs are TRUE (if 'this' AND 'that' are true). (1) AND (0) would evaluate to zero because one of the inputs is false (both must be TRUE for it to evaluate to TRUE). (1) AND (1) evaluates to 1. (any number but 0) AND (0) evaluates to 0. All of the above means that If, and only if, both expressions evaluate to True, result is True. If either expression evaluates to False, result is False.

 

The following table illustrates how result is determined:

 

If expression1 is

And expression2 is

The result is

True

True

True

True

False

False

True

Null

Null

False

True

False

False

False

False

False

Null

False

Null

True

Null

Null

False

False

Null

Null

Null

 

Syntax:


SELECT column1, column2, columnN

FROM table_name

WHERE [condition1] AND [condition2]...AND [conditionN];

We can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.

 

Example: there is an example of COMPANY table which has following records:


ID

NAME

AGE

ADDRESS

SALARY

1

Ajay

32

Calcutta 

20000.0

2

Vijay

25

Delhi

15000.0

3

Vinay

23

Varansi

20000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

6

Preeti

22

Mumbai

45000.0

7

Neetu

24

Raebareli

10000.0

 

Here is an example of AND operator with SELECT statement that lists down all the records where AGE is greater than or equal to 25 AND 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

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

 

OR Operator: OR operator is also used to combine multiple conditions in an SQLite statement's WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true. If either or both expressions evaluate to True, result is True.

 

The following table illustrates how result is determined:


If expression1 is

And expression2 is

Then result is

True

True

True

True

False

True

True

Null

True

False

True

True

False

False

False

False

Null

Null

Null

True

True

Null

False

Null

Null

Null

Null

 

Syntax:


SELECT column1, column2, columnN

FROM table_name

WHERE [condition1] OR [condition2]...OR [conditionN]

We can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.

Example: Consider COMPANY table is having the following records:


ID

NAME

AGE

ADDRESS

SALARY

1

Ajay

32

Calcutta 

20000.0

2

Vijay

25

Delhi

15000.0

3

Vinay

23

Varansi

20000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

6

Preeti

22

Mumbai

45000.0

7

Neetu

24

Raebareli

10000.0

 

Here is an example of OR operator with SELECT statement that lists down all the records where AGE is greater than or equal to 25 OR 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

Ajay

32

Calcutta

20000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

 


Don't want to miss updates? Please click the below button!

Follow MindStick