SQLite Select Query

Here we will learn SQLite select query with example and how to use SQLite select statement to get data from a single table or multiple tables with example.

SQLite Select Statement

The SQLite SELECT statement is used to get data from tables in the database. Generally, sqlite select statement will return the result set in the form of a table and by using the sqlite select statement we can perform simple calculations or multiple expressions based on our requirements.

SQLite SELECT is a read-only command and it will perform all the operations like standard SQL select statement.

Generally, in SQLite select statement the returned values are derived from the contents of the database, but SELECT can also be used to return the value of simple expressions.

Following is the example of SQLite select statements with simple expressions.

SELECT 1+1, 'abc' || 'xyz'

The above SQLite select statement example gives the output as following

1+1 'abc'||'def'

---------- ----------

2 abcxyz

Syntax of SQLite Select Statement

Following is the syntax of using sqlite select statements to query data from tables in the database.

SELECT [ALL | DISTINCT] result [FROM table-list]

[WHERE expr]

[GROUP BY expr-list]

[HAVING expr]

[compound-op select]*

[ORDER BY sort-expr-list]

[LIMIT integer [(OFFSET|,) integer]]

If you observe above sqlite select statement syntax we defined all possible ways of using, the select statement in our databases to get data based on our requirements. We will learn each property in detail in the next chapters.

DISTINCT - If we use distinct keyword in our SQLite select statement it returns only distinct rows of data.
ALL - If we use ALL keyword in a select statement it returns all the rows of data even if it is duplicated.
table-list - It is a list of tables from which you want to get data.
WHERE expr - The WHERE expr is used to define our custom conditions to get the required data from tables.
GROUP BY expr-list - The GROUP BY expr-list in SQLite is used to combine one or more rows of result into a single row of output. This is especially useful when the result contains aggregate functions.
HAVING expr - The HAVING expr is similar to WHERE except that HAVING applies after grouping has occurred.
ORDER BY sort-expr-list - The ORDER BY sort-expr-list causes the output rows to be sorted.
LIMIT integer - The LIMIT integer is used to set a limit on the number of rows returned in the result. The optional OFFSET integer following LIMIT specifies how many rows to skip at the beginning of the result set.

The only required item in a SELECT statement is the result, which can be one of the following:

The * character
A comma-separated list of one or more column names
An expression

SQLite Select Statement Examples

Following is the simple example of using sqlite select statement.

SELECT a, b FROM test;

If you write sqlite select statement as follows it gives error:

SELECT a+1, b+1 FROM test;

Here in SQLite select statement FROM clause is an optional if you are not fetching any data from database and you just giving expression or any SQLite functions like as shown below.

sqlite> SELECT (60 * 60 * 24);

86400

sqlite> SELECT random();

22086

SQLite Select Statement with Single Table

For illustrating the use of SELECT statement with tables, let’s create a table called emp_master as follows:

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC);

Let’s insert some records in emp_master table like as shown below

INSERT INTO emp_master

values (1,"Honey","Patel",10100),

(2,"Shweta","Jariwala", 19300),

(3,"Vinay","Jariwala", 35100);

Let’s look at a simple example of SQLite SELECT statement for fetching the name of the employee and salary of an employee from the emp_master table.

sqlite> SELECT first_name, salary FROM emp_master;

first_name  salary
----------  ----------
Honey       10100
Shweta      19300
Vinay       35100

If you want to fetch data of all columns of the employee table, then use * instead of writing each field like as shown below.

sqlite> SELECT * from emp_master;

emp_id      first_name  last_name   salary
----------  ----------  ----------  ----------
1           Honey       Patel       10100
2           Shweta      Jariwala    19300
3           Vinay       Jariwala    35100

SQLite Select Statement with Multiple Tables

We can also get data from multiple tables by using a comma separated list of tables with the FROM clause in SQLite as follows. To check this we will create another table called dept_master like as shown below.

CREATE TABLE dept_master(dept_id INTEGER PRIMARY KEY AUTOINCREMENT, dept_name TEXT);

Once the dept_master table created insert some values like as shown below.

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control');

Now let’s look at the example of fetching emp_name and its department name from emp_master, dept_master table.

sqlite>SELECT e.first_name, d.dept_name FROM emp_master e, dept_master d;

first_name  dept_name
----------  ----------
Honey       Admin
Honey       Sales
Honey       Quality Co
Shweta      Admin
Shweta      Sales
Shweta      Quality Co
Vinay       Admin
Vinay       Sales
Vinay       Quality Co