Subquery in sql-: Suppose we want ti write a query to find out who earns a salary more then Rames’s salary. To solve this problem we need two query first find out the Ramesh salary and second who earn more then Ramesh. we can solve this problem by combine two query placing one query inside the other query. This is called the subquery.
Subquery can be used the following purpose.
- To provide values for conditions in WHERE HAVING AND START with clause of SELECT statement.
- To define the set of rows to be inserted into the target table of an INSERT or CREATE table statement.
- To define set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement.
- To define a table to be opened on by containing query.
Limitation of SUBQUERY -there are some limitation of subquery these are the
- A subquery must be enclosed in parentheses.
- It must be appear on the right side of the comparison operator.
- It can’t contain an Order BY clause . We can have only one Order By clause for a select statement and if specified it must be the last clause in the main select statement .
- Two clauses of comparison operators are used in subquery Single row operator and multiple row operators.
Types of subquery .– There are three type of subquery
- Single Row subquery
- Multiple row subquery
- Multiple column subquery
Single Row subquery – In a single row subquery is one that returns one rows from the inner Select statement. This type of subquery uses a single row operator. The list of single row operator are Equal to(=),grater than(>),grater than or equal to(>=),less than or equal to (<+),less than(<),not equal(<>).
Multiple Row Subquery-: Subquery that return more than one row are called multiple row subquery. We use multiple row operator, instead of single row operator with a multiple row subquery. here are some multiple row operator are IN(equal to any member in the list), ANY(compare value to each value returned by the subquery), ALL( compare value to every value returned by the subquery).
Multiple Column Subquery-: If we want to compare two or more than two column we must write a compound where clause using logical operator.