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 characterList
and table-2 as movieDetails
SELECT
name,id
FROM
characterList
WHERE
id IN (
SELECT
id
FROM
movieDetails
WHERE
boxOfficeStatus = 'Hit'
);
I used IN
it 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 BY
in the subquery but can use at the main query - can use
sum
ormin
function inside thesubquery
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)andNOT 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