Monday, October 15, 2012

JOINS Concept in Oracle || JOINS Overview



JOINS :
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Joins are classifieds into 8 types.

1)Equi/Inner/Simple Join : 
===================
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

  • We use equal operator in the where clause of select statement. 
  • If we want to perform equi join operation at least we require one common column between the tables. 


Syntax: select * from Emp e,Dept d where e.deptno=d.deptno;

(To view SQL Command prompt in full screen, Click on the image)

Inner Join : 
========
 An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

Syntax: select * from Emp e inner join Dept d on e.deptno=d.deptno;
Output: Same as output of equi join.

2)Natural Join :
===========
Natural join and inner join both are same as par as output is concern.The differences are

  • No need to mention the Table name before the column name. 
  • No need to check the where condition. 
  • Common Column will be displayed in the starting position of the output. 

Syntax: select * from Emp natural join Dept;

(To view SQL Command prompt in full screen, Click on the image)

3)Non-Equi Join : 
============= 
In this join we use any relational operator such as #,<=,>=,> etc other than excepting equal to (=) operator.

Syntax:select * from Emp e,Dept d where e.deptno<=d.deptno;
Example: select emp.*,gno from emp ,grade where sal>=ss and sal<=ss;

(To view SQL Command prompt in full screen, Click on the image)


OUTER JOIN :
===========
 An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.In equi join there might be a chance to loss some information, to recover we use outer joins.

LEFT OUTER JOIN : 
==============
 It is a combination of equi join operation plus the loss of information from left hand side of the table.
 Example: select * from emp left outer join dept on emp.deptno=dept.deptno;

Oracle 8i Model :
============
Example: select * from emp,dept where emp.deptno=dept.deptno(+);

(To view SQL Command prompt in full screen, Click on the image)


RIGHT OUTER JOIN :
===============
 It is a combination of equi join operation plus the loss of information from right hand side of the table.
Example: select * from emp right outer join dept on emp.deptno=dept.deptno;

Oracle 8i Model :
============
 Example: select * from emp,dept where emp.deptno(+)=dept.deptno;

(To view SQL Command prompt in full screen, Click on the image)


FULL OUTER JOIN :
==============
It is a combination of equi join operation plus right outer join plus left outer join.
Ex: select * from emp full outer join dept on emp.deptno=dept.deptno;

 (To view SQL Command prompt in full screen, Click on the image)


SELF JOIN : 
==========
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.

Example: select e.empno,e.ename manager,m.ename from emp e,emp m where e.empno=m.mgr;

(To view SQL Command prompt in full screen, Click on the image)


CROSS JOIN :
==========
This join is the product of two or more than two tables, it means that ‘m’ number of records in one table and ‘n’ number of records in another table so we can get product of these two tables.

Example:1) select * from emp cross join dept;
                2)select * from emp,dept;
Output: if records of emp as 'm' and records of dept as 'n' then we will get 'm*n' records.



No comments:

Post a Comment

back to top