How SQL Subqueries Work

5
min read

A SQL subquery can look complicated:

CODE:https://gist.github.com/fronofro/e42364eef7cdf04be54a29945c4f67e7.js

The core concept to grasp is that the subquery (the part highlighted in blue inside the parentheses) is a separate SQL query that produces a table that is then used in the main query.

Let’s break the example above up and do the subquery first by itself.

Subquery:

CODE:https://gist.github.com/fronofro/6034baf5669f45354d1378bec1872462.js

SQL Subquery Animation

This subquery produces a table:

Result of SQL Subquery

This table from the subquery can then be used by the main query.

Main query:

CODE:https://gist.github.com/fronofro/d75d39815c6936e5270abc27e5426239.js

Main Query

Now while this was a somewhat pointless subquery example since you could have just run the subquery to get the same result, it illustrates the point the the outer query is pulling from the created table of the subquery. In this case it is selecting everything from the subquery.

Full SQL Subquery Animation

Subquery placement

Subqueries are most commonly in the FROM statement as a table to query from. However a subquery can also be placed in the WHERE or HAVING statement. When using it in the WHERE or HAVING statement, the subquery has to produce a single value table if you are using comparison operators such as =, <, <=, >, >=, !=.

Single value subquery table:

CODE:https://gist.github.com/fronofro/3eb8b3a8224e221c1388a866ff12f1de.js

Multi-value subquery tables will result in an error since it would try to compare a single field to every row in a column or every cell within table at once.

CODE:https://gist.github.com/fronofro/78819685ff3367eb9bb83b1936513514.js

 

Full SQL Subquery Animation in WHERE Statement

Multi-value subquery tables can be used with logical operators such as IN which compares a single field to every row in a column or table.

CODE:https://gist.github.com/fronofro/b7f106fbdfe8c024707336bb2ab1c46d.js

SQL Subquery with Logical Operators Animation

 

Common table expression

Subqueries can also be written in a WITH statement instead of in the main query. You write WITH and then what you want to name the resulting table from the subquery and then in parentheses write the subquery. Then you can write a query that uses that subquery by referring to the name given to it.  When written in this way they are referred to as a common table expression (CTE). Look at the original example and re-written version using a WITH statement below.

Subquery in the Query:

CODE:https://gist.github.com/fronofro/f3b69cc41332930cc4fcfc9027b52b0c.js

Subquery in a WITH statement:

CODE:https://gist.github.com/fronofro/4268879fb28a734a422d5c4dbaee999d.js

Writing it this way can help make it more clear that you are first creating a table and then referencing it in the outer query.

SQL Subquery Example with CTE

Give Feedback on our Google Doc

Chapter topics