Tuesday, June 10, 2014

Joins

JOINS
=====

To read/retrieve the data from more than one table or to read/retrieve the different
information from the same table.

Types of Joins:

- EQUI JOIN/SIMPLE JOIN (Gets matching records from both the tables)
join condition using = operator.

NATURAL JOIN: Gets matched rows/records from more than one table, but in this
all the common columns will be compared automatically to fetch the matched rows

-NON-EQUI JOIN (Join condition using other than '=' operator)

-SELF JOIN (Joining table to itself)

-INNER JOIN

-LEFT OUTER JOIN (Gets all rows from left table and matching from right table)

matched rows from both the table and unmatched from the left table.

LEFT OUTER JOIN = EQUI JOIN + UNMATCHED ROWS from left table.



-RIGHT OUTER JOIN (Gets all rows from right table and matching from left)

matched rows from both the table and unmatched row from the right table.

RIGHT OUTER JOIN = EQUI JOIN + UNMATCHED ROWS from right table.


-FULL OUTER JOIN (Gets all rows from both tables)
matched rows from both the table and unmatched row from the left table and unmatched
from the right table.
FULL OUTER JOIN=LEFT OUTER JOIN + RIGHT OUTER JOIN

-CROSS JOIN (CARTESIAN PRODUCT OR CROSS PRODUCT) - where no join condition is specified
or when no proper condition is specified. In cross join all the rows of the first table
will join with all the rows of the second table.

to join the table we can use two syntaxes:
1. Oracle proprietory syntax
2. sql 1999 syntax

EQUI JOIN: With this join we can fetch matched rows from more than one table.

-- oracle proprietory syntax:

SELECT ENAME, DNAME
FROM EMP, DEPT 
WHERE emp.DEPTNO=dept.DEPTNO;

SELECT ENAME, emp.deptno, dept.deptno, DNAME
FROM EMP, DEPT 
WHERE emp.DEPTNO=dept.DEPTNO;

SELECT e.ENAME, e.deptno, d.deptno, d.DNAME
FROM EMP e, DEPT d
WHERE e.DEPTNO=d.DEPTNO;

desc hr.locations;
desc hr.departments;
desc hr.employees;

SELECT e.last_name, d.department_name, l.city
FROM hr.employees e, hr.departments d, hr.locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;

-- sql 1999 syntax:

SELECT ENAME, DNAME
FROM EMP JOIN DEPT 
ON(emp.DEPTNO=dept.Deptno);

SELECT ENAME, emp.deptno, dept.deptno, DNAME
FROM EMP JOIN DEPT 
ON(emp.DEPTNO=dept.DEPTNO);

SELECT e.ENAME, e.deptno, d.deptno, d.DNAME
FROM EMP e JOIN DEPT d
ON(e.DEPTNO=d.DEPTNO);

SELECT e.ENAME, e.deptno, d.deptno, d.DNAME
FROM EMP e INNER JOIN DEPT d
ON(e.DEPTNO=d.DEPTNO);

SELECT e.last_name, d.department_name, l.city
FROM hr.employees e JOIN hr.departments d
ON(e.department_id=d.department_id)
JOIN hr.locations l
ON(d.location_id=l.location_id);

SELECT ENAME, DEPTNO, DEPTNO, DNAME
FROM EMP JOIN DEPT
USING(deptno);

SELECT E.LAST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM HR.EMPLOYEES E JOIN HR.DEPARTMENTS D
USING(DEPARTMENT_ID)
JOIN HR.LOCATIONS L
USING(LOCATION_ID);

Note: table alias should not be used with common columns when we join table
with USING clause. When we use USING column names should be same in both the tables
then only we can use USING clause to fetch the matched rows from both the table.


NATURAL  JOIN: IN NATURAL JOIN IT WILL FETCH MATCHED ROWS FROM BOTH THE TABLE
BY COMPARING ALL THE COLUMNS which is having same name. IN THIS JOIN WE SHOULD not
GIVE COLUMN NAMES IN THE CONDITION, IT WILL AUTOMATICALLY USE ALL THE COMMON COLUMNS
FROM BOTH THE TABLE. IN THIS JOIN IN BOTH THE TABLE COLUMN NAMES SHOULD BE SAME,
DATA TYPE SHOULD BE SAME THEN ONLY IT WILL FETCH THE MATCHED ROWS.


