Let’s look into the sub query syntax –
A common customer complaint at the MyFlix Video Library is the low number of movie titles. The management wants to buy movies for a category which has least number of titles.
You can use a query like It gives a result
Let’s see how this query works
The above is a form of Row Sub-Query. In such sub-queries the , inner query can give only ONE result. The permissible operators when work with row subqueries are [=, >, =, <=, ,!=, ]
Let’s look at another example , Suppose you want Names and Phone numbers of members of people who have rented a movie and are yet to return them. Once you get Names and Phone Number you call them up to give a reminder. You can use a query like
Let’s see how this query works
In this case, the inner query returns more than one results. The above is type of Table sub-query.
Till now we have seen two queries , lets now see an example of triple query!!! Suppose the management wants to reward the highest paying member. We can run a query like The above query gives the following result –
Sub-Queries Vs Joins!
When compare with Joins , sub-queries are simple to use and easy to read. They are not as complicated as Joins Hence there are frequently used by SQL beginners. But sub-queries have performance issues. Using a join instead of a sub-query can at times give you upto 500 times performance boost. Given a choice, it is recommended to use a JOIN over a sub query. Sub-Queries should only be used as a fallback solution when you cannot use a JOIN operation to achieve the above
Summary
Subqueries are embedded queries inside another query. The embedded query is known as the inner query and the container query is known as the outer query.
Sub queries are easy to use, offer great flexibility and can be easily broken down into single logical components making up the query which is very useful when Testing and debugging the queries.
MySQL supports three types of subqueries, scalar, row and table subqueries.
Scalar sub queries only return a single row and single column.
Row sub queries only return a single row but can have more than one column.
Table subqueries can return multiple rows as well as columns.
Subqueries can also be used in INSERT, UPDATE and DELETE queries.
For performance issues, when it comes to getting data from multiple tables, it is strongly recommended to use JOINs instead of subqueries. Sub queries should only be used with good reason.
