Description
A subquery is a nested query where the result of one query can be used in another query via relational operator or Aggregation Functions. And a subquery must be enclosed with in parentheses and contain only one column in the
Select clause if used in where clause.
An Order By Clause is not allowed in a subquery. And this can be nested within other subqueries. Subquries are used in Where, Having, from and in Select clauses. Syntax
The syntax for MySQL SubQueries is as follows:
Select <column_name1>,<column_name2> from <table_name> where <column_name2> <condition> (select <column_name2> from <table_name> where condition);
table name => Any accurate table in the database.
column name =>The column names that are inserted in the table.
condition => Is a logic.
Examples
By viewing the below example, the concept of MySQL Subqueries can be easily understood.
[c]
mysql> select * from employee;
+--------+-------+-------+--------+-----------+
| emp_id | ename |salary | deptno | job |
+--------+-------+-------+--------+-----------+
| 1001 | mike | 12000 | 10 | manager |
| 1002 | rambo | 13000 | 20 | scalesman |
| 1003 | kate | 14000 | 10 | manager |
| 1003 | jeo | 14000 | 20 | manager |
| 1003 | finn | 14000 | 30 | manager |
+--------+-------+-------+--------+-----------+
5 rows in set (0.00 sec)
mysql> select emp_id,ename,sal from employee where salary >(select salary from employee where ename='rambo');
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1003 | kate | 14000 |
| 1003 | jeo | 14000 |
| 1003 | finn | 14000 |
+--------+-------+-------+
3 rows in set (0.08 sec)
mysql> select min(salary) from employee where salary=(select salary from employee where ename='rambo');
+--------+-------+-------+--------+-----------+
| emp_id | ename |salary | deptno | job |
+--------+-------+-------+--------+-----------+
| 1002 | rambo | 13000 | 20 | salesman |
+--------+-------+-------+--------+-----------+
1 rows in set (0.08 sec)[/c]
Here in the above example, a query within another query is used, such as empid, ename and salary are selected from employee table and another select statement is performed on salary where ename is equal to rambo with some condition. So it executes employee salary greater than rambo salary.And in another example it will display the salary of employee by name rambo.
Key Points
- MySQL Subqueries - A query within another query is said to be subquery.