MySQL SubQuery Tutorial with Examples

Details

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 -

MySQL SubQuery Tutorial with Examples

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

MySQL SubQuery Tutorial with Examples

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 );
YOU MIGHT LIKE: