SQLite SELECT Statement: Query Data with Tested Examples

Ninad Pathak

October 7, 2026

Reading data out of an SQLite database is the job of SELECT. SELECT * shows a whole table, and the rest of the statement narrows down and combines what comes back. In this article I’ll walk you through the SQLite SELECT statement, from picking columns to joining tables and summarising rows.

TLDR

SELECT * FROM users; returns every column of every row.
List columns to get only those: SELECT name, city FROM users;.

Filter rows with WHERE, and sort them with ORDER BY.
GROUP BY with functions such as count() summarises rows, and JOIN combines tables.
SELECT also works without a table, as a calculator: SELECT 7 * 6;.

What is the SELECT statement in SQLite?

SELECT reads data. It returns rows from one or more tables, or the result of expressions, and never changes the database. In SQLite’s words, “The SELECT statement is used to query the database” (SQLite SELECT).

SQLite SELECT syntax

The clauses always come in this order, and each one is optional except the column list:

SELECT [DISTINCT] columns_or_expressions
FROM table_name
[JOIN other_table ON condition]
[WHERE condition]
[GROUP BY columns [HAVING condition]]
[ORDER BY columns [ASC | DESC]]
[LIMIT count [OFFSET skip]];

How to query a table with SELECT

To show you how this works, I will use a small shop database with a users table and an orders table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  age INTEGER
);
INSERT INTO users (name, city, age) VALUES
  ('Ada', 'London', 36), ('Grace', 'New York', 45), ('Linus', 'Helsinki', 28),
  ('Margaret', 'Boston', 33), ('Ken', 'New York', 52);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total REAL NOT NULL
);
INSERT INTO orders (user_id, total) VALUES (1, 25.5), (1, 40.0), (2, 12.0), (4, 99.9), (5, 15.0), (5, 7.5);

Step 1: Select every column

* means all columns:

sqlite> SELECT * FROM users;
id  name      city      age
--  --------  --------  ---
1   Ada       London    36
2   Grace     New York  45
3   Linus     Helsinki  28
4   Margaret  Boston    33
5   Ken       New York  52

Step 2: Select only the columns you need

Naming the columns keeps the result small and the order fixed:

sqlite> SELECT name, city FROM users;
name      city
--------  --------
Ada       London
Grace     New York
Linus     Helsinki
Margaret  Boston
Ken       New York

Step 3: Rename columns and compute values

AS gives a column a new name in the result, and any expression can be a column:

sqlite> SELECT name AS customer, age + 1 AS age_next_year, upper(city) AS city FROM users LIMIT 2;
customer  age_next_year  city
--------  -------------  --------
Ada       37             LONDON
Grace     46             NEW YORK

Step 4: Filter rows with WHERE

sqlite> SELECT name, age FROM users WHERE city = 'New York' AND age > 40;
name   age
-----  ---
Grace  45
Ken    52

See SQLite WHERE clause for every operator.

Step 5: Sort and limit the result

sqlite> SELECT name, age FROM users ORDER BY age DESC LIMIT 3;
name   age
-----  ---
Ken    52
Grace  45
Ada    36

See ORDER BY and LIMIT.

Examples

These examples go past the basics, from unique values to queries with no table at all.

List unique values with DISTINCT

sqlite> SELECT DISTINCT city FROM users ORDER BY city;
city
--------
Boston
Helsinki
London
New York

Summarise all rows with aggregate functions

sqlite> SELECT count(*) AS users, avg(age) AS avg_age, min(age) AS youngest FROM users;
users  avg_age  youngest
-----  -------  --------
5      38.8     28

Count per group with GROUP BY and HAVING

HAVING filters groups, the way WHERE filters rows:

sqlite> SELECT city, count(*) AS users FROM users GROUP BY city HAVING count(*) > 1;
city      users
--------  -----
New York  2

Combine two tables with a JOIN

A LEFT JOIN keeps users with no orders, and coalesce() turns their missing total into 0:

sqlite> SELECT u.name, count(o.id) AS orders, coalesce(sum(o.total), 0) AS spent FROM users AS u LEFT JOIN orders AS o ON o.user_id = u.id GROUP BY u.id ORDER BY spent DESC;
name      orders  spent
--------  ------  -----
Margaret  1       99.9
Ada       2       65.5
Ken       2       22.5
Grace     1       12.0
Linus     0       0

Use a subquery in WHERE

sqlite> SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 30);
name
--------
Ada
Margaret

SELECT without a table

With no FROM, SELECT evaluates expressions, which makes the sqlite3 shell a handy calculator and date tool:

sqlite> SELECT 7 * 6 AS answer, date('2026-10-07', '+1 month') AS next_month, sqlite_version() AS version;
answer  next_month  version
------  ----------  -------
42      2026-11-07  3.51.0

Conclusion

Start with the columns you need instead of *, then add filters and sorting as the question gets more specific. Reach for GROUP BY and JOIN when you need totals or data from more than one table. If you’re opening an unfamiliar database, list its tables first.