SQLite Subqueries

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 requirements 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 subqueries.

 

Generally, in SQLite we can use subqueries along with SELECT, UPDATE, INSERT and DELETE statements or inside of any subqueries 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 subqueries 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 subqueries but it’s not possible to use ORDER BY or BETWEEN clauses in main query along with subqueries.
• Always we need to use parenthesis in main query to define sub queries. 

Syntax of SQLite Subquery

Following is the syntax of using subqueries in sqlite main query.

 

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

Example of using SQLite Subquery

Now we will see how to use subqueries 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.

 

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

 

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

 

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

 

SQLite Subquery with Where Clause

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

 

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.

 

Here, in above example we used “>” operator with a subquery. 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”.

 

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

 

SQLite Subquery with NOT IN

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

 

 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.

 

SQLite Subquery with Exists

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

 

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

 

SQLite Subquery with Comparison (>=) Operator

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

 

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 a result like as shown following

 

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 a new table called a person and insert data using the following queries.

 

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

 

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.

 

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

 

SQLite Subquery with Update Statement

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

 

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.

 

If you observe the 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 the emp_master table is having the following set of records.

 

Another table dept_master is having following records.

 

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

 

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

 

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

 

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

 

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