SQLite: SELECT Statement

This SQLite post explains how to use the SQLite SELECT statement with syntax and examples.

Description

The SQLite SELECT statement is used to retrieve records from one or more tables in SQLite.

Syntax

In its simplest form, the syntax for the SQLite SELECT statement is:

SELECT expressions
FROM tables
[WHERE conditions];

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

SELECT [ ALL | DISTINCT ]
expressions
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. Learn more about the DISTINCT clause.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

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

WHERE conditions

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

GROUP BY expressions

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

HAVING condition

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. Learn more about the HAVING clause.

ORDER BY expression

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

LIMIT number_rows OFFSET offset_value

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

Let's look at how to use a SQLite SELECT query to select all fields from a table.

SELECT *
FROM employees
WHERE employee_id  50
ORDER BY last_name ASC;

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

Example - Select individual fields from one table

You can also use the SQLite SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id  50
ORDER BY last_name ASC, employee_id DESC;

This SQLite SELECT example would return only the employee_id, last_name, and first_name fields from the employees table where the employee_id is less than 50. The results are sorted by last_name in ascending order and then employee_id in descending order.

Example - Select fields from multiple tables

You can also use the SQLite SELECT statement to retrieve 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;

This SQLite SELECT example 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.