Home > DeveloperSection > Articles > SQLite – where clause

SQLite – where clause


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

SQLite – where clause 


In this article we are going see the SQLite WHERE clause that is used to specify a condition while fetching the data from one table or multiple tables.


If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL.

 

The WHERE clause not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would study in subsequent chapters.


Syntax: The basic syntax of SQLite SELECT statement with WHERE clause is as follows:

 

SELECT column1, column2, columnN

FROM table_name

WHERE [condition]

 

Example: We can specify a condition using Comparison or Logical Operators like >, <, =, LIKE, NOT, etc. Consider COMPANY table has 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 are simple examples showing usage of SQLite Logical Operators. Following SELECT statement 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

 

Following SELECT statement 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

2

Vijay

25

Delhi

15000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

 

Following SELECT statement lists down all the records where AGE is not NULL which means all the records because none of the record is having AGE equal to NULL:

 

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

 

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

 

Following SELECT statement lists down all the records where NAME starts with 'Pr', does not matter what comes after 'Pr'.

 

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Pr%';

 


ID

NAME

AGE

ADDRESS

SALARY

6

Preeti

22

Mumbai

45000.0

 

Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki':

 

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Pr*';

 


ID

NAME

AGE

ADDRESS

SALARY

6

Preeti

22

Mumbai

45000.0

 

Following SELECT statement lists down all the records where AGE value is either 25 or 27:

 

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

ID

NAME

AGE

ADDRESS

SALARY

2

Vijay

25

Delhi

15000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

 

Following SELECT statement lists down all the records where AGE value is neither 25 nor 27:

 

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

 

ID

NAME

AGE

ADDRESS

SALARY

1

Ajay

32

Calcutta

20000.0

3

Vinay

23

Varansi

20000.0

6

Preeti

22

Mumbai

45000.0

7

Neetu

24

Raebareli

10000.0

 

Following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27:

 

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

 

ID

NAME

AGE

ADDRESS

SALARY

2

Vijay

25

Delhi

15000.0

4

Subhash

25

Punjab

65000.0

5

Suresh

27

Lucknow

85000.0

 

Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query:

sqlite> SELECT AGE FROM COMPANY


        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

 


AGE

32

25

23

25

27

22

24

 

Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query:

sqlite> SELECT * FROM COMPANY



        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

 


ID

NAME

AGE

ADDRESS

SALARY

1

Ajay

32

Calcutta

20000.0

 


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

Follow MindStick