SELECT E.ENAME, DEPTNO, DEPTNO, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;

DESC HR.EMPLOYEES;
DESC HR.DEPARTMENTS;

SELECT E.LAST_NAME, MANAGER_ID, DEPARTMENT_ID, MANAGER_ID, DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E NATURAL JOIN HR.DEPARTMENTS D;

SELECT E.LAST_NAME, E.MANAGER_ID, E.DEPARTMENT_ID, D.MANAGER_ID, D.DEPARTMENT_ID,
D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID AND E.MANAGER_ID=D.MANAGER_ID;

Note: When tables are joined with NATURAL JOIN operator table alias should not
be used with common columns.

NON-EQUI JOIN:
---------------------

-- ORACLE PROPRIETORY SYNTAX

SELECT e.ename, e.sal, G.LOSAL, G.HISAL, g.grade
FROM emp e, salgrade g
WHERE e.sal BETWEEN g.losal AND g.hisal;

-- SQL 1999 SYNTAX:

SELECT e.ename, e.sal, G.LOSAL, G.HISAL, g.grade
FROM emp e JOIN salgrade g
ON(e.sal BETWEEN g.losal AND g.hisal);

SELECT e.ename, e.sal, G.LOSAL, G.HISAL, g.grade
FROM emp e INNER JOIN salgrade g
ON(e.sal BETWEEN g.losal AND g.hisal);

-- Dt.28-Mar-2014
SELF JOIN:
---------
-- ORACLE PROPRIETORY SYNTAX:

SELECT e.ename AS emp_name, m.ename AS manager_name
FROM emp e, emp m
WHERE e.mgr=m.empno;

-- SQL 1999 SYNTAX:

SELECT e.ename AS EMPLOYEE_NAME, m.ename AS MANAGER_NAME
FROM emp e JOIN emp m
ON(e.mgr=m.empno);

SELECT e.ename AS EMPLOYEE_NAME, m.ename AS MANAGER_NAME
FROM emp e INNER JOIN emp m
ON(e.mgr=m.empno);

SELECT e.ename ||' manager is :'|| m.ename employees_manager
FROM emp e JOIN emp m
ON(e.mgr=m.empno);

OUTER JOIN:
-----------

SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID FROM HR.EMPLOYEES;

SELECT * FROM HR.DEPARTMENTS;


RIGHT OUTER JOIN: EQUI JOIN + UNMATCHED ROW FROM THE RIGHT TABLE
----------------

-- ORACLE PROPRIETORY SYNTAX:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e, HR.DEPARTMENTS d
WHERE e.depARTMENT_ID(+)=d.depARTMENT_ID;

-- SQL 1999 SYNTAX:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e RIGHT OUTER JOIN HR.DEPARTMENTS d
ON e.depARTMENT_ID=d.depARTMENT_ID;


LEFT OUTER JOIN: EQUI JOIN + UNMATCHED ROW FROM THE LEFT TABLE

-- ORACLE PROPRIETORY SYNTAX:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e, HR.DEPARTMENTS d
WHERE e.depARTMENT_ID=d.depARTMENT_ID(+);

-- SQL 1999 SYNTAX:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e LEFT OUTER JOIN HR.DEPARTMENTS d
ON e.depARTMENT_ID=d.depARTMENT_ID;

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e LEFT JOIN HR.DEPARTMENTS d
ON e.depARTMENT_ID=d.depARTMENT_ID;


FULL OUTER JOIN: EQUI JOIN + UNMATCHED ROW FROM LEFT TABLE + UNMATCHED ROW FROM RIGHT
TABLE

FULL OUTER JOIN=LEFT OUTER JOIN + RIGHT OUTER JOIN

NOTE: FULL OUTER JOIN FEATURES IS NOT THERE IN ORACLE PROPRIETORY SYNTAX

-- SQL 1999 SYNTAX:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e FULL OUTER JOIN HR.DEPARTMENTS d
ON e.depARTMENT_ID=d.depARTMENT_ID;

CROSS JOIN(CARTESIAN PRODUCT):

-- ORACLE PROPRIETORY SYNTAX:

SELECT e.ename, e.deptno,d.deptno, d.dname FROM emp e, dept d
ORDER BY e.ename;

14 * 4 = 56(IT IS A PRODUCT OF TWO TABLES)

-- SQL 1999 SYNTAX:

