A nested SELECT statement is a statement in which a SELECT statement is used within another SELECT statement. Nested SELECT queries or subqueries are used in SQLite to perform complex operations.

This article explains how to use nested SELECT statements in SQLite by covering all the basic structures and concepts of nested SELECT Statements in SQLite. It will also cover the practical applications of nested SELECT queries with examples.

Nested Select Statement in SQLite

In SQLite, a nested SELECT statement refers to a SELECT statement that is embedded in another SELECT statement. A nested SELECT statement can be divided into two parts i.e. parent query and a subquery.

In most cases, the result of the parent query depends on the result of the child query or subquery.

Nested SELECT statements are used to filter results. Filtering of results is a basic task in data analysis.

Syntax

SELECT column_name_01FROM table_name_01WHERE column_name_01 IN (SELECT column_name_02 FROM table_name_02);

Demo SQL Database

Here are two tables: horje and courses. These tables will used in examples to explain practical applications of nested SELECT statements.

horje table:

Table – horje

To create this table on your system, write the following queries:

 CREATE TABLE horje 
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    questions INTEGER,
    potd_streak INTEGER
);
INSERT INTO horje (id, name, questions, potd_streak)
VALUES
    (1001, 'Vishu', 550, 300),
    (1002, 'Neeraj', 510, 290),
    (1003, 'Aayush', 520, 50),
    (1004, 'Sumit', 450, 120),
    (1005, 'Vivek', 365, 260);

courses table:

Table – courses

To create this table on your system, write the following queries:

CREATE TABLE courses 
(
    id INTEGER ,
    name TEXT,
    course_name TEXT,
    duration INTEGER
);
INSERT INTO courses(id, name, course_name, duration)
VALUES (1001, 'Vishu', 'Python', 6),  -- Vishu, Python (Assuming this is the intended course)
       (1004, 'Sumit', 'Python', 6),
       (1003, 'Aayush', 'Java', 5),
       (1002, 'Neeraj', 'Javascript', 5);

Nested SELECT Statements in SQLite Examples

There are different ways to form a nested SELECT statement in SQLite. This section covers practical examples of nested SELECT statements in SQLite.

Nested SELECT Statement in SQLite Using IN Operator Example

In this example, we are going to a form a nested SELECT statement with the help of IN operator.

This query displays all the details from table horje where a student has taken courses of duration 6 months.

Query:

SELECT *
FROM horje 
WHERE id In (SELECT courses.id FROM courses 
    WHERE courses.id = horje.id AND courses.duration = 6);

Output:

Nested SLECT statement output using In Operator

Explanation: There is only one course in courses table of duration ‘6’ months i.e. ‘Python’ taken by id’s 1001 and 1004. That is the same id’s displayed in the output block.

Nested SELECT Statement in SQLite Using Exists Operator

In this example, we are going to use exists operator. When using Exists operator, parent query will only run if the child query returns a True value.

This query will display all the records of ‘horje’ table only if there are courses in ‘courses’ table where duration is greater than 4 months.

Query:

SELECT *
FROM horje 
WHERE EXISTS ( SELECT 1 FROM courses 
   WHERE courses.id = horje.id AND courses.duration > 4 );

Output:

Nested SELECT statement output using Exists Operator

Explanation: In the above image, we can clearly notice all the records from ‘horje’ table get displayed. Since all the records of courses table has course duration greater than 4 months.

Conclusion

A nested SELECT statement consists of two parts i.e. a parent query and child query. A parent query result generally depends on the result of child query or subquery. Using nested SELECT queries allows users to perform more complex data retrieval operations in SQLite.

This article covered all the basic concepts related to the topic with clear and concise examples along with there explanations.