{"id":1172,"date":"2016-06-01T23:08:41","date_gmt":"2016-06-01T16:08:41","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1172"},"modified":"2022-04-03T15:02:51","modified_gmt":"2022-04-03T08:02:51","slug":"sqlite-subquery","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-subquery\/","title":{"rendered":"SQLite Subquery"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn about the SQLite subquery to construct more readable and complex queries.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQLite subquery<\/h2>\r\n\r\n\r\n\r\n

A subquery is a SELECT<\/code> statement nested in another statement. See the following statement.<\/p>\r\n\r\n\r\n

SELECT<\/span> column_1\r\nFROM<\/span> table_1\r\nWHERE<\/span> column_1 = (\r\n   SELECT<\/span> column_1 \r\n   FROM<\/span> table_2\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following query is the outer query<\/strong>:<\/p>\r\n\r\n\r\n
SELECT<\/span> column_1\r\n  FROM<\/span> table_1\r\n WHERE<\/span> colum_1 =<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
And the following query is the subquery<\/strong>.<\/p>\r\n\r\n\r\n
(SELECT<\/span> column_1\r\n  FROM<\/span> table_2)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
You must use a pair of parentheses to enclose a subquery. Note that you can nest a subquery inside another subquery with a certain depth.<\/p>\r\n\r\n\r\n\r\n
Typically, a subquery returns a single row as an atomic value, though it may return multiple rows for comparing values with the IN<\/a><\/code> operator.<\/p>\r\n\r\n\r\n\r\n
You can use a subquery in the SELECT<\/a><\/code>, FROM<\/code>, WHERE<\/a><\/code>, and JOIN<\/code> clauses.<\/p>\r\n\r\n\r\n\r\n
SQLite subquery examples<\/h2>\r\n\r\n\r\n\r\n
We will use the tracks<\/code> and albums<\/code> tables from the sample database<\/a> for the demonstration.<\/p>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
1) SQLite subquery in the WHERE clause example<\/h3>\r\n\r\n\r\n\r\n
You can use a simple subquery as a search condition. For example, the following statement returns all the tracks in the album with the title  Let There Be Rock<\/code><\/p>\r\n\r\n\r\n
SELECT<\/span> trackid,\r\n       name<\/span>,\r\n       albumid\r\nFROM<\/span> tracks\r\nWHERE<\/span> albumid = (\r\n   SELECT<\/span> albumid\r\n   FROM<\/span> albums\r\n   WHERE<\/span> title = 'Let There Be Rock'<\/span>\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
The subquery returns the id of the album with the title 'Let There Be Rock'<\/code>. The query uses the equal operator (=) to compare albumid<\/code> returned by the subquery with the  albumid<\/code> in the tracks<\/code> table.<\/p>\r\n\r\n\r\n\r\n
If the subquery returns multiple values, you can use the IN<\/a><\/code> operator to check for the existence of a single value against a set of value.<\/p>\r\n\r\n\r\n\r\n
See the following employees<\/code> and customers<\/code> table in the sample database:<\/p>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
For example, the following query returns the customers whose sales representatives are in Canada.<\/p>\r\n\r\n\r\n
SELECT<\/span> customerid,\r\n       firstname,\r\n       lastname\r\n  FROM<\/span> customers\r\n WHERE<\/span> supportrepid IN<\/span> (\r\n           SELECT<\/span> employeeid\r\n             FROM<\/span> employees\r\n            WHERE<\/span> country = 'Canada'<\/span>\r\n       );\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
The subquery returns a list of ids of the employees who locate in Canada. The outer query uses the IN<\/a><\/code> operator to find the customers who have the sales representative id in the list.<\/p>\r\n\r\n\r\n\r\n
2) SQLite subquery in the FROM clause example<\/h3>\r\n\r\n\r\n\r\n
Sometimes you want to apply aggregate functions<\/a> to a column multiple times. For example, first, you want to sum the size of an album and then calculate the average size of all albums. You may come up with the following query.<\/span><\/p>\r\n\r\n\r\n
SELECT<\/span> AVG<\/span>(SUM<\/span>(bytes<\/span>) \r\nFROM<\/span> tracks\r\nGROUP<\/span> BY<\/span> albumid;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
This query is not valid.<\/p>\r\n\r\n\r\n\r\n
To fix it, you can use a subquery in the FROM<\/code> clause as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tAVG<\/span>(album.size)\r\nFROM<\/span>\r\n\t(\r\n\t\tSELECT<\/span>\r\n\t\t\tSUM<\/span>(bytes<\/span>) SIZE<\/span>\r\n\t\tFROM<\/span>\r\n\t\t\ttracks\r\n\t\tGROUP<\/span> BY<\/span>\r\n\t\t\talbumid\r\n\t) AS<\/span> album;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
AVG(album.size)\r\n---------------\r\n  338288920.317<\/code><\/pre>\r\n\r\n\r\n\r\n
In this case, SQLite first executes the subquery in the FROM<\/code> clause and returns a result set. Then, SQLite uses this result set as a derived table in the outer query.<\/p>\r\n\r\n\r\n\r\n
SQLite correlated subquery<\/h2>\r\n\r\n\r\n\r\n
All the subqueries you have seen so far can be executed independently. In other words, it does not depend on the outer query.<\/p>\r\n\r\n\r\n\r\n
The correlated subquery is a subquery that uses the values from the outer query. Unlike an ordinal subquery, a correlated subquery cannot be executed independently.<\/p>\r\n\r\n\r\n\r\n
The correlated subquery is not efficient because it is evaluated for each row processed by the outer query.<\/p>\r\n\r\n\r\n\r\n
The following query uses a correlated subquery to return the albums whose size is less than 10MB.<\/p>\r\n\r\n\r\n
SELECT<\/span> albumid,\r\n       title\r\n  FROM<\/span> albums\r\n WHERE<\/span> 10000000<\/span> > (\r\n                      SELECT<\/span> sum<\/span>(bytes<\/span>) \r\n                        FROM<\/span> tracks\r\n                       WHERE<\/span> tracks.AlbumId = albums.AlbumId\r\n                  )\r\n ORDER<\/span> BY<\/span> title;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
How the query works.<\/p>\r\n\r\n\r\n\r\n