SQL Sub-Query

Sub-Query

Types of Sub query:-In the basis of place of the subquery, there are three types

1. Nested Subquery:- The subquery appears in the WHERE clause of the SQL.
Example:- select * from emp where deptno in (select deptno from dept);
2. Inline View: The subquery appears in the FROM clause (in place of table name) of the SQL.
Example:- SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a,
          (SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b
          WHERE a.deptno = b.deptno AND a.sal > b.salavg;
3. Scalar Subquery: The subquery appears in the SELECT clause of the SQL.
Example:-
select a.*,(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno) avg_sal
from emp a
where a.sal>(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno);


In the basis of execution of subquery, there are two categories of subqueries

1. Simple Subquery: A simple subquery can be exceuted independently.
                    It is executed first then parent query executed on the basis of Subquery result.
Example:-select * from emp where deptno in (select deptno from dept);
2. Correlated Subquery: A correlated subquery can not be excuted independently.
                       In Correlated Subquery, parent query executed first then Correlated Subquery
                       executed on the basis of parent query record.                      
Example:-select a.* from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);

Multiple-Column Subquery:-
Syntax:-
SELECT ,,... FROM 
WHERE (,, ...) IN (SELECT ,,... FROM
WHERE condition);

Example:- Display the employee's details whose job and deptno matched with 'WARD'
          and  'WARD' details should not be displayed

Pair-wise Comparison:-
select * from emp where (job,deptno) in (select job,deptno from emp where ename='WARD')
and ENAME<>'WARD';

Non Pair-wise Comparison:-
select * from emp
where (job) in (select job from emp where ename='WARD')
and (deptno) in (select deptno from emp where ename='WARD')
and ENAME<>'WARD';

Sub query in FROM clause (Inline View or Logical Table):-
Example:-
SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a,
(SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b
WHERE a.deptno = b.deptno AND a.sal > b.salavg;


Question for Practice :-

Q1 :- What is the Sub-Query ?
Q2 :- What are types of Sub-Query ?
Q3 :- Why Sub-Query is required ?
Q4 :- What is the difference between Main Sub-Query and Co-related Sub-Query ?
Q5 :- Can we use Sub-Query in place of Column Name as well ?
Q6 :- What is the In-line View ?

SQL Join

Join

Joins:-We are using joins when we need data from two are more tables

Note:- By "exists" we can select columns of outer table only , same way we can select outer table columns by "in" as well but by "Join" we can select columns from all joined tables (Which is possible in join only). 

 Types of Joins:- There are below types joins in Oracle

1) Cross Join or Cartesian Join
2) Inner Join
3) Natural Join
4) Outer Join
             a) Left Outer Join
             b) Right Outer Join
             c) Full Outer Join
5) Self Join

Note:- There are 2 standard syntax for Join
           1) ANSI Standard
           2) Oracle Standard

1) ANSI Standard:- In ANSI standard Join type need to mentioned and joining condition supply with "on" clause.

2) Oracle Standard :-In Oracle Standard, Join type not mentioned and joining condition supply with "where" clause.

1) Cross Join or Cartesian Join:-This is the worst join where no join condition mentioned. In this join, Oracle joins every record of first table with all records of second table .

Note:-After this join record count will be (m X n) where m is record count of first table and n is the record count of second table.


Example:-
select a.*,b.* from emp a,dept b;--Oracle Standard
select a.*,b.* from emp a cross join dept b; --ANSI standard

2) Inner Join:-In this join result depend on join condition.

Note:-After this join record count will be (<= m )  where m is the record count of that table which have the highest records.


Example:- 
ANSI standard:-
select a.*,b.* from emp a  inner join dept b on a.deptno=b.deptno; 
                                OR
select a.*,b.* from emp a  join dept b on a.deptno=b.deptno;

Oracle standard:-
select a.*,b.* from emp a , dept b where  a.deptno=b.deptno;

3) Natural Join :- This join is the same as Inner Join but here no need to supply any joining condition. Oracle automatically decide joining condition on the basis of columns name in Tables.There should be at-least one column name should be same in both tables.

Note:- You can't select that column on which system joined both tables in select query and when you are selecting all columns by "*" system will display that column in first position. 

select * from emp a  natural join dept b ; --ANSI standard
select a.EMPNO,a.ENAME,b.DNAME from emp a  natural join dept b ;

Note:-You can not take join column in select list.

4) Outer Join:- This join is used when we need records from joining table that are not satisfying join condition with records that are satisfying matching condition.

a) Left Outer Join:- In Left Outer Join, All records from left table will be part of result and where not matching records of right table, NULL will be displayed.

select a.*,b.* from emp111 a  left outer join dept b on a.deptno=b.deptno;

ANSI standard:-
select a.*,b.* from emp111 a  left join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp111 a , dept b where a.deptno=b.deptno(+);--Oracle Standard

b) Right Outer Join:- In Right Outer Join, All records from right table will be part of result and where not matching records of left table, NULL will be displayed.

select a.*,b.* from emp a  right outer join dept b on a.deptno=b.deptno;

ANSI standard:-
select a.*,b.* from emp a  right join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno;--Oracle Standard

c) Full Outer Join:- In Full Outer Join, All records from both tables will be part of result and where not matching records of left/right table, NULL will be displayed.

select a.*,b.* from emp111 a  full outer join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a  full join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno
union
select a.*,b.* from emp a , dept b where a.deptno=b.deptno(+);--Oracle Standard

Practice Questions :-

Q1 :- What is Join ?
Q2 :- Why Join is necessary ?
Q3 :- What are types of Join ?
Q4 :- What is the difference between Inner Join and Outer Join ?
Q5 :- What types of Outer Joins available in Oracle ?