SELECT e.ename, e.deptno, d.deptno, d.dname
FROM emp e CROSS JOIN dept d;

Note:  To join three tables we need  N-1 conditions
N -> number of tables used in join
3-1=2  (2 conditions we should write to join three tables)
to join two tables we need N-1 conditions
2-1=1  (1 condition we should write to join two tables)

SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND (E.SAL BETWEEN S.LOSAL AND S.HISAL);

SELECT E.ENAME, D.DNAME, deptno
FROM EMP E NATURAL JOIN DEPT D;

SELECT E.ENAME, D.DNAME, DEPTNO
FROM EMP E JOIN DEPT D
USING (DEPTNO);

SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO   
INNER JOIN SALGRADE S
ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

select emp.ename,dept.dname from emp, dept where emp.deptno=dept.deptno;

select emp.ename, dept.dname from emp natural join dept;

select emp.ename, dept.dname from emp inner join dept on (emp.deptno=dept.deptno);

select emp.ename, dept.dname from emp join dept using (deptno);

UPDATE DEPT SET DEPTNO=20 WHERE DNAME = 'OPERATIONS';

SELECT * FROM DEPT;

SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO!=DEPT.DEPTNO ORDER BY EMP.ENAME;

SELECT EMP.ENAME, DEPT.DNAME FROM EMP, DEPT ORDER BY emp.ename;

SELECT EMP.ENAME, SALGRADE.GRADE
FROM EMP, SALGRADE
WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;

SELECT * FROM
EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

SELECT E.ENAME, e.deptno, d.deptno, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO > D.DEPTNO
ORDER BY e.ename;

SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO<D.DEPTNO;

SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO>=D.DEPTNO;

SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO<=D.DEPTNO;

SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO   
INNER JOIN SALGRADE S
ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

SELECT E.ENAME employee, M.ENAME manager
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

SELECT E.ENAME, M.ENAME
FROM EMP E INNER JOIN EMP M
ON E.MGR=M.EMPNO;

SELECT E.ENAME, SG.GRADE
FROM EMP E INNER JOIN SALGRADE SG
ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL;

SELECT E.ENAME, M.ENAME FROM EMP E INNER JOIN EMP M ON E.deptno<M.deptno;

SELECT E.ENAME, M.ENAME FROM EMP E INNER JOIN EMP M ON E.deptno=M.deptno;

DROP TABLE T1;

DROP TABLE T2;

CREATE TABLE T1(C1 NUMBER, C2 CHAR(1));

CREATE TABLE T2(C3 NUMBER, C4 CHAR(1));

INSERT INTO T1 VALUES(1,'A');

INSERT INTO T1 VALUES(2,'B');

INSERT INTO T1 VALUES(3,'C');

INSERT INTO T2 VALUES(1,'P');

INSERT INTO T2 VALUES(1,'X');

INSERT INTO T2 VALUES(3,'Z');

INSERT INTO T2 VALUES(4,'R');

commit;


Left Outer Join: Left Outer Join displays all the records from left table
and matching records from right table. It displays nulls in
right table columns for unmatched records


Right Outer Join: Right Outer Join displays all the records from right table
and matching records from left table. It displays nulls in
left table columns for unmatched records 


T1                      T2

C1  C2                C3    C4                     

1     P

1   A                 1     X

2   B                 3     Z

3   C                 4     R



SELECT * FROM T1 INNER JOIN T2 ON T1.C1=T2.C3; 

SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3;

SELECT C1,C2,C3,C4 FROM T1 RIGHT OUTER JOIN T2 ON T1.C1=T2.C3;

SELECT C1,C2,C3,C4 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C3;

SELECT * FROM T1, T2 WHERE T1.C1=T2.C3(+);

SELECT * FROM T1, T2 WHERE T2.C3(+)=T1.C1;

SELECT * FROM T1, T2 WHERE T1.C1(+)=T2.C3;


SELECT E.ENAME, M.ENAME FROM EMP E LEFT OUTER JOIN EMP M ON E.MGR=M.EMPNO;

SELECT D.DNAME, E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO;

SELECT D.DNAME, E.ENAME FROM DEPT D RIGHT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO;

SELECT D.DNAME, E.ENAME FROM EMP E RIGHT OUTER JOIN DEPT D ON D.DEPTNO=E.DEPTNO;


SELECT E.ENAME, M.ENAME
FROM EMP E left outer JOIN EMP M ON E.deptno<M.deptno;

