Crack SQL Interview Question: Subquery

Solving SQL questions with useful procedures

Photo by Jay Wennington on Unsplash

In this article, we will go over a SQL question from a Yelp data science interview. This exercise involves using a subquery in a query. Hope the information explained in this article would help you become more effective in writing SQL queries.

Subquery

A subquery is a query within a query. It is a versatile tool to create a complex query to implement data analysis. A subquery can be used in different clauses in a query.

• A subquery (shown as a table) can be found in a FROM clause. In this case, a subquery serves as a separate table.

SELECT column_1
FROM 
   (SELECT column_1
    FROM table)

• A subquery (shown as a single value) can be found in a SELECT clause or a WHERE clause. In these cases, a subquery is usually created by an aggregate function.

SELECT column_1, 
   (SELECT AGGREGATE_FUNCTION(column_name) FROM table1)
FROM table2SELECT column_1
FROM table2 
WHERE column_2 > (SELECT AGGREGATE_FUNCTION(column_name) FROM table1)

• A subquery (shown as a list of values) can be found in an IN, ANY, or ALL operator.

SELECT column_1
FROM table2 
WHERE column_2 IN (SELECT column_name FROM table1)