MySQL Subqueries

A subquery is a SQL query within a greater query that is recursive, or a subquery is considered an internal query. In contrast, an outer query is termed as the query that includes the subquery. A MySQL subquery can be embedded in the queries, including SELECT, INSERT, UPDATE, or DELETE. Furthermore, within another subquery, a subquery may be nestled. The phrase subquery should be closed in brackets wherever it is used. We’ll teach you how and when to use MySQL subquery to compose complicated queries and describe the idea of the associated subquery. Open the command-line shell from your desktop and write your password to start using it. Press Enter and continue.

Subquery within Single Table Records:

Create a table named ‘animals’ in the database ‘data.’ Add the below following record of different animals with different properties as displayed. Fetch this record using the SELECT query as follows:

>> SELECT * FROM data.animals;

Example 01:

Let’s retrieve the limited records of this table using the subqueries. Using the below query, we know that subquery will be executed first, and its output will be used in the main query as input. A subquery is simply fetching the age where the animal price is 2500. The age of an animal whose price is 2500 is 4 in the table. The main query will select all the table records where the age is greater than 4, and the output is given below.

>> SELECT * FROM data.animals WHERE Age > ( SELECT Age FROM data.animals WHERE Price=’2500’ );

Example 02:

Let’s use the same table in different situations. In this example, we will be using some Function instead of WHERE clause in the subquery. We have been taking the average of all the prices given for animals. The average price will be 3189. The main query will select all the records of animals having a price of more than 3189. You will get the below output.

>> SELECT * FROM data.animals WHERE Price > ( SELECT AVG(Price) FROM data.animals);

Example 03:

Let’s use the IN clause in the main SELECT query. First of all, the subquery will fetch prices greater than 2500. After that, the main query will select all the records of table ‘animals’ where the price lies in the subquery result.

>> SELECT * FROM data.animals WHERE Price IN ( SELECT Price FROM data.animals WHERE Price > 2500 );

Example 04:

We have been using the subquery to fetch the name of the animal where the price is 7000. As that animal is a cow, that’s why the name ‘cow’ will be returned to the main query. In the main query, all the records will be retrieved from the table where the animal name is ‘cow.’ As we have only two records for animal ‘cow,’ that’s why we have the below output.

>> SELECT * FROM data.animals WHERE Name = ( SELECT Name FROM data.animals WHERE Price=’7000’ );

Subquery within Multiple Table Records:

Assume the below two tables, ‘ student’ and ‘teacher,’ in your database. Let’s try some examples of subqueries using these two tables.

>> SELECT * FROM data.student;
>> SELECT * FROM data.teacher;

Example 01:

We will fetch data from one table using the subquery and use it as an input for the main query. This means that these two tables can relate in some manner. In the below example, we have been using the subquery to fetch the student’s name from the table ‘student’ where the teacher name is ‘Samina.’ This query will return ‘Samina’ to the main query table ‘teacher.’ The main query will then select all the records related to the teacher name ‘Samina.’ As we have two records for this name, therefore we have got this result.

>> SELECT * FROM data.teacher WHERE TeachName = ( SELECT TeachName FROM data.student WHERE TeachName = ‘Samina’ );

Example 02:

To elaborate the subquery in the case of different tables, try this example. We have a subquery that is fetching the teacher’s name from the table student. The name should have ‘i’ at any position in its value. This means, all the names in the column TeachName having ‘i’ in their value will be selected and returned to the main query. The main query will select all the records from the ‘teacher’ table where the teacher name is in the output returned by the subquery. As subquery returned 4 names of teachers, that’s why we will be having a record of all these names residing in the table ‘teacher.’

>> SELECT * FROM data.teacher WHERE TeachName IN ( SELECT TeachName FROM data.student WHERE TeachName LIKE ‘%i%’ );

Example 03:

Consider the below two tables, ‘order’ and ‘order1’.

>> SELECT * FROM data.order;
>> SELECT * FROM data.order1;

Let’s try an ANY clause in this example to elaborate subquery. The subquery will select the ‘id’ from the table ‘order1’, where the column ‘Status’ has a value of ‘Unpaid.’ The ‘id’ can be more than 1. This means that more than 1 value would be returned to the main query to get the table ‘order’ results. In this case, any ‘id’ could be used. We have got the below output for this query.

>> SELECT Item, Sales, id FROM data.order WHERE id= ANY ( SELECT id FROM data.order1 WHERE Status= ’Unpaid’ );

Example 04:

Assume you have the below data in the table ‘order1’ before applying any query.

>> SELECT * FROM data.order1;

Let’s apply the query within a query to delete some records from the table ‘order1’. Firstly, the subquery will select the ‘Status’ value from the table ‘order’ where the Item is ‘Book.’ The subquery returns ‘Paid’ as the value. Now the main query will delete the rows from the table ‘order1’ where the ‘Status’ column value is ‘Paid.’

>> DELETE FROM data.order1 WHERE Status= ( SELECT Status FROM data.order WHERE Item = ’Book’ );

Upon checking, we have now the below records remained in the table ‘order1’ after the execution of the query.

>> SELECT * FROM data.order1;

Conclusion:

You have efficiently worked with a lot of subqueries in all the above examples. We hope everything is clear and clean now.

from Linux Hint https://ift.tt/37W17L7