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:
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
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
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
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
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.