Subqueries in SQL – Embedding SELECT in SELECT

Our SQL abilities tend to go from beginner to intermediate when we start to learn how to utilize subqueries. A sql subquery is a nested query where we have a SELECT within a SELECT statement.

We can also apply subqueries to several parts of a query. As a result, they can be used in the SELECT, FROM, or WHERE clause. Subqueries can also be applied to DML queries such as UPDATE, DELETE, and INSERT statements.

Example 1 Syntax: Filtering based on a list of values

SELECT * FROM Table WHERE Value IN (SELECT ValueList FROM InnerTable);

Example 2 Syntax: Filtering a table before joining to it

SELECT * FROM Table1 A
JOIN (SELECT * FROM Table2 WHERE Column1 = ‘FilterValue’) B
ON A.JoinColumn = B.JoinColumn; — Subquery in a FROM clause

Example 3 Syntax: Getting a count using sql with SELECT in a SELECT

SELECT ColumnCount = (SELECT COUNT(*) FROM Table2), * FROM Table1;

Practical Example of a Nested Subquery

The abstract syntax examples above may be hard to visualize in practice. Here is an actual example with results to show how we can use subqueries in the real world.

Adding A Count Grouping To A Row

The above query will return us our list of customers names along with a count of the total customers in each of the respective countries. We can use a similar approach to gain other useful metrics within a query.

There are many possibilities why one would want to build out a subquery. As a result, it’s hard to show all the possible use cases.

Final Thoughts

Subqueries start to test our SQL abilities and push us to utilize SQL in a more meaningful way. As we find more uses, we realize that many functions are used in replacement for simplicity (ex. LAG() and LEAD() functions). As a result, we find ourselves using more of these window functions.