SQLite Subqueries

  Total Views : 475
  Comments : 0
  Keywords : Subquery in sqlite with examples, Use sqlite subquery in select inner or outer query with examples, SQLite use subquery with select / insert / update statements with examples

Here we will learn SQLite subquery with example and how to use sqlite subqueries as nested queries or inner queries in select / where clause with examples.

SQLite Sub Queries

In SQLite sometimes we get requirement like need to compare one select statement result set with some other table column values in that case we will write queries within the query and we will call it as sub queries.

 

Generally, in SQLite we can use sub queries along with SELECT, UPDATE, INSERT and DELETE statements or inside of any sub queries based on our requirements.

 

Mostly, in SQLite we use sub queries with WHERE, EXISTS or IN operator along with comparison operators like >, <, =, etc. to compare result set values with table column values.

 

Following are the basic rules which we need to follow while creating sub queries in SQLite.

 

  • In SQLite sub queries always return only one column and that column must match with the column mentioned in WHERE clause of main query.
  • We can use ORDER BY or BETWEEN clauses within the sub queries but it’s not possible to use ORDER BY or BETWEEN clauses in main query along with sub queries.
  • Always we need to use parenthesis in main query to define sub queries. 

Syntax of SQLite Subquery

Following is the syntax of using sub queries in sqlite main query.

 

SELECT column-list

FROM table_name

WHERE column1 operator

(SELECT column1 FROM table_name);

If you observe above sqlite sub query syntax in WHERE clause we used column1 to compare values against sub query result set.

Example of using SQLite Subquery

Now we will see how to use sub queries in sqlite select statements with examples for that we need create two tables dept_master and emp_master and need to insert some data for that use following queries.

 

To create and insert some data in dept_master table execute following query.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control'),

('Marketing');

Once we create and insert data execute following query to see the data of dept_master table.

 

sqlite> SELECT * FROM dep_master;

 

dept_id     dept_name

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

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute following queries to create and insert some data in emp_master table.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3),

(9,'Shwets','Jariwala',19400,2);

 Now run the following query to check records of emp_master table.

 

sqlite>SELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

SQLite Subquery with Where Clause

Now write SQLite query like as shown following to use sub query with WHERE clause in SELECT statement.

 

SELECT *

FROM emp_master

WHERE salary >

(SELECT salary FROM emp_master WHERE dept_id=2);

If you observe above query we are trying to get the employees whose salary greater than the salary of employee whose dept_id is 2.

 

Following is the result of using subquery in SQLite SELECT statement.

 

emp_id      first_name  last_name   salary      dept_id

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

3           Vinay       Jariwala    35100       3

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

Here, in above example we used “>” operator with sub query. Now we will see how to use subquery with IN operator. 

SQLite Subquery with IN Operator

Now write the SQLite query like as shown following to use subquery with IN operator to return all the employees whose department is “Sales”.

 

SELECT FROM emp_master

WHERE dept_id IN

(SELECT dept_id from dept_master WHERE dept_name='Sales');

When we run above sqlite query we will get the result like as shown following.

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

4           Jagruti     Viras       9500        2

7           Yamini      Patel       10000       2

9           Shwets      Jariwala    19400       2

SQLite Subquery with NOT IN

Following is the sqlite query to use NOT IN operator with subquery.

 

SELECT FROM emp_master

WHERE dept_id NOT IN

(SELECT dept_id from dept_master WHERE dept_name='Admin');

 If you observe above example we are trying to get the employees whose department not equal to “Admin”. 

 

Following is the result of above query.

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

SQLite Subquery with Exists

Now we will see how to use subquery with EXISTS operator for that write the query like as shown following.

 

SELECT FROM emp_master

WHERE EXISTS

(SELECT FROM dept_master WHERE dept_name='Sales'

AND emp_master.dept_id dept_master.dept_id)

ORDER BY salary;

In above example we are trying to get the records from emp_master table where department name is “Sales” and dept_id in emp_master table must match with dept_id of dept_master table and return the records in ascending order of salary.

 

When we run above query we will get the result like as shown following

 

emp_id      first_name  last_name   salary      dept_id

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

4           Jagruti     Viras       9500        2

7           Yamini      Patel       10000       2

2           Shweta      Jariwala    19300       2

9           Shwets      Jariwala    19400       2

