SQLite  - SELECT Query 

SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of result table. These result tables are also called result-sets.


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

SELECT expressions

FROM tables

WHERE conditions;

However, the full syntax for the SQLite SELECT statement is:



FROM tables

WHERE conditions

[ GROUP BY expressions ]

[ HAVING condition ]

[ ORDER BY expression [ ASC | DESC ] ]

[ LIMIT number_rows OFFSET offset_value ];


Parameters or Arguments


ALL : Optional. If specified, it returns all matching rows.

DISTINCT : Optional. If specified, it removes duplicates from the result set.

Expressions : The columns or calculations that you wish to retrieve.

Tables : The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

Conditions : The conditions that must be met for the records to be selected.

GROUP BY : Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING : Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY : Optional. It is used to sort the records in your result set. Learn more about the ORDER BY clause.

LIMIT : Optional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.


Example - Select all fields from one table



FROM employees

WHERE employee_id< 150

ORDER BY last_name ASC;

In this example, we've used * to signify that we wish to select all fields from the employees table where the employee_id is less than 150. The result set is sorted by last_name in ascending order.


Example - Select individual fields from one table


SELECT employee_id, last_name, first_name

FROM employees

WHERE employee_id < 150

ORDER BY last_name ASC, employee_id DESC;

In this example we fetch individual fields from the table, as opposed to all fields from the table. Here we would return only the employee_idlast_name, and first_name fields from the employees table where the employee_id is less than 150. The results are sorted by last_name in ascending order and then employee_id in descending order.


Example - Select fields from multiple tables


SELECT employees.employee_id, employees.last_name, positions.title

FROM employees

INNER JOIN positions

ON employees.employee_id = positions.employee_id

ORDER BY positions.title;

In this example we joins two tables together to gives us a result set that displays the employee_id, last_name, and title fields where the employee_id value matches in both the employees and positions table. The results are sorted by title in ascending order.

Leave Comment