Monday, 6 August 2012

JOINS IN ORACLE-different joins in oracle with examples



1. The purpose of a join is to combine the data across tables.
2. A join is actually performed by the where clause which combines the specified rows of tables.
3. If a join involves in more than two tables then Oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES
1     Equi join
2     Non-equi join
3     Self join
4     Natural join
5     Cross join
6     Outer join 
  • Left outer 
  • Right outer 
  • Full outer 
7     Inner join
8     Using clause
9     On clause

Assume that we have the following tables.
SQL> select * from dept;

DEPTNO
DNAME
LOC
10
INVENTORY
HYBD
20
FINANCE
BGLR
30
HR
MUMBAI



SQL> select * from emp;

EMPNO
ENAME
JOB
MGR
DEPTNO
111
saketh
analyst
444
10
222
sudha
clerk
333
20
333
jagan
manager
111
10
444
madhu
engineer
222
40



      1.      EQUI JOIN
A join which contains an equal to ‘=’ operator in the joins condition.
Ex:
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;




EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR


Using clause
SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);


EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR

On clause
SQL> select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR



      2.      NON-EQUI JOIN
 A join which contains an operator other than equal to ‘=’ in the joins condition.
 Ex:
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;


EMPNO
ENAME
JOB
DNAME
LOC
222
sudha
clerk
INVENTORY
HYBD
444
madhu
engineer
INVENTORY
HYBD
444
madhu
engineer
FINANCE
BGLR
444
madhu
engineer
HR
MUMBAI



      3.      SELF JOIN
Joining the table itself is called self join.
Ex: 
SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr;

EMPNO
ENAME
JOB
DEPTNO
111
jagan
analyst
10
222
madhu
clerk
40
333
sudha
manager
20
444
saketh
engineer
10



      4.      NATURAL JOIN
Natural join compares all the common columns.
Ex:
SQL> select empno,ename,job,dname,loc from emp natural join dept;

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR



      5.      CROSS JOIN
This will gives the cross product.
Ex:
SQL> select empno,ename,job,dname,loc from emp cross join dept;

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
222
sudha
clerk
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
444
madhu
engineer
INVENTORY
HYBD
111
saketh
analyst
FINANCE
BGLR
222
sudha
clerk
FINANCE
BGLR
333
jagan
manager
FINANCE
BGLR
444
madhu
engineer
FINANCE
BGLR
111
saketh
analyst
HR
MUMBAI
222
sudha
clerk
HR
MUMBAI
333
jagan
manager
HR
MUMBAI
444
madhu
engineer
HR
MUMBAI




      6.      OUTER JOIN
Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN
This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:
SQL> select empno,ename,job,dname,loc from emp e left outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where
e.deptno=d.deptno(+);

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR
444
madhu
engineer




RIGHT OUTER JOIN
This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:
SQL> select empno,ename,job,dname,loc from emp e right outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =
d.deptno;

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR



HR
MUMBAI


FULL OUTER JOIN
This will display the all matching records and the non-matching records from both tables.
Ex:
SQL> select empno,ename,job,dname,loc from emp e full outer join dept d
on(e.deptno=d.deptno);

EMPNO
ENAME
JOB
DNAME
LOC
333
jagan
manager
INVENTORY
HYBD
111
saketh
analyst
INVENTORY
HYBD
222
sudha
clerk
FINANCE
BGLR
444
madhu
engineer





HR
MUMBAI



      7.      INNER JOIN
This will display all the records that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