SQLite Subquery with Comparison (>=) Operator

Write the query like as shown following to use sub queries with comparison (>=) operator.

 

SELECT from emp_master

WHERE salary >=

(SELECT avg(salaryFROM emp_master);

In above example we are trying the get details of employee whose salary is greater than the average salary of employees in the firm.

 

When we run above query we will get result like as shown following

 

emp_id      first_name  last_name   salary      dept_id

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

8           Khyati      Shah        50000       3

SQLite Subquery with Insert / Update / Delete

Now, we will see how to use SQLite sub queries with INSERT, UPDATE and DELETE operations. For this, we need to create new table called person and insert data using following queries.

 

CREATE TABLE person

(ssid INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

email TEXT,

phone_no INTEGER,

city TEXT);

 

INSERT INTO person

values (1,'Vinay','Jariwala','vinay@gmail.com', 898545,'Vapi'),

(2,'Shweta','Jariwala','shweta@gmail.co','','Vapi'),

(3,'Sonal','Menpara','Sonal@gmail.com',84697,'Surat'),

(4,'Jagruti','Viras','jagu@gmail.com',656454,'Daman'),

(5,'Yamini','Patel','rani@gmail.com',98788,'Mumbai'),

(6,'Yamini','Shah','yamini@gmail.co','','Baroda');

 Once we create person table use following query to check records in person table.

 

sqliteselect from person;

 

ssid        first_name  last_name   email            phone_no    city

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

1           Vinay       Jariwala    vinay@gmail.com  898545      Vapi

2           Shweta      Jariwala    shweta@gmail.co              Vapi

3           Sonal       Menpara     Sonal@gmail.com  84697       Surat

4           Jagruti     Viras       jagu@gmail.com   656454      Daman

5           Yamini      Patel       rani@gmail.com   98788       Mumbai

6           Yamini      Shah        yamini@gmail.co              Baroda

SQLite Subquery with Insert Statement

Now we will try to insert records in emp_master from person table whose city is either Vapi or Surat.

 

INSERT INTO emp_master(first_name,last_name)

SELECT first_name,last_name from Person

WHERE city IN('Vapi','Surat');

Now, let’s check the records of emp_master table after INSERT using following query.

 

sqliteselect from emp_master;

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

10          Vinay       Jariwala

11          Shweta      Jariwala

12          Sonal       Menpara

SQLite Subquery with Update Statement

Now we will see how to use subquery in SQLite UPDATE statement with example. Following is the example of using subquery in UPDATE statement.

 

UPDATE emp_master set

salary = salary-100

WHERE salary >

(SELECT avg(salaryFROM emp_master);

In above query we are reducing the salary of employees by 100rs whose salary is greater than average salary. 

 

Once we run above query we will get result like as shown below.

 

sqliteselect from emp_master;

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35000       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        49900       3

9           Shwets      Jariwala    19400       2

10          Vinay       Jariwala

11          Shweta      Jariwala

12          Sonal       Menpara

If you observe above result 2 employees (vinary and khyati) salary is deducted by 100rs.

SQLite Subquery with DELETE Statement

Now we will see how to use subquery with SQLite DELETE statement with example.

 

Let’s consider emp_master table is having following set of records.

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35000       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        49900       3

9           Shwets      Jariwala    19400       2

10          Vinay       Jariwala    20000       4

11          Shweta      Jariwala    20000       4

12          Sonal       Menpara     20000       4

Another table dept_master is having following records.

 

sqliteSELECT FROM dept_master;

 

dept_id     dept_name

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

1           Admin

2           Sales

3           Quality Co

4           Marketing

Now we will delete the employees from emp_master table whose last_name is “jariwala” and belongs to department “Marketing”. 

 

By using following query we can delete the records from emp_master table based on our requirements.

 

DELETE FROM emp_master

WHERE last_name='Jariwala'AND

dept_id IN

(SELECT dept_id FROM dept_master

WHERE dept_name='Marketing');

 When we run above query it will delete the records from emp_master table whose department equal to “Marketing”. 

 

By using following query we can see the remaining records of emp_master table.

 

sqliteselect from emp_master;

 

emp_id      first_name  last_name   salary      dept_id

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

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35000       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        49900       3

9           Shwets      Jariwala    19400       2

12          Sonal       Menpara     20000       4

This is how we can use SQLite subqueries in databases based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User