Using SQLite

Advanced Techniques

Beyond the basic SELECT syntax, there are a few advanced techniques for expressing more complex queries.

Subqueries

The SELECT command provides a great deal of flexibility, but there are times when a single SELECT command cannot fully express a query. To help with these situations, SQL supports subqueries. A subquery is nothing more than a SELECT statement that is embedded in another SELECT statement. Subqueries are also known as sub-selects.

Subqueries are most commonly found in the FROM clause, where they act as a computed source table. This type of subquery can return any number of rows or columns, and is similar to creating a view or running the query, recording the results into a temporary table, and then referencing that table in the main query. The main advantage of using an in-line subquery is that the query optimizer is able to merge the subquery into the main SELECT statement and look at the whole problem, often leading to a more efficient query plan.

To use a subquery in the FROM clause, simply enclose it in parentheses. The following two statements will produce the same output:

SELECT * FROM TblA AS a JOIN TblB AS b;
SELECT * FROM TblA AS a JOIN (SELECT * FROM TblB) AS b;

Subqueries can show up in other places, including general expressions used in any SQL command. The EXISTS and IN operators both utilize subqueries. In fact, you can use a subquery any place an expression expects a list of literal values (a subquery cannot be used to generate a list of ...