-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
Subqueries Sub queries are used to get the results based on unknown values. Types of Sub queries:
Single row subquery Ex: Step 1: we need to find ALLEN sal Select sal from emp Step 2: Query: Subquery is highlighted in blue color. Always subquery should be used in parenthesis. Ex 2: Select * from emp Ex 3: Step 1: Find the highest sal. Step 2: In all the above examples, subquery is returning only one row ( one value ). 2) Multiple-row subquery Ex: It is something like Select * from emp But, the above query will fail. Note: There are three multiple-row operators
First I want to discuss about ALL operator. ALL Operator: Select * from emp When we run the subquery, we know it returns six values. It is something like Select * from emp Do you think KING who is having sal 5000 is displayed? Do you think FORD who is having sal 3000 is displayed? Do you think CLARK who is having sal 2450 is displayed? We get the following four rows in the output, as they are having sal greater than all the six
Note: In other words, we get the above four rows are they are having sal greater than maximum value of the subquery. ANY Operator Select * from emp When we run the subquery, we know it returns six values. It is something like Select * from emp Do you think KING who is having sal 5000 is displayed? Do you think CLARK who is having sal 2450 is displayed? Do you think WARD who is having sal 1250 is displayed?
Note: In other words, we get the above twelve rows are they are having sal greater than minimum value of the subquery. IN Operator In Operator will display the rows who are which are matching with the list of values provided. Ex: Select * from emp where job in ( ‘CLERK’, ‘MANAGER’); In the output, we get all the employees who are CLERK’s and MANAGER’s. Similarly, Select * from emp When we run the subquery, we know it returns six values.
It is something like Select * from emp We get the row who are having sal matching with any of the value of the subquery. Multiple-column subquery: When subquery returns more than one column, it is called multiple-column subquery. Ex: Select * from emp In the above example, as subquery is returning two columns, parent query should also compare both the columns. Pair-wise comparisons are done. The following are the values returned by the subquery.
Now, the parent query will return the rows when combination of values are matching. To be more clear, consider employee JONES
JONES will not be displayed in the output, as combination of values ( 2975, MANAGER )
When subquery is executed in-relation to parent query, it is called co-related subquery. |