When the need to use SQL subqueries arises, then you know you are bordering on the advanced uses of SQL. The best way to describe a subquery is to say that it is the use of a SELECT statement within another SQL statement. Subqueries in SQL are best used when there is data that needs to be accessed or manipulated based on criteria stored in a database with data that may be dynamic, partially or fully unknown.
An example would be that I could want to know the names of all the people that bought a certain prescription drug from a drug store during a certain period because we suspect that drug may have expired and is unfit for human consumption.
This kind of scenario is where you would be interested in using a subquery.
Structure of a Subquery
Though not always the case, SQL subqueries are generally used in SQL SELECT queries which have conditions specified under the WHERE clause or where JOINS are involved. These subqueries can also be used in other kinds of SQL statements including INSERT, UPDATE and DELETE. All these are best used when there is a WHERE clause as well.
The general form of an SQL statement with a subquery is as follows:
SELECT name, phone FROM customers WHERE (SELECT name FROM purchases WHERE substance = 'chemical X') AND customers.name = purchases.name;
In the example above, there is a subquery embedded within another SQL query. The subquery is evaluated before the outermost query. In this case the inner query retrieves a list of everyone who bought chemical X and then compares it with the existing customer database to retrieve their phone number so the drug store can call them and alert them of the dangers discovered in the drug they just purchased.
Even the simplest of SQL statements with a subquery embedded within it may look quite intimidating to the developer who is new to SQL. The best way to handle these kinds of SQL queries is to look at them as what they really are; separate queries. Once you have broken up the statement into two separate queries, the next step is to tackle the innermost query based on how they are nested.
It may be a simple and obvious case in the example above as to what is needed. You need to keep in mind though that subqueries may be further nested to third and fourth levels and that is where things really do get complicated.
A Deeper Look
So what does the subquery really do to the dynamics of the entire SQL statement? To answer that we can break up the whole statement and examine it deeper. The subquery in this case is just a simple straight SQL SELECT query which returns a list of names alleging they bought this Chemical X substance according to the list of customers who made purchases. The outer query goes into the customer database to retrieve a list of names of phone numbers of clients.
The problem here is that we do not want to extract the entire list of customers. We are only interested in the ones that bought Chemical X. The good news is that the subquery did a great job of telling us who bought this medicine. Unfortunately the purchases list does not keep contact information and therefore the subquery was not sufficient for this transaction. So we have two queries each with specific information.
The WHERE Clause Role
Now that the two queries each have vital information, we are still at a deadlock because we still cannot start making the emergency calls until we match up the buyer name and the name from the existing customer list to get the phone number. The WHERE clause is actually very good at this. The WHERE clause already spawned a subquery to get part of the information it needs to do the matching. It then takes that information and uses the related information from the outer query to match the names from both tables. It then hands back the compiled list of customers who bought Chemical X to the outer query which becomes the result of the query and the subquery working hand in hand.
- MySQL Docs, https://dev.mysql.com/doc/
- Image Credit: Wikimedia Commons/Jamesssss