SQL - JOINS

SQL - JOINS:

1) Inner join :

An Inner join essentially combines the records from two tables based on a give join – predicate. An Inner join does require each record in the two joining tables to have a matching column. It returns all rows from both the tables where there is a match.

SQL specifies two different syntactical ways to express joins.

The first called explicit join notation uses the keyword JOIN and second one is implicit join notation.

The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement using commas to separate them.


Select * from emp, dept where emp.deptno = dept.deptno; -- Implicit Join

Select * from emp INNER JOIN dept ON emp.depno = dept.deptno; -- Explicit Join



2) Types of Inner joins

Equi – join
Non equi join
Natural join
Cartesian join



3) Equi join

An equi join is a join statement that uses an equivalence operation to match rows from different tables.

Select * from emp, dept where emp.deptno = dept. deptno and job = ‘CLERK’;


4) Non equi join

A non equi join is a join statement that uses an unequal operation ( i.e <>, >, <=, BETWEEN etc) to match rows from different tables.

Select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;


5) Natural join

A natural join is a join statement that compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

Select * from emp NATURAL JOIN dept;


6) Cartesian join

A Cartesian join is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified.


Select * from emp, dept;


7) Outer join

An outer join returns non matched rows also from the table with the outer join operator (+). Missing values are filled with NULL values.


(+) sign indicates that in case the column contains a null it should also be included.


8) Left outer join

It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

Select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno (+); (LOJ)

Select e.ename, d.dname from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;


9) Right outer join


It preserves the unmatched rows from second(right) table, joining them with a NULL row in the shape of the first (righ) table.

Select e.ename, d.dname from emp e, dept d where e.deptno (+) = d.deptno; (ROJ)
It displays : operations(40)



Select e.ename, d.dname from emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;


10) Full outer join

A full outer join also returns non matching rows from both the tables left and right.

Select e.ename, d.dname from emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;


11) Self join

A self join is a join in which a table is joined with itself.

Select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

Select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;