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_id, last_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.

Last updated:3/31/2019 5:43:49 PM


Leave Comment