SELECT E.ENAME EMPLOYEE, M.ENAME MANAGER
FROM EMP E, EMP M

WHERE E.MGR = M.EMPNO;


SELECT E.ENAME EMPLOYEE, M.ENAME MANAGER
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+);

SELECT E.EMPNO, E.ENAME EMPLOYEE, M.ENAME MANAGER
FROM EMP E, EMP M
WHERE MOD(E.EMPNO,2)=1 AND E.MGR = M.EMPNO(+);

-- dt.31-Mar-2014

ANALYTICAL FUNCTIONS
====================
SELECT SUM(SAL) FROM EMP;

SELECT ENAME, SAL, SUM(SAL) over( ) FROM EMP;

SELECT ENAME, SAL, SUM(SAL) OVER() AS TOTAL_SAL,
ROUND(SAL / SUM(SAL) OVER() * 100) AS "PERCEN%",
SUM(SAL) OVER() - SAL AS DIFFERENCE
FROM EMP;

SELECT ENAME, SAL, (SELECT SUM(SAL) FROM EMP) SSAL FROM EMP;

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

SELECT ename, sal, DEPTNO, SUM(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

SELECT ename, sal, DEPTNO, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP;

SELECT DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) FROM EMP;

SELECT DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL),
SUM(SAL) OVER()
FROM EMP;

SELECT DEPTNO, SAL, SUM(SAL) OVER(ORDER BY sal) FROM EMP;

SELECT DEPTNO, SAL, SUM(SAL) OVER(partition by deptno) FROM EMP;

SELECT DEPTNO, SAL, SUM(SAL) OVER(ORDER BY deptno) FROM EMP;

SELECT DEPTNO, ENAME, SAL,  SUM(SAL) OVER(ORDER BY sal, ENAME desc) FROM EMP;

SELECT mgr, empno, ename, sal, 
SUM(sal) OVER (PARTITION BY mgr ORDER BY sal) l_csum  
FROM emp
ORDER BY mgr, empno, ename, sal, l_csum;


SELECT ENAME, DEPTNO, SAL, RANK() OVER (ORDER BY SAL DESC) RK FROM EMP;

SELECT ENAME, DEPTNO, SAL, RANK() OVER (ORDER BY SAL DESC) RK FROM EMP
WHERE RANK() OVER (ORDER BY SAL DESC)=2;  -- will give error

SELECT * FROM (SELECT ENAME, DEPTNO, SAL, RANK() OVER (ORDER BY SAL DESC) RK FROM EMP)
WHERE RK =2;

SELECT * FROM (SELECT ENAME, DEPTNO, SAL, RANK() OVER (ORDER BY SAL DESC) RK FROM EMP)
WHERE RK <= 5;

SELECT ENAME, DEPTNO, SAL, RANK() OVER (PARTITION BY deptno ORDER BY SAL DESC) RK
FROM EMP;

SELECT ENAME, DEPTNO, SAL, DENSE_RANK() OVER (ORDER BY SAL DESC) DRK FROM EMP;

SELECT ENAME, DEPTNO, SAL, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY SAL DESC) DRK
FROM EMP;

select ename, deptno, sal, drk from
(select ename, deptno, sal, dense_rank() over(order by SAL desc) drk from emp)
where drk=3;

SELECT ENAME, DEPTNO, SAL, ROWNUM FROM EMP;

SELECT ENAME, DEPTNO, SAL, ROWNUM FROM EMP ORDER BY SAL DESC;

SELECT ENAME, DEPTNO, SAL , ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP;

SELECT ename, deptno, sal, ROWNUM FROM (SELECT ename, deptno, sal FROM emp ORDER BY sal DESC);

SELECT ENAME, DEPTNO, SAL , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY SAL DESC) RN
FROM EMP;

SELECT * FROM (SELECT ENAME, DEPTNO, SAL , ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP)
WHERE RN <=5;

SELECT * FROM (SELECT ENAME, DEPTNO, SAL , ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP)
WHERE RN =5;

SELECT   ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP;

SELECT  SAL, ENAME, ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME ASC) RN FROM EMP;

SELECT * FROM (SELECT ENAME, HIREDATE, ROW_NUMBER( ) OVER(ORDER BY HIREDATE DESC) RN FROM EMP)
WHERE RN =5;


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)