Oracle Joins

Joins are used to display or retrieve the data from more than one table using single query.
Types of JOINS available:
EQUI JOIN(INNER JOINS)
NON-EQUI JOIN
SELF JOIN
CARTESIAN JOIN

EQUI JOIN:

  • Equality condition tables are joined.
  • Tables must have common column between them with respective to their datatypes. Which means the datatype of the common column must be the same.
  • If n tables are joined, n-1 conditions are needed.
  • Only matching records are displayed.

Syntax:
SELECT Column1, Column2,….
FROM table1, table2, …
WHERE Table1.column_name=table2.column_name;

Example:

SELECT emp.empno, emp.ename, dept.deptno, dept.dname,dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;

NON-EQUI JOIN: Data is retrieved without equality condition. We are retrieving the data from mulitple tables. When tables does not have common columns, then only we are using equi join.

Syntax:

SELECT column1, column2,…..
FROM table1, table2,…
WHERE table1.column_name between table2.column_name AND table2.column_name;

Example:

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal between s.lowsal and s.highsal;

SELF JOIN:
Joining the table itself is called self join. This is used on the same table. The table must have atleast 2 column with same datatype and value should have same data.

Example:

SELECT e2.ename, e1,sal
FROM emp e1, emp e2
WHERE e1.sal=e2.sal and
e1.ename=’SMITH’;

OUTER JOIN:

  • Used to retrieve all the rows from table but matching rows from other table.
  • It uses an operator(+), it is called as join operator.

Example:

SELECT ename, sal, d.deptno, dname, loc
FROM emp e, dept d
WHERE e.deptno(+)=d.deptno
UNION
SELECT ename, sal, d.deptno, dname, loc
FROM emp e, dept d
WHERE e.deptno(+)=d.deptno;

CARTESIAN JOIN:

  • Used to join the table without any common column.
  • Number of rows retrieved will be based on Cartesian product.

Example:

SELECT empno, ename, sal, job, emp.deptno,loc, dname
FROM emp,deptno;

ANSI or 9i Joins:
NATURAL JOINS
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

NATURAL JOIN:

  • In this, natural is used to retrieve data from multiple tables.
  • If common column name is not there in joining table,then the join becomes as equi-join.
  • No alias names are allowed in the natural join.

Example:

SELECT empno, ename, sal, deptno, dname, loc
FROM emp natural join dept;

INNER JOIN: If we want to retrieve only matching rows, we use inner join.

Example:

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e inner join dept d
on(e.deptno=d.deptno);

LEFT OUTER JOIN: In this join, always returns left side table and matching records from right side table and also returns null values in place of non matching rows in another table.

Example:

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e left outer join dept d
on(e.deptno=d.deptno);

RIGHT OUTER JOIN: In this join, always returns right side table and matching records from leftt side table and also returns null values in place of non matching rows in another table.

Example:

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e right outer join dept d
on(e.deptno=d.deptno);

FULL OUTER JOIN:

  • This join returns matching and non-matching rows from all the tables.
  • It is combination of left and right join.
  • This join returns null values in place of non matching rows in another table

Example:

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e full outer join dept d
on(e.deptno=d.deptno);

Powered by k2schools