SUBQUERY in Oracle Join,Union and More..

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;

JOIN

•        To manipulate data from Multiple tables, JOINS are used.

•        Tables are joined on columns having the same <data type> and <data width> in the tables.

•        A join compares data in specified columns and gets rows according to comparison results.

•        Types of Joins:

1.     Inner Join (EQUI JOIN)

2.     Outer Join (Left, Right, Full)

3.     Self Join

4.     Cross Join

INNER JOIN

•        This join is called “EQUI JOIN” because the WHERE part compares two columns from two tables with the “ = “ operator.

ENO	ENAME	DEPTNO

DEPTNO	DNAME

ENAME	DNAME

SQL> SELECT ename , dname FROM emp , dept WHERE emp.deptno = dept.deptno;

OUTER JOIN

•        Whenever we want to see the data from one Table ,even if there is no corresponding row in the joining table OUTER JOIN method is used.

•        The outer join operator (+) is used in query with the column of the table from which you want to retrieve only the matching rows.

LEFT OUTER JOIN:-

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