Oracle Joins and Queries

Here we are going through different types of Joins, special queries used in Oracle.
 

Types of JOIN:

Implicit JOIN:
SELECT * FROM A, B WHERE A.column = B.column(+)

• Implicit JOINs simply list all tables together.
• The join conditions are specified in a WHERE clause.

Explicit JOIN:

SELECT * FROM A LEFT OUTER JOIN B ON A.column = B.column
Explicit JOINs associate join conditions with a specific table's inclusion instead of in a WHERE clause.

Natural Join:

select * from emp natural join dept

Equi Join:

select * from emp , dept where dept.deptno = emp.deptno

Self Join:

Select a.empno, a.ename, b.empno,b.ename
from emp a , emp b
where a.empno = b.mgr

Cartesian - Cross Join:

select * from emp, dept

LEFT OUTER JOIN:

SELECT *  FROM A, B WHERE A.column = B.column(+)

RIGHT OUTER JOIN:

SELECT * FROM A, B WHERE B.column(+) = A.column

The placement of the (+) determines RIGHT or LEFT. (Specifically, if the (+) is on the right, it's a LEFT JOIN. If (+) is on the left, it's a RIGHT JOIN.

Group By:

select deptno, sum(sal) from emp group by deptno

select dept.deptno, sum(sal) from emp , dept where emp.deptno(+) = dept.deptno
group by dept.deptno

Using clause:

SELECT emp.empno, ename, dept.loc, deptno FROM   emp JOIN dept USING (deptno) ;

ON Clause:

SELECT e.empno, e.ename, e.deptno,
       d.deptno, d.loc
FROM   emp e JOIN dept d
ON     (e.deptno = d.deptno);

Connect BY.. Tree Structure:

SELECT a.empno, a.ename, a.mgr
FROM emp a
start with a.ename = 'KING'
CONNECT BY PRIOR a.empno = a.mgr

select lpad(' ',2*level)||ename organization_chart
from emp
connect by prior empno = mgr
start with ename = 'KING';


Sub Query:select * from emp where deptno in (select deptno from dept)
select * from emp where deptno = (select deptno from dept where dname = 'SALES')

Correlated:

SELECT ename, sal, deptno
FROM   emp outer
WHERE  sal > (SELECT AVG(sal)
          FROM   emp
          WHERE  deptno =  
          outer.deptno)

Delete duplicate records:

delete from tests where rowid not in (
select max(rowid) from tests
group by name
)

Create Index:
create index emp_no on emp(deptno)

--If there are any locks on the db.

Select session_id "sid",SERIAL#  "Serial",

substr(object_name,1,20) "Object",

  substr(os_user_name,1,10) "Terminal",

  substr(oracle_username,1,10) "Locker",

  nvl(lockwait,'active') "Wait",

  decode(locked_mode,

    2, 'row share',

    3, 'row exclusive',

    4, 'share',

    5, 'share row exclusive',

    6, 'exclusive',  'unknown') "Lockmode",

  OBJECT_TYPE "Type"

FROM

  SYS.V_$LOCKED_OBJECT A,

  SYS.ALL_OBJECTS B,

  SYS.V_$SESSION c

WHERE

  A.OBJECT_ID = B.OBJECT_ID AND

  C.SID = A.SESSION_ID

ORDER BY 1 ASC, 5 Desc

--Session count

Select SESSIONS_CURRENT "current sessions", SESSIONS_HIGHWATER "highwater sessions" from v$license;

--Maximum number of process

Select SESSIONS_CURRENT "current sessions", SESSIONS_HIGHWATER "highwater sessions" from v$license;

--Session Info

      character  Select s.username||'@||

        s.command||'@'||

        s.lockwait||'@'||

        s.status ||'@'||

        s.schemaname||'@'||

        s.osuser||'@||

        s.process||'@'||

        s.machine||'@'||

        s.terminal||'@'||

        s.program||'@'||

        s.type||'@'||

        s.row_wait_row||'@'||

        s.logon_time||'@||

        s.last_call_et||'@'||

        s.blocking_session_status||'@'||

        s.blocking_instance||'@'||

        s.blocking_session||'@'||

        s.wait_time||'@'||

        s.seconds_in_wait||'@'||

        s.state||'@'||

        s.service_name from v$session s

--If there are any locks on the db.

Select session_id "sid",SERIAL#  "Serial",

substr(object_name,1,20) "Object",

  substr(os_user_name,1,10) "Terminal",

  substr(oracle_username,1,10) "Locker",

  nvl(lockwait,'active') "Wait",

  decode(locked_mode,

    2, 'row share',

    3, 'row exclusive',

    4, 'share',

    5, 'share row exclusive',

    6, 'exclusive',  'unknown') "Lockmode",

  OBJECT_TYPE "Type"

FROM

  SYS.V_$LOCKED_OBJECT A,

  SYS.ALL_OBJECTS B,

  SYS.V_$SESSION c

WHERE

  A.OBJECT_ID = B.OBJECT_ID AND

  C.SID = A.SESSION_ID

ORDER BY 1 ASC, 5 Desc

--Session count

Select SESSIONS_CURRENT "current sessions", SESSIONS_HIGHWATER "highwater sessions" from v$license;

--Maximum number of process

Select SESSIONS_CURRENT "current sessions", SESSIONS_HIGHWATER "highwater sessions" from v$license;