Using subqueries in SQL is a trick that can be used to make a query dynamic or greatly decrease the execution time of a query. In this post, I’ll show you two tricks I use often to make my queries more efficient.
Decrease Query Execution Time
Sometimes a query can take a really long time to execute because the joining table is really big. The execution time can be compounded when both tables being joined are huge or they are not indexed properly.
A subquery is simply the output of a query that you use as part of another query. Often times, you only need a portion of the data from a table for your query. You may also want to join the results of a query to a table in your database. By creating a subquery, one can isolate data before joining it into another table. This will result in a much smaller data set which ultimately decreases the execution time of a query.
A subquery is nested within the main query. Here’s an example of joining to a subquery:
SELECT * FROM table1 INNER JOIN (SELECT * FROM table2 where account_name = 'abc_company') as T1
Instead of joining the entire table2 to table1, in my example, I can just join the part of the table where account_name = 'abc_company'. This means that I am joining fewer rows. Lastly, giving the subquery a table alias means that you can reference those columns elsewhere.
Make Queries Dynamic
The other way you can leverage subqueries is to reference the results of a subquery instead of a static value in the WHERE statement. I run monthly reports where the list of accounts changes month to month. Instead of having my WHERE statement include a static list, I can replace the static list with a subquery. Here is how that would look:
#Query 1 SELECT * FROM table1 WHERE account_name in (1,2,3,4,5,6,7,8,9,10) #Query 2 SELECT * FROM table1 WHERE account_name in (Select account_id from accounts where account_name like "abc%")
In Query 2, I am looking for any account that starts with abc.
One thing to note is that you have to return only one column from the subquery. Using * or having multiple columns returned in a subquery will result in an execution error.
There are many ways you can leverage subqueries and I would love to hear how you use them. Comment below!