Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 11 October 2017

Oracle SQL joins


The purpose of a SQL join is to combine the data across tables.
A join is actually performed by the where clause which combines the specified rows of tables.
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.


SQL Join Types 

Inner Join
This will display all the records that have matched.
Left Outer
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.
Right Outer
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.
Full Outer
This will display the all matching records and the non-matching records from both tables.
  


Assume that we have the following tables.


SQL> select * from Dept;


DEPTNO
DNAME     
LOC
10           
Mkt       
Hyd
20           
Fin       
Bang
30           
Hr        
Bombay


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


INNER JOIN
 
This will display all the records that have matched. 

SQL Inner Join
 Example:     
 
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);
     EMPNO     ENAME   JOB        DNAME    LOC
     ---------- ---------- ----------        ----------    ----------
       111          saketh     analyst      mkt             hyd
       333          jagan       manager   mkt            hyd
       222          sudha      clerk          fin             bang




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.

Left Outer Join

Example: 
 
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       mkt        hyd
       333          jagan      manager    mkt        hyd
       222          sudha     clerk           fin          bang
       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.

SQL Right Outer Join
Example:

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      mkt        hyd
       333          jagan       manager   mkt        hyd
       222          sudha      clerk          fin          bang
                                                       hr           bombay
FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.
SQL Full Outer Join
Example:  


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    mkt        hyd
       111     saketh   analyst       mkt        hyd
       222     sudha    clerk           fin        bang
       444     madhu   engineer     
                                                 hr         bombay

Share this article :

3 comments:

  1. Hello,


    11/10!! Your blog is such a complete read. I like your approach with #topic. Clearly, you wrote it to make learning a cake walk for me.



    how to find first nonspace character from end of the string


    ex: 'abc def g '

    I should get 4 in this case. (the first nonspace character from backwards in the above example is g and position is 4 from backwards.

    Could you help me in getting this?





    I am so grateful for your blog. Really looking forward to read more.


    Best Regards,

    ReplyDelete
  2. Marhaba,


    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on Oracle SQL joins.

    I have DMLStatements which contains some polish characters for POLAND country.
    When i am executing that DML through the QL Developer i am able to see my Polishcharacters.

    but when we gave the release through the SQLPLUS those characters are replacing with some special characters
    like ? (Question Mark)


    Data type of the column is : NCLOB
    NLS_CHARACTERSET : AL32UTF8
    NLS_NCHAR_CHARACTERSET : AL16UTF16

    Could you please help to resolve this?

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Kind Regards,
    Radhey

    ReplyDelete
  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete