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 orderby clause is not allowed in a subquery. And this can be nested within other subqueries. Subquery are used in where, having, from and in select clauses. Subqueries are a substitute method for returning information from various tables. These can be utilized with the accompanying SQL proclamations alongside the comparison administrators like =, , >=, <= and so on.These are used in creation of
SELECT INSERT UPDATE DELETE "Syntax"
The syntax for SubQuery 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 Subquery can be easily understood.
[c]
sql> select * from employee21;
+--------+-------+-------+--------+-----------+
| emp_id | ename | sal | 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)
sql> select emp_id,ename,sal from employee21 where sal >(select sal from employee21 where ename='rambo');
+--------+-------+-------+
| emp_id | ename | sal |
+--------+-------+-------+
| 1003 | kate | 14000 |
| 1003 | jeo | 14000 |
| 1003 | finn | 14000 |
+--------+-------+-------+
3 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.
"Key
- Subquery - A query within another query is said to be subquery.