What is SubQuery?

The SubQuery is the method that help to execute multiple query in single query. It is inner Query between outer Query with the help of "() round brakets" those return value to outer Query.

Single Row Single Column SubQuery

It inner_query return single column and single row to outer query column. It deal to use "=" operator.

Syntax
outer_query WHERE column = (inner_query);
Example 
mysql> SELECT max(total) FROM mark;
+------------+
| max(total) |
+------------+
|     283.00 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT id FROM mark WHERE total = 283.00;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT id FROM mark WHERE total = (SELECT max(total) FROM mark);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT fname FROM student WHERE id = 1;
+-------+
| fname |
+-------+
| aman  |
+-------+
1 row in set (0.00 sec)

mysql> SELECT fname FROM student WHERE id = (SELECT id FROM mark WHERE total = (SELECT max(total) FROM mark));
+-------+
| fname |
+-------+
| aman  |
+-------+
1 row in set (0.00 sec)

mysql>
Multiple Row Single Column SubQuery

It inner_query return single column and multiple row to outer query column. It deal to use " in() " function.

Syntax
outer_query WHERE column IN (inner_query);
Example 
mysql> SELECT fname FROM student WHERE id = (SELECT id FROM mark WHERE total > 200);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT fname FROM student WHERE id IN (SELECT id FROM mark WHERE total > 200);
+-------+
| fname |
+-------+
| aman  |
| geeta |
+-------+
2 rows in set (0.00 sec)

mysql>
Single Row Multiple Column SubQuery

It inner_query return multiple column and single row to outer query column. It deal to use " in() , any() ,all() " function.

Syntax
outer_query WHERE column [ IN | ANY | ALL ] (inner_query);
Example 
mysql> SELECT id FROM mark WHERE (m1,m2) IN (SELECT m1,m2 FROM mark WHERE total = (SELECT max(total) FROM mark));
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>
Multiple Row Multiple Column SubQuery

It inner_query return multiple column and multiple row to outer query column. It deal to use " in() , any() ,all() " function.

Syntax
outer_query WHERE column [ IN | ANY | ALL ] (inner_query);
Example 
mysql> SELECT id FROM mark WHERE (m1,m2) IN (SELECT m1,m2 FROM mark WHERE total < 200);
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
5 rows in set (0.00 sec)

mysql>