MySQL SubQuery with Examples

 What are sub queries?

A sub query is a select query that is contained inside another query. The inner select query is usually used to determine the results of the outer select query.

 

 
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

SELECT category_name FROM categories WHERE category_id =( SELECT MIN(category_id) from movies);

 
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

SELECT full_names,contact_number FROM   members  WHERE  membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL );

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

Select full_names From members WHERE membership_number = (SELECT membership_number FROM payments WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

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.