Summary: in this tutorial, you will learn how to use SQLite SELECT statement to query data from a table.
SELECT statement is the most commonly used statements in SQL. The SQLite
SELECT statement provides all features of the
SELECT statement in standard SQL.
Simple uses of SELECT statement
You can use the
SELECT statement to perform a simple calculation as follows:
1 + 1;
You can use multiple expressions in the
SELECT statement as follows:
10 / 5, 2 * 4 ;
Querying data from a table using SELECT statement
You often use the
SELECT statement to query data from one or more table. The syntax of the
SELECT statement is as follows:
SELECT DISTINCT column_list
JOIN table ON join_condition
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
SELECT statement is the most complex statement in SQLite. To help easier to understand each part, we will break the
SELECT statement into multiple easy-to-understand tutorials.
- Use ORDER BY clause to sort the result set
- Use DISTINCT clause to query unique rows in a table
- Use WHERE clause to filter rows in the result set
- Use LIMIT OFFSET clauses to constrain the number of rows returned
- Use INNER JOIN or LEFT JOIN to query data from multiple tables using join.
- Use GROUP BY to get the group rows into groups and apply aggregate function for each group.
- Use HAVING clause to filter groups
In this tutorial, we are going to focus on the simplest form of the
SELECT statement that allows you to query data from a single table.
SELECT clause appears before the
FROM clause, SQLite evaluates the
FROM clause first and then the
SELECT clause, therefore:
- First, you specify the table where you want to get data from in the
FROMclause. Notice that you can have more than one table in the
FROMclause. We will discuss it in the subsequent tutorial.
- Second, you specify a column or a list of comma-separated columns in the
You use the semicolon (;) to terminate the statement.
SQLite SELECT examples
Let’s take a look at the
tracks table in the sample database.
tracks table contains columns and rows. It looks like a spreadsheet.
To get data from the tracks table such as trackid, track name, composer, and unit price, you use the following statement:
You specify a list column names, which you want to get data, in the
SELECT clause and the
tracks table in the
FROM clause. SQLite returns the following result:
To get data from all columns, you specify the columns of the
tracks table in the
SELECT clause as follows:
For a table with many columns, the query would be so long that time-consuming to type. To avoid this, you can use the asterisk (*), which is the shorthand for all columns of the table as follows:
The query is shorter and cleaner now.
You should use the asterisk (*) for the testing purpose only, not in the real application development.
When you develop an application, you should control what SQLite returns to your application. Suppose, a table has 3 columns, and you use the asterisk (*) to retrieve the data from all three columns.
What if someone removes a column, your application would not be working properly, because it assumes that there are three columns returned and the logic to process those three columns would be broken.
If someone adds more columns, your application may work but it gets more data than needed, which creates more I/O overhead between the database and application.
So try to avoid using the asterisk (*) as a good habit when you use the
In this tutorial, you have learned how to use a simple form of the SQLite
SELECT statement to query data from a single table.