MySQL Tutorials for Business Analyst: How to use Sub-Queries in MySQL

Hits: 19

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

MySQL SubQuery Tutorial with Examples

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 );

MySQL SubQuery Tutorial with Examples

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.

 

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

 

PostgreSQL tutorial for Beginners – PostgreSQL – Sub Queries

Beginners Guide to SQL – SQL Subqueries

MySQL Tutorials for Business Analyst: MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN