SQLite SELECT Statement

The SELECT statement is used to query data from SQLite database tables. It is one of the most commonly used SQL statements.

Let’s assume we have a Users table with some data:

id | name       | age | email
-----------------------------------
1  | John Doe   | 24  | [email protected]
2  | Jane Doe   | 35  | [email protected]  
3  | Bob Smith  | 27  | [email protected]
4  | Sam Wilson | 40  | [email protected]

The SELECT statement is used to query data from SQLite database tables like this Users table. It is one of the most commonly used SQL statements.

SQLite SELECT Statement Syntax

SELECT column1, column2, columnN 
FROM table_name;

To query all columns from a table, use the asterisk (*) wildcard:

SELECT * FROM table_name;

Also read: SQLite Show Tables

Querying Specific Columns

To query only specific columns from a table, specify the column names after SELECT:

SELECT id, name, email 
FROM users;

Output

id          name        email
----------  ----------  ----------------  
1           John Doe    [email protected]
2           Jane Doe    [email protected] 
3           Bob Smith   [email protected]

This will return only the id, name, and email columns for all rows in the users table.

Filtering SELECT Statement Results with WHERE

To filter results, add a WHERE clause with a condition:

SELECT * 
FROM users
WHERE age > 30;

Output:

id          name        age        email
----------  ----------  ----------  ----------------
2           Jane Doe    35         [email protected]
4           Sam Wilson  40         [email protected]

This will return only users whose age is greater than 30.

You can use comparison operators like >, <, =, !=, etc. as well as AND/OR logical operators.

Sorting SQLite SELECT Statement Results with ORDER BY

To sort results in ascending or descending order, use ORDER BY:

SELECT * 
FROM users
ORDER BY age DESC;

Output:

id          name        age        email
----------  ----------  ----------  ----------------  
4           Sam Wilson  40         [email protected]
2           Jane Doe    35         [email protected]
3           Bob Smith   27         [email protected]
1           John Doe    24         [email protected]

This will return all users sorted by age in descending order (high to low).

Also check: SQLite Expressions

Limiting Results with LIMIT

To limit the number of rows returned, use LIMIT:

SELECT *
FROM users
LIMIT 2;

Output:

id          name        age        email
----------  ----------  ----------  ----------------
4           Sam Wilson  40         [email protected]
2           Jane Doe    35         [email protected]

This will return only the first two rows from the users table.

Query Examples

Here are some example SELECT queries:

Get names for users over the age of 30

SELECT name
FROM users 
WHERE age > 30;

Output:

name
----------  
Jane Doe
Sam Wilson

Get 10 oldest users ordered by age descending

SELECT *
FROM users
ORDER BY age DESC
LIMIT 10;

Output:

id          name        age        email 
----------  ----------  ----------  ----------------
4           Sam Wilson  40         [email protected]  
2           Jane Doe    35         [email protected]
...

Get number of users in each city

SELECT city, COUNT(*) as user_count 
FROM users
GROUP BY city;

Output:

city        user_count
----------  ----------
Boston         2
Miami          1 
Chicago        3

Conclusion

This covers the basics of querying data from SQLite tables using SELECT statements. With the flexibility of conditions, sorting, limiting and grouping, SELECT provides powerful ways to retrieve and organize your data.