This is a document Template
 
 
EQUIJOIN :  This type of join, uses equal to operator to join these two tables, therefore is called as equi-join.

select e.col1, e.col2, e.col3, d.col1, d.col2
from table1 e, table2 d
where e.col1= d.col2

Outer join: In this type of join, only the rows that contain the common keys are selected. In query

  • retains the rows of both tables
  • used to view all records from ONE  table
  • left and right outer joins
    • left joins: returns all from left table
    • right joins; returns all from right table
    • full joins: returns all column from both

Inner join :

  • used to find matching rows in two tables

Self-joins

When a table is joined with itself it is called as self-join. This is a variant of join. In this two
copies of the same table are taken and joined as if they are two different types.

NATURAL JOINS:

  • ON CLAUSE can't be used with natural joins
  • USING clause can't be used with natural joins and LOB
 
INNER JOIN : Table_A from left joins to right Table_B with equijoin condition.
  • used in equi-join and natural join
Outer Joins: In a Query A to B, this query also prints the column in the table(B)  that did not match with table A

Left outer join

SELECT <list of column_names>
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.column_name=t2.column_name;

Query returns all rows from the left (t1), with matching row in the right table(t2).

 
Right outer : Returns all the rows from (Right-Outer ) or t2, table have no matching row in t1 or left table.

SELECT <list of column_names>
FROM table1 t1
RIGHT OUTER JOIN table2 t2
ON t1.column_name=t2.column_name;
Compared : left outer with right outer :

LEFT returns ( 14 ROWS) and right returns 15 rows

Right outer join: Prints column "operation" from the DEPT table

Left outer join: It prints all the columns in EMP , and ignores unmatched column in the table DEPT
right join

SELECT e.* , d.* FROM emp e right outer join DEPT d ON e.deptno = d.deptno

PARTITION BY :

SELECT EMPNO, ENAME,DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ENAME) RN FROM EMP