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:
However, the full syntax for the SQLite SELECT statement is:
SELECT [ ALL | DISTINCT ]
[ 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
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
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
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.