Subqueries in sql

Feb 4, 2022 - 20:48

0 49

What are subqueries?

A subquery is a select query that is contained within another query. An inner select query is typically used to determine the results of an outer select query.

Let's look at the subquery syntax −

A common complaint from customers in the MyFlix Video Library is the small number of movie titles. Management wants to buy films for the category that has the fewest titles.

You can use query like

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

This gives the result

Let's see how this query works

Above is the row subquery form . In such subqueries, the inner query can only return ONE result. Valid operators for row subqueries: [=,>, =, <=,,! =,]

Let's look at another example,

Let's say you want the names and phone numbers of people who have rented a movie and haven't returned it yet. As soon as you get names and phone numbers, you call them to remind them. You can use 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 result. Above is the type of T -capable subquery y.

So far we have seen two requests, let's now look at an example of a triple request !!!

Suppose management wants to reward the highest paid member.

We can 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 produces the following result −

Subqueries vs. joins!

When compared to Joins, subqueries are easy to use and easy to read. They are not as complex as Joins.

Hence, often used by SQL beginners.

But subqueries have performance issues. Using a join instead of a subquery can sometimes improve performance by up to 500x.

When given a choice, it is recommended to use a JOIN over a subquery.

Subqueries should only be used as a fallback solution if you cannot use the JOIN operation to achieve the above

Summary

Subqueries are inline queries within another query. An inline request is known as an inner request and a containerized request is known as an outer request.
Subqueries are easy to use, offer great flexibility, and can be easily broken down into the individual logical components that make up a query, which is very useful when testing and debugging queries.
MySQL supports three types of subqueries: scalar, row, and table.
Scalar subqueries return only one row and one column.
Row subqueries return only one row, but can have more than one column.
Table subqueries can return multiple rows and columns.
Subqueries can also be used in INSERT, UPDATE, and DELETE queries.
As for performance issues when it comes to getting data from multiple tables, it's highly recommended to use JOIN instead of subqueries. Subqueries should only be used for a good reason.