EMPNO
ENAME
JOB
DNAME
LOC
111
saketh
analyst
INVENTORY
HYBD
333
jagan
manager
INVENTORY
HYBD
222
sudha
clerkx`
FINANCE
BGLR



  
If you like this post, please share it on google by clicking on the Google +1 button.

PLEASE CHECK OUT OUR LATEST BLOG on :



Please go through similar Oracle Posts @DWHLAUREATE:

  WHAT ARE EXTERNAL TABLES IN ORACLE


102 comments:

  1. Harish Pandalangatt9 June 2013 at 11:55

    SUPERB JOB.!!

    ReplyDelete
    Replies
    1. Thanks harish for your comments

      Delete
    2. realy explained well...specially examples

      Delete
  2. Good one. Thanks for the tutorial. Keep these comming.

    ReplyDelete
  3. Amazing tutorial

    ReplyDelete
  4. Simple and easy to understand.. Thanks!!

    ReplyDelete
  5. Excellent post and the blogs also dear..
    Thanks.. :)

    ReplyDelete
  6. Explained in a simple and elegant manner !!! Kudos for the good work :)

    ReplyDelete
  7. NICE AN INFORMATIVE FOR LEARNING KEEP UP

    ReplyDelete
  8. Thankyou Everyone..Keep supporting so that we can put more informative posts like this.....

    ReplyDelete
  9. EASY STEPS TO UNDERSTAND FOR A BEGINNER,REALLY EXCELLENT JOB

    ReplyDelete
  10. very easy steps for understanding of join in oracle

    ReplyDelete
  11. helpful...........

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete
  13. nice informstion for learning..........superb

    ReplyDelete
  14. Can we retrieve multiple row data without join? using only where clause

    ReplyDelete
    Replies
    1. U can retrieve multiple row data without join, but u cant rerieve multiple table row data.

      Delete
  15. Nice, short and to the point explanation.....keep it up

    ReplyDelete
  16. There two tables employee & department for some employee department is not assigned. Write the outer join that will return all employees that have departments associated with them & those for which no department is there.

    ReplyDelete
    Replies
    1. I hope u dont hv any confusion dear....follow the above mentioned example

      Delete
  17. Difference between equi join and the inner join ???

    ReplyDelete
    Replies
    1. There is no difference between these two above mentioned in practicality, but by the fact is equi join belongs to Oracle Proprietary joins (8i and prior) whereas inner join is belongs to ANSI SQL: 1999 Compliant joins

      Delete
    2. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
      Equi join only have equality (=) operator in the join condition.

      Delete
  18. Explained in a good manner.. really useful thank you....

    ReplyDelete
  19. Wonderful Job!!!! This blog is very helpful.

    ReplyDelete
  20. its very helpful to understand join

    ReplyDelete
  21. Good one,Really helpful

    ReplyDelete
  22. Good Example for easy understanding !! Keep it up ..

    ReplyDelete
  23. Really Useful ............... Thanks for it.

    ReplyDelete
  24. Thank You all for the valuable comments....Please check out our latest post on Unix
    http://dwhlaureate.blogspot.in/2013/12/how-to-do-sorting-in-unix.html

    ReplyDelete
  25. Excellent Job - Venkad

    ReplyDelete
  26. Thank you it's very use full for us

    ReplyDelete
  27. Only one word, WOW

    I have seen some where joins are explain using venn diagram,

    If feasible then please...

    ReplyDelete
  28. Grate yar...Its simple to understand

    ReplyDelete
  29. User friendly and easy to understand

    ReplyDelete
  30. I have some requirement like this
    select *
    from emp
    where ename like 'S%';

    without using like operator i need to retrieve data


    ReplyDelete
    Replies
    1. select ename from emp where substr(ename,1,1)='S';

      Delete
  31. what is the difference between equi join and natural join and inner join??????

    ReplyDelete
  32. indeed great job.. so clearly stated with examples.

    ReplyDelete
  33. Really good and understandable....Can you please explain how the cross join works once?

    ReplyDelete
  34. Excellent job..took just 5 mins to understand joins..

    ReplyDelete
  35. Nice tutorial.. superb:)

    ReplyDelete
  36. Great work bro........ :)

    ReplyDelete
  37. your using clause example is wrong

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete
  39. Gr8 job bro, thanks fr this help. :)

    ReplyDelete
  40. Great job bro, thanks for this.
    i hope this helps to everybody

    ReplyDelete
  41. Quite helpful n easy to understand. Thanks a ton.....

    ReplyDelete
  42. i am beginner but i underder stood easily nice document thank u sir

    ReplyDelete
  43. Fantastic work, simple but brief ....... Thanks

    ReplyDelete
  44. thanks buddy

    ReplyDelete
  45. Then what is the difference between equijoin and inner join?

    ReplyDelete
  46. excellent.great job.explained in a simple manner.

    ReplyDelete
  47. Thank you so much fr the wonderful explanation ..
    Keep up the good work and keep posting ..
    :)

    ReplyDelete
  48. @Viki J

    You can use Regexp_Like() function

    ReplyDelete
  49. Superb explanation... Short and Concise.. up to the mark.. Thanks for sharing... keep on.

    ReplyDelete
  50. Hey!!! I havn't seen such a dedicated/clear explanation anywhere till now..Thanks a loot!!!

    ReplyDelete
  51. Great job.Keep it up

    ReplyDelete
  52. Neat Explanation... Thanks for sharing...

    ReplyDelete
  53. Good work..its really helpful and easy to understand to everyone.thanx

    ReplyDelete
  54. thanks a lot....

    ReplyDelete
  55. self join is not getting exicute
    error name

    ERROR at line 1:
    ORA-00904: "E2"."EMPNO": invalid identifier

    ReplyDelete
  56. This comment has been removed by the author.

    ReplyDelete
  57. Why any one use cross join ? Data output does not make sense.

    ReplyDelete
  58. Good one...explained in two ways.Thank alot

    ReplyDelete
  59. Easy to learn types of joins...excellent work

    ReplyDelete
  60. i through out all of my confuse about Joining by this work
    thanks a lot.

    ReplyDelete
  61. This article has examples which helped me lot , thanks .

    ReplyDelete
  62. Thanks for sharing the very useful info about Oracle and please keep updating........

    ReplyDelete
  63. Nice blog. Really helpful information about Oracle …. Please keep update some more…………

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis