What's the need of Subquery here?

0 Sathyamoorthy R · June 7, 2015
Why do we need to go for Subquery, While an Ordinary query can produce the Same Result?

mysql> SELECT  name,  MIN(cost)  FROM  items  WHERE  name  LIKE  '%boxes of frogs';

+----------------------+-------------------------------+
| name                    | MIN(cost)                        |
+----------------------+-------------------------------+
| 3 boxes of frogs | 30.489999771118164 |
+----------------------+-------------------------------+

1 row in set (0.09 sec)

Post a Reply

Replies

Oldest  Newest  Rating
+1 Dave . · June 8, 2015
If you are talking about Tutorial #21 the reason is the lesson is about Subquery and how to use them.

The lessons are not driven by what might be the most appropriate action to take but rather subject driven.

There are of course multiple ways to get to the results that we want.
0 Ron Butcher · June 26, 2015
A reason to use a sub query is to get information out of a related table.  This helps with data consistency especially when inputting data.

A properly built database has multiple tables to represent any possible data that may duplicate along with an ID for each item in the table.  Here is an example.  We have a table of famous painters, and a table of famous paintings.

mysql> SELECT * FROM `painters`;
+------------+-------------------+
| painter_id | painter_name      |
+------------+-------------------+
|          1 | Leonardo da Vinci |
|          2 | Edward Munch      |
|          3 | Vincent van Gogh  |
+------------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `paintings`;
+-------------+-----------------+------------+
| painting_id | painting_name   | painter_id |
+-------------+-----------------+------------+
|           1 | Mona Lisa       | 1          |
|           2 | The Scream      | 2          |
|           3 | The Stary Night | 3          |
|           4 | The Last Supper | 1          |
+-------------+-----------------+------------+
4 rows in set (0.00 sec)


Hopefully you can see how the data relates.  The ID of the painter, is placed into the paintings table.  Instead of typing in the painters name (and possibly misspelling it) we reference another table.
If we want to select all the paintings that Leonardo da Vinci did, we can use a subquery to get that data.

mysql> SELECT * FROM `paintings` WHERE `painter_id` =
(SELECT `painter_id` FROM `painters` WHERE `painter_name` = "Leonardo da Vinci");
+-------------+-----------------+------------+
| painting_id | painting_name   | painter_id |
+-------------+-----------------+------------+
|           1 | Mona Lisa       | 1          |
|           4 | The Last Supper | 1          |
+-------------+-----------------+------------+
2 rows in set (0.00 sec)

Hopefully that makes sense. 
  • 1

SQL & Databases

107,203 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator