Mastering in SQLite

Subquery || Nested query

Before getting into Subquery, if you haven’t covered the previous topic-Joining tables, then get into it.

To visit the introduction page to see the available topics click here

A number of superheroes are available in our sample DB, click here to download it.

What is subquery?

Query within another query and embedded within the WHERE clause

or

A subquery is used to run a separate query from the main query

The subquery will be inside (subquery) parentheses

Subquery Syntax

SELECT column_1 FROM table_1 WHERE column_1 = ( SELECT column_1     FROM table_2 );

Subquery VS join

• Join and subqueries are both used to combine different tables into a single result.
• the subquery can be used to return scalar (single) value or a row set but joins can return rows.
• subquery result can be used immediately (as per the query)

Subquery examples

mostly subquery used as a search condition

table-1 as characterListand table-2 as movieDetails

SELECT
     name,id
 FROM
     characterList
 WHERE
     id IN (
         SELECT
             id
         FROM
             movieDetails
         WHERE
             boxOfficeStatus = 'Hit'
     );

I used INit will return multiple rows, as the main query displays all the id returned by the subquery.

if used = , the main query will display only one value from the subquery

NOTE

• we can’t use any function like ORDER BYin the subquery but can use at the main query
• can use sumor minfunction inside the subquery like

SELECT
     name,id
 FROM
     characterList
 WHERE
     id IN (
         SELECT
             sum(salary)  // we can use here any function
         FROM
             movieDetails
         WHERE
             boxOfficeStatus = 'Hit' 
     );

• You can also use the alias method in the subquery

EXISTS

EXISTS is a logical operator, its check weather subquery returns any rows

If the subquery returns any row EXISTS will be true and returns all the selected rows in the main query

NOT EXISTS will return true when the subquery returns a zero(Null) rows and returns all the selected rows in the main query

this is the main use of EXISTS but here I explain with another use of it

Syntax

EXISTS(subquery)
and
NOT EXISTS(subquery)

example

SELECT
     name,id
 FROM
     characterList a 
 WHERE
    EXISTS(
         SELECT
             id
         FROM
             movieDetails
         WHERE
            a.id = id
     )

Here EXISTS will act like WHERE id IN (subquery) and displayed a similar id between characterList and movieDetails

Example of NOT EXISTS

SELECT
     name,
     id
 FROM
     characterList a
 WHERE
     NOT EXISTS(
         SELECT
             id
         FROM
             movieDetails
         WHERE
             a.id = id
     )

Here displays dissimilar id between movieDetails and characterList

Reach out the next topic- SQLITE FUNCTIONS