SQLite Select Query

SQLite Select Statement

In SQLite SELECT statement is used to get data from tables in database. Generally, sqlite select statement will return result set in the form of table and by using 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 database, but SELECT can also be used to return the value of simple expressions.

Following simple example of SQLite select statement 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 statement to query data from tables in 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, select statement in our databases to get data based on our requirements. We will learn each property in detailed in 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 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 required data from tables.

GROUP BY expr-list - The GROUP BY expr-list in SQLit 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 limit on 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 give 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 use of SELECT statement with tables, let’s create 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 simple example of SQLite SELECT statement for fetching name of employee and salary of employee from 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 column of 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 comma separated list of tables with 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 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

 Here e and d are alias name given to emp_master and dept_master table respectively.