CROSS JOIN
This will gives the cross product.
Ex:
SQL> select empno,ename,job,dname,loc
from emp cross join dept;
EMPNO
ENAME JOB DNAME LOC
---------- ---------- ----------
---------- ----------
111 saketh analyst
mkt hyd
222 sudha clerk mkt hyd
333 jagan manager
mkt hyd
444 madhu engineer
mkt hyd
111 saketh analyst
fin bang
222 sudha clerk fin bang
333 jagan manager
fin bang
444 madhu engineer
fin bang
111 saketh analyst
hr bombay
222 sudha clerk hr bombay
333 jagan manager
hr bombay
444 madhu engineer
hr bombay
OUTER JOIN
Outer join gives the non-matching records along with
matching records.
LEFT OUTER JOIN
This will display the all matching records and the records
which are in left hand side table those that are not in right hand side table.
Ex:
SQL> select empno,ename,job,dname,loc from emp e left outer join dept
d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc
from emp e,dept d where
e.deptno=d.deptno(+);
EMPNO
ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan
manager mkt hyd
222 sudha clerk fin bang
444 madhu engineer
RIGHT OUTER JOIN
This will display the all matching records and the records
which are in right hand side table those that are not in left hand side table.
Ex:
SQL> select empno,ename,job,dname,loc from emp e right outer join
dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc
from emp e,dept d where e.deptno(+) =
d.deptno;
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh
analyst mkt hyd
333 jagan
manager mkt hyd
222 sudha clerk fin bang
hr bombay
FULL OUTER JOIN
This will display the all matching records and the
non-matching records from both tables.
Ex:
SQL> select empno,ename,job,dname,loc from emp e full outer join
dept d
on(e.deptno=d.deptno);
EMPNO ENAME
JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
333 jagan
manager mkt hyd
111
saketh analyst mkt hyd
222 sudha clerk fin bang
444 madhu
engineer
hr bombay
INNER JOIN
This will display all the records that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept
using(deptno);
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt
hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
SUBQUERIES AND EXISTS
SUBQUERIES
1
Nesting of queries, one within the other is termed as a subquery.
2
A statement containing a subquery is called a parent query.
3
Subqueries are used to retrieve data from tables that depend on the
values in the table itself.
TYPES
1 Single row subqueries
2 Multi row subqueries
3 Multiple subqueries
4 Correlated subqueries
SINGLE
ROW SUBQUERIES
In single row subquery, it will return one value.
Ex:
SQL> select * from emp where sal > (select sal from emp where
empno = 7566);
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM DEPTNO
----------
---------- --------- ---------- ------------
------- ---------- ----------
7788 SCOTT
ANALYST 7566 19-APR-87
3000 20
7839 KING
PRESIDENT
17-NOV-81 5000 10
7902 FORD ANALYST
7566 03-DEC-81 3000 20
MULTI
ROW SUBQUERIES
In multi row subquery, it will return more than one value.
In such cases we should include operators like any, all, in or not in between
the comparision operator and the subquery.
Ex:
SQL> select * from emp where sal > any (select sal from emp where
sal between 2500
and 4000);
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM DEPTNO
----------
---------- --------- ---------- -----------
-------- ---------- ----------
7566 JONES
MANAGER 7839 02-APR-81 2975 20
7788 SCOTT
ANALYST 7566 19-APR-87 3000 20
7839 KING
PRESIDENT 17-NOV-81 5000 10
7902 FORD
ANALYST 7566 03-DEC-81 3000 20
SQL> select * from emp where sal >
all (select sal from emp where sal between 2500
and 4000);
EMPNO ENAME JOB
MGR HIREDATE SAL
COMM DEPTNO
----------
---------- --------- ---------- -------------
------ ---------- ----------
7839 KING
PRESIDENT
17-NOV-81 5000 10
MULTIPLE
SUBQUERIES
There is no limit on the number of subqueries included in a
where clause. It allows nesting of a query within a subquery.
Ex:
SQL> select * from emp where sal = (select max(sal) from emp where
sal < (select
max(sal) from emp));
EMPNO ENAME
JOB MGR HIREDATE SAL
COMM DEPTNO
----------
---------- --------- ---------- ------------
------- ---------- ----------
7788 SCOTT
ANALYST 7566 19-APR-87
3000 20
7902 FORD
ANALYST 7566 03-DEC-81
3000 20
CORRELATED
SUBQUERIES
A subquery is evaluated once for the entire parent
statement where as a correlated subquery is evaluated once for every row
processed by the parent statement.
Ex:
SQL> select distinct deptno from emp e where 5 <= (select
count(ename) from emp
where e.deptno = deptno);
DEPTNO
----------
20
30
EXISTS
Exists
function is a test for existence. This is a logical test for the return of rows
from a query.
Ex:
Suppose we want to display the
department numbers which has more than 4
employees.
SQL> select deptno,count(*) from emp group by deptno having count(*)
> 4;
DEPTNO COUNT(*)
--------- ----------
20 5
30 6
From the above
query can you want to display the names of employees?
SQL> select deptno,ename, count(*) from
emp group by deptno,ename having count(*)
> 4;
no rows selected
The above query
returns nothing because combination of deptno and ename never
return more than
one count.
The solution is
to use exists which follows.
SQL> select deptno,ename from emp e1
where exists (select * from emp e2
where e1.deptno=e2.deptno group by
e2.deptno having count(e2.ename) > 4)
order by deptno,ename;
DEPTNO ENAME
---------- ----------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
NOT
EXISTS
SQL> select deptno,ename from emp e1
where not exists (select * from emp e2
where e1.deptno=e2.deptno group by e2.deptno
having count(e2.ename) > 4) order
by deptno,ename;
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
WALKUP TREES AND INLINE VIEW
WALKUP
TREES
Using hierarchical queries, you can retrieve data based on
a natural hierarchical relationship between rows in a table. However, where a
hierarchical relationship exists between the rows of a table, a process called
tree walking enables the hierarchy to be constructed.
Ex:
SQL> select ename || '==>' ||
prior ename, level from emp start with ename = 'KING'
connect by prior empno=mgr;
ENAME||'==>'||PRIORENAM LEVEL
------------------------------------ --------
KING==> 1
JONES==>KING 2
SCOTT==>JONES 3
ADAMS==>SCOTT 4
FORD==>JONES 3
SMITH==>FORD 4
BLAKE==>KING 2
ALLEN==>BLAKE 3
WARD==>BLAKE 3
MARTIN==>BLAKE 3
TURNER==>BLAKE 3
JAMES==>BLAKE 3
CLARK==>KING 2
MILLER==>CLARK 3
In
the above
Start
with clause specifies the root row of the table.
Level
pseudo column gives the 1 for root , 2 for child and so on.
Connect
by prior clause specifies the columns which has parent-child relationship.
INLINE
VIEW OR TOP-N ANALYSIS
In
the select statement instead of table name, replacing the select statement is
known as inline view.
Ex:
SQL> Select ename, sal, rownum rank from
(select *from emp order by sal);
ENAME SAL RANK
---------- ---------- ----------
SMITH 800 1
JAMES 950 2
ADAMS 1100 3
WARD 1250 4
MARTIN 1250 5
MILLER 1300 6
TURNER 1500 7
ALLEN 1600 8
CLARK 2450 9
BLAKE 2850 10
JONES 2975 11
SCOTT 3000 12
FORD 3000 13
KING 5000 14
No comments:
Post a Comment