How to use subqueries in SQLite

SQLite is an RDBMS that is used to manage the data stored in a database and also it manages the data stored in the form of tables; by using different clauses, queries, subqueries, and built-in functions. In this article, we will discuss the sub-queries and their usage in SQLite.

What is a subquery

A subquery is a nested query, which is present inside the main query, for example, we have a statement in the figure below:

In this image, we can see clearly, the nested SELECT statement is known as a subquery or inner query, moreover, there are some principles which should be kept in mind while using the subqueries:

A subquery may occur with SELECT clause, FROM clause, UPDATE clause, DELETE clause, INSERT clause, and WHERE clause
Mostly subquery is used with WHERE clause, along with the SELECT statement of another table
Comparison operators like IN, NOT IN, >, <, and = can be used with the subqueries
Always use the parentheses () to define a subquery so that it can be differentiated from the main query
The subquery will return only one column
The subquery will return a single row, but it can return multiple rows if used with the IN operator

What is the general syntax of using the subquery

The general syntax of the subquery is as:

SELECT column_1 FROM table1
WHERE column_1=(SELECT column_1 FROM table2);

How to use subquery with the SELECT and WHERE clauses

A nested query can be used with the SELECT and WHERE clause, to understand this, we will create two tables:

CREATE TABLE John_employees (emp_id INTEGER, emp_name TEXT);
CREATE TABLE John_employees_salary (emp_id INTEGER, emp_salary INTEGER);

Now, insert some data in these newly created tables, using:

INSERT INTO John_employees VALUES (1,’Hannah’),(2,’Paul’),(3, ’Alexander’);
INSERT INTO John_employees_salary VALUES (1,50000),(2,38000),(3, 93000);

Now using the subquery, we will display the employees whose salary is greater than 38000:

SELECT*FROM John_employees WHERE emp_id IN (SELECT emp_id FROM John_employees_salary WHERE emp_salary > 40000);

The above output displayed those employees whose salaries are greater than 40000, by using the subquery it compared the values of one table to another. In the example above, “ (SELECT emp_id FROM John_employees_salary WHERE emp_salary > 40000);” is the subquery that is used in a nested statement.

How to use subquery with INSERT clause

The subqueries can also be used with the INSERT clause to insert the values from one table to the other table. To understand it, consider an example; we have a table, Paul_employees, which is similar to John_employees in table structure. Now we are copying the data of emp_names from the John_employees to Paul_employees using a subquery:

INSERT INTO Paul_employees SELECT * FROM John_employees WHERE emp_name IN (SELECT emp_name FROM John_employees);

To display the content of the Paul_employees table, we will run the statement:

SELECT emp_name FROM Paul_employees;

How to use subquery with UPDATE clause

A subquery can be used with the UPDATE clause to update the data of any table, for example, we have a table of John_employees_salary:

SELECT * FROM John_employees_salary;

We are updating the values of emp_salary, of the table John_employees_salary by 50%, of those employees that have emp_id greater than 1, so by using subquery as:

UPDATE John_employees_salary SET emp_salary = emp_salary * 1.50 WHERE emp_id IN (SELECT emp_id FROM John_employees WHERE emp_id > 1 );

To show the salaries of John_employees_salary:

SELECT * FROM John_employees_salary;

From the output, we can confirm that the salaries of the employees have been increased whose emp_id is greater than 1.

How to use subquery with DELETE clause

We can also use the subquery with the DELETE clause to delete the data from the table, to understand it, consider a table John_employees, whose data is displayed by using:

SELECT * FROM John_employees;

Now we will delete the names of those employees, who are taking salaries more than 80,000, mentioned in the table John_employees_salary using the subquery as:

DELETE FROM John_employees WHERE emp_id IN (SELECT emp_id FROM John_employees_salary WHERE emp_salary > 80000);

To confirm the changes, we will display the table John_employees:

SELECT * FROM John_employees;

Conclusion

SQLite is a serverless relational database management system that uses queries to organize the data. There are different methods in SQLite to access the data of a database, one of them is nested Queries. Nested queries which are known as subqueries are mostly required when we are modifying the data according to some conditions which are dependent on some other table. In this article, we have discussed the SQLite subqueries, and also their usage with examples.