Explanation Of SUBQUERY in Oracle

A sub query is a query within a query i.e. A SELECT statement is used as part of another SQL statement. The outer statement is called the parent and the nested query passes a value to the outer query. The nested query executes first.

Important Notes About Subqueries

·       The nested query must return a single column.

·       The result can only contain columns from the tables referenced in the outermost query.

·       The nested query must return a single row when a standard operator such as =, <, > is used.

·       The BETWEEEN operator cannot be used with a Subquery.

·       Subqueries can also be used in INSERT, UPDATE and DELETE statements.

In order to list the employees who earn LESS THAN the average salary in the organization, a group function AVG must be used to calculate the average salary. However, the group function cannot be used in the WHERE clause. In such a case a sub-query may be used.

SELECT * FROM SALARY WHERE BASIC < (SELECT AVG (BASIC) FROM SALARY;

SQL > SELECT d.deptno, d.dname, e.empno , e.ename  FROM dept d, emp e WHERE d.deptno = e.deptno(+);

RIGHT OUTER JOIN:-

•        All rows from right side table will be displayed, whereas only matching rows will be displayed from left side.

SQL > SELECT d.deptno, d.dname, e.empno , e.ename  FROM dept d, emp e WHERE d.deptno(+) = e.deptno;

FULL OUTER JOIN:-

•        To get this type of join, use left outer join SQL with UNION it with right outer join SQL.

•        All rows from both as well as all matching rows will be display

SQL > SELECT d.deptno, d.dname, e.empno , e.ename FROM dept d, emp e       WHERE d.deptno = e.deptno(+) UNION SELECT d.deptno, d.dname, e.empno , e.ename FROM dept d, emp e    WHERE d.deptno(+) = e.deptno ;

CROSS JOIN:-

•        In this type the join combines every row in the right table.

•        This type of join can be used in situations where it is desired to select all possible combination of rows and columns from both tables.

SQL>select e.empno,e.ename,d.deptno from emp e ,